INDEX DEFRAGMENTATION SCRIPT

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 18856

    Comments posted to this topic are about the item INDEX DEFRAGMENTATION SCRIPT

    Muthukkumaran KaliyamoorthyHelping SQL DBAs and Developers >>>SqlserverBlogForum[/url]

  • ryan.mcatee

    SSC Eights!

    Points: 857

    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.

  • F.L

    Right there with Babe

    Points: 734

    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”

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 18856

    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 KaliyamoorthyHelping SQL DBAs and Developers >>>SqlserverBlogForum[/url]

  • e-ghetto

    SSChasing Mays

    Points: 601

    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?

  • Balance

    SSCrazy

    Points: 2636

    Can I ask how you came to the decision to only do if @Page>1000?

    Thanks

  • Alex Gay

    SSCrazy

    Points: 2181

    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.

  • D Rose

    SSC Rookie

    Points: 34

    I see in the SQL that this is for SQL 2005 +.

    Is this possible on SQL 2000?

    Thanks!

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 18856

    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 KaliyamoorthyHelping SQL DBAs and Developers >>>SqlserverBlogForum[/url]

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 18856

    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 KaliyamoorthyHelping SQL DBAs and Developers >>>SqlserverBlogForum[/url]

  • Balance

    SSCrazy

    Points: 2636

    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.

  • Balance

    SSCrazy

    Points: 2636

    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

    SELECT OBJECT_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

    FROM SYS.DM_DB_INDEX_PHYSICAL_STATS ('+cast(@DB_ID as varchar)+',NULL,NULL,NULL,NULL) F

    JOIN SYS.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

    join sys.tables t on f.object_id = t.object_id -- NOTE NEW LINE TO REPLACE USE OF INFORMATION_SCHEMA

    join sys.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()

    AND OBJECTPROPERTY(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.

  • jshailendra

    SSCrazy

    Points: 2260

    Thanks Mutthu...this goes to my briefcase.:satisfied:

  • macherla.harish

    SSC Enthusiast

    Points: 123

    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

  • macherla.harish

    SSC Enthusiast

    Points: 123

    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 50 total)

You must be logged in to reply to this topic. Login to reply