Post reply

Weekly DB Maintenance - DBREINDEX

  • January 20, 2009 at 10:55 am

    #97090

    All,

    As part of the weekly db maintenance, I run the following:

    exec sp_MSforeachtable

    @command1 = "Print '?'",

    @command2 = "DBCC DBREINDEX ('?')"

    Last week, I changed the DBREINDEX to be DBREINDEX ('?',' ',10). Two things happened. 1 - The database went from 20 gb to 128 gb. 2 - Performance has gone south.

    I have since changed the DBREINDEX back, but the damage is done. Can someone please tell me what I should do to get back to where I was before I made that horrible decision to change the DBREINDEX. I need my small db back and performace to be back to normal.

    Thanks in advance - I will be eternally grateful.

  • January 20, 2009 at 1:10 pm

    #929926

    you have told the reindex job to only use 10% of each page when it reorganises the data, hence you have loads of wasted space within each page, so the total space used by the data has grown. This will have increased your i/o to retrieve the same amount of data so performance has suffered

    rerun you SQL with DBREINDEX ('?',' ',90),

    ---------------------------------------------------------------------

  • January 21, 2009 at 10:24 am

    #930519

    Thanks George. I got the table counts and re-indexed the smallest tables (up to 2500 rows) at 95, the next group were tables w\up to 5000 rows at 90 then I did the remaining tables at 80.

    Then I turned on auto-shrink overnight and was very happy to see that this morning the database was only 56 gb (down from 128 gb). I will re-index again tonight and turn on auto-shrink and see if how close I can get to the original 20 gb.

    Thanks for your help!

  • January 21, 2009 at 10:38 am

    #930529

    thanks for the feedback.

    Please don't keep shrinking the file, it will fragment the database each time you do, also auto shrink could kick in at busy times, last thing you want. Get the database file down to a reasonable size that will fit your data plus room for growth and the extra space a reindex temporarily uses, and leave it there. Turn auto shrink off on production databases.

    Also optimum fill factor is dependant on amount of inserts and updates which could cause a page split rather than size of table, but you are in a better place than you were!

    ---------------------------------------------------------------------

  • January 26, 2009 at 7:21 am

    #932585

    Hi George,

    Just letting you know the final outcome is that I have the database down to 26 gb now!! I am ecstatic!! And auto shrink is turned off now. But I tell you what, it came through for me when I needed it!!

    Thanks again.

  • January 26, 2009 at 7:29 am

    #932587

    good news. auto shrink (and shrink in general) is not without its uses, thats why its there, but is dangerous if not used with care! 🙂

    ---------------------------------------------------------------------

  • January 26, 2009 at 9:53 am

    #932682

    Cricketdogger (1/21/2009)

    Then I turned on auto-shrink overnight and was very happy to see that this morning the database was only 56 gb (down from 128 gb). I will re-index again tonight and turn on auto-shrink and see if how close I can get to the original 20 gb.

    Do you realise by shrinking you've completely undone the reorganise operation?

    Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

    See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

    Turn autoshrink off. Free space inside a database file is not a problem.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 26, 2009 at 12:22 pm

    #932772

    Gail, thanks for pointing this out, I've covered it with the OP (see previous posts) and I think we have one more DBA educated in the pitfalls of shrinking files. 🙂

    ---------------------------------------------------------------------

  • January 26, 2009 at 12:28 pm

    #932777

    I just wanted to emphasise the fragmented indexes that it would cause.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 26, 2009 at 12:38 pm

    #932784

    no probs,

    ---------------------------------------------------------------------

Viewing 10 posts - 1 through 9 (of 9 total)

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