November 19, 2009 at 9:28 am
Comments posted to this topic are about the item INDEX DEFRAGMENTATION SCRIPT
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 8, 2009 at 5:55 am
Very nice work, thank you. I would have 5-starred it, but I don't like the script writing to the master database. I realize others might disagree with me, but I'd rather the script writes to the database being maintained, or optimally, have it's own, dedicated database to write to.
December 8, 2009 at 7:10 am
Very nice script indeed.
Just a question, isn’t there some misinformation in this part?
BEGIN
--IF PAGE LEVEL LOCKING IS DISABLED THEN REBUILD
BEGIN TRY
EXEC ('USE ['+@DB_NAME+'];ALTER INDEX ['+@IND_NAME+'] ON ['+@SCHEMA_NAME+'].['+@TAB_NAME +'] REORGANIZE ')
END TRY
BEGIN CATCH
IF ERROR_NUMBER()=2552
EXEC ('USE ['+@DB_NAME+'];ALTER INDEX ['+@IND_NAME+'] ON ['+@SCHEMA_NAME+'].['+@TAB_NAME +'] REBUILD ')
END CATCH
EXEC ('USE ['+@DB_NAME+'];UPDATE STATISTICS ['+@SCHEMA_NAME+'].['+@TAB_NAME+'] (['+@IND_NAME+']) ' )
INSERT INTO MSDB.DBO.DBA_DEFRAG_MAINTENANCE_HISTORY
VALUES (@DB_NAME,@TAB_NAME,@IND_NAME,@FRAG,@PAGES,'REORGANIZE & UPDATESTATS',GETDATE())
END
If the TRY fail it goes into the CATCH, it does a rebuild.
But it still updates the history table with “REORGANIZE & UPDATESTATS”
December 8, 2009 at 8:11 am
If the TRY fail it goes into the CATCH, it does a rebuild.
But it still updates the history table with “REORGANIZE & UPDATESTATS”
Hi F.L
Thanks for your information.I ll change this ASAP.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 10, 2009 at 3:26 am
OFFTOPIC: MUTHUKKUMARAN, you have the coolest name, I've ever heard!:-P
I guess it's not the first time you hear this, isn't it?
January 22, 2010 at 3:36 am
Can I ask how you came to the decision to only do if @Page>1000?
Thanks
January 22, 2010 at 3:48 am
Quick bug fix.
The line "EXEC ('USE '+@DB_NAME+';"
Should be "EXEC ('USE ['+@DB_NAME+'];"
otherwise it fails if you have a database name with spaces in.
Not that any of us would do this, but third parties who don't know any better do/did.
Otherwise a very useful script.
January 22, 2010 at 7:47 am
I see in the SQL that this is for SQL 2005 +.
Is this possible on SQL 2000?
Thanks!
January 24, 2010 at 4:16 am
Alex Gay (1/22/2010)
Quick bug fix.The line "EXEC ('USE '+@DB_NAME+';"
Should be "EXEC ('USE ['+@DB_NAME+'];"
otherwise it fails if you have a database name with spaces in.
Not that any of us would do this, but third parties who don't know any better do/did.
Otherwise a very useful script.
S i agreed.I missed that one.Thanks
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 24, 2010 at 4:19 am
D Rose (1/22/2010)
I see in the SQL that this is for SQL 2005 +.Is this possible on SQL 2000?
Thanks!
Defragmentation script only works for sql server 2005/2008.If u want u can search here for 2000.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
February 4, 2010 at 2:03 am
I know this comment is rather late in the day but I'm just revisiting your script - I've used it as the basis of something that suits what I want so thought I'd mention a simple performance improvement & that is to make the cursor a fast_forward cursor. Makes access to a "step through 1 row after another" type of cursor significantly faster.
February 11, 2010 at 5:23 am
Having used this as the basis for my own version though I best update again - there is an error in the code when you have multiple schemas in a database where tablenames are repeated (so schemaA.Table1 and schemaB.Table1).
Solution is to make the code schema aware, so changing this code section :
INSERT INTO #TEMPFRAG
SELECT OBJECT_NAME(F.OBJECT_ID) OBJ,I.NAME IND,
F.AVG_FRAGMENTATION_IN_PERCENT,
F.PAGE_COUNT,TABLE_SCHEMA
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(),NULL,NULL,NULL,NULL) F
JOIN SYS.INDEXES I
ON(F.OBJECT_ID=I.OBJECT_ID)AND I.INDEX_ID=F.INDEX_ID
JOIN INFORMATION_SCHEMA.TABLES S
ON (S.TABLE_NAME=OBJECT_NAME(F.OBJECT_ID))
--WHERE INDEX_ID<> 0
AND F.DATABASE_ID=DB_ID()
AND OBJECTPROPERTY(I.OBJECT_ID,''ISSYSTEMTABLE'')=0'
with this
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
The only other change you want to make for the above to work is to add at the start of your code :
declare @DB_ID int
set @DB_ID= DB_ID('Your DB Name Here')
Because that gets around a failure in the original source if you want to run this for any database from any database.
February 11, 2010 at 5:38 am
Thanks Mutthu...this goes to my briefcase.:satisfied:
February 12, 2010 at 3:19 am
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
February 12, 2010 at 3:21 am
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
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply