February 13, 2010 at 3:38 am
macherla.harish (2/12/2010)
HI Muthu ,Good script given.
HI muthu, i have a Dought in Sqlserver2000 , IN sqlserver 2005 Copy_only option is there But In sql server2000 not there if hava log shiping or Replication iam using iam taken backup lsn chain will breakup.
In my production sqlserver2000 is there how to take a backup without lSN Breakup
Thank You
Harishkumar.M
Please start a new thread 🙂
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
May 3, 2010 at 1:26 am
Balance (1/22/2010)
Can I ask how you came to the decision to only do if @Page>1000?Thanks
Its MS rule of thumb.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 13, 2011 at 12:36 am
Click here:INDEX DE-FRAGMENTATION SCRIPT for SQL server 2000 [/url]
Edit: to Fix the link
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 30, 2011 at 9:28 am
I haven't read through the above replies to check, but has anyone received the below error ONLY when the SP is executed against a CS collated DB? e.g. "SQL_Latin1_General_CP1_CS_AS"
"
Msg 208, Level 16, State 1, Line 2
Invalid object name 'SYS.DM_DB_INDEX_PHYSICAL_STATS'. "
It runs fine on any other DB. It might be the solution to my failed indexing scripts, won't know until I get it running for a little bit.
Thanks,
Bobw
November 30, 2011 at 11:39 am
bobw1776 (11/30/2011)
I haven't read through the above replies to check, but has anyone received the below error ONLY when the SP is executed against a CS collated DB? e.g. "SQL_Latin1_General_CP1_CS_AS""
Msg 208, Level 16, State 1, Line 2
Invalid object name 'SYS.DM_DB_INDEX_PHYSICAL_STATS'. "
It runs fine on any other DB. It might be the solution to my failed indexing scripts, won't know until I get it running for a little bit.
Thanks,
Bobw
Bobw,
Hi,
Ask a question in general forum ---> http://www.sqlservercentral.com/Forums/
you got 'Invalid object name' error the DMV available on 2005 onwards.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
November 30, 2011 at 7:44 pm
Nice script.
For performance purposes, have you considered dynamically specifying the FILLFACTOR on your index rebuilds to help with page splits and IO performance?
Not sure what your database default is for FILLFACTOR, but your script is going to apply the default setting to all your index rebuilds when this is not specified. Since you are already checking for the percent fragmentation in your script, you could specify a lower FILLFACTOR for more fragmented non-clustered indexes and possibly even a zero FILLFACTOR on clustered indexes that currently have a near zero percent fragmentation since they are obviously designed to add all new rows to the end of the table.
This approach allows your clustered indexes to have a higher page density (improving overall IO performance) and allows your non-clustered indexes to handle some number of new records before a page split is needed, but not too much to waste a bunch of space on the drives and in memory. Having too low of a page density can also adversely affect performance by requiring the buffer pool to perform more IOs to get the same amount of data off the disk or out of memory.
The FILLFACTOR should be set dynamically as the insert and update patterns on your tables will most likely vary from table to table - figuring out that algorithm is the tricky part. I guess you could even create a table of all your indexes where you specify your best FILLFACTOR based on your trials and refer to that table during your index rebuilds and only use the default on all new indexes that have not been added to your table yet.
-Eric
-Eric
December 1, 2011 at 2:22 am
if your machine is case sensitive I would check the code carefully.
We also used some of this code as the basis for our current routine - for a production install you might want to consider your local preferences on minimum pages, fragmentation levels (esp whether you ever want to reorganize) and also the other index options that rebuild should consider.
February 2, 2012 at 10:52 am
Very nice and useful script. Thank you.
Had problems with similar table names under different schemes as well.
However, there is no need for joins to sys.tables and sys.schemas.
INSERT INTO #TEMPFRAG
SELECTOBJECT_NAME(F.OBJECT_ID)
,I.NAME IND
,F.AVG_FRAGMENTATION_IN_PERCENT
,F.PAGE_COUNT
, s.name -- NOTE CHANGED FROM TABLE_SCHEMA
--,i.allow_page_locks -- I use this to better handle allow_page_locks true / false
FROMSYS.DM_DB_INDEX_PHYSICAL_STATS ('+cast(@DB_ID as varchar)+',NULL,NULL,NULL,NULL) F
JOINSYS.INDEXES I ON(F.OBJECT_ID=I.OBJECT_ID) AND I.INDEX_ID=F.INDEX_ID and i.is_disabled = 0 -- Note I'm skipping disabled indexes
joinsys.tables t on f.object_id = t.object_id -- NOTE NEW LINE TO REPLACE USE OF INFORMATION_SCHEMA
joinsys.schemas s on s.schema_id = t.schema_id -- NOTE NEW LINE TO COMPLETE JOIN
--WHERE INDEX_ID<> 0
AND F.DATABASE_ID=DB_ID()
ANDOBJECTPROPERTY(I.OBJECT_ID,''ISSYSTEMTABLE'')=0
You can use OBJECT_SCHEMA_NAME function instead:
INSERT INTO #tempfrag (schema_name,table_name,index_name,frag,pages)
SELECTOBJECT_SCHEMA_NAME(v.object_id) ASSchemaName,
OBJECT_NAME(v.object_id)ASTableName,
i.nameASIndexName,
v.avg_fragmentation_in_percentASFragmentation,
v.page_countASPageCount
FROMSYS.DM_DB_INDEX_PHYSICAL_STATS(11,NULL,NULL,NULL,NULL) v
INNER JOIN
SYS.INDEXES i ON v.object_id = i.object_id
AND
v.index_id = i.index_id
AND
i.is_disabled = 0
February 2, 2012 at 10:29 pm
Thanks Greg Shinder.I'll look into this.
I'm glad you liked it.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 23, 2012 at 2:58 pm
I am new to TSQL.
What would be the best way to run this sp for all user databases on a SQL 2008 server?
February 23, 2012 at 9:36 pm
vanceprice (2/23/2012)
I am new to TSQL.What would be the best way to run this sp for all user databases on a SQL 2008 server?
Welcome to T-SQL language.
Loop the sys.databases and use this SProc inside the loop.
Take an example of my Index defragmentation script and Try yourself using sys.databases.
If you face any problem open a new thread and ask your quires.We will help you.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 8, 2012 at 12:02 pm
I've got the following error when used your script in sql 2000. Any idea?Thanks
Server: Msg 1038, Level 15, State 3, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.
(1 row(s) affected)
March 9, 2012 at 9:17 am
natasha-501269 (3/8/2012)
I've got the following error when used your script in sql 2000. Any idea?ThanksServer: Msg 1038, Level 15, State 3, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.
(1 row(s) affected)
It will not work for SQL 2000.
Use this for SQL 2000
Rebuild Index script for SQL 2000
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 9, 2012 at 12:12 pm
Thanks , however this script from your link for sql 2000 is exactly what I used... and it does not run successfully.
March 9, 2012 at 10:18 pm
What error are you getting?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply