Post reply

Rebuilding Index SQL server 2000

  • May 19, 2009 at 5:51 am

    #103739

    I have configured database maintenance plan for rebuilding index every week sunday after complete of Full backup, so currently database size 340 GB, completing of rebuild jobs db size have been increased more than 8GB. i think this is sql server behaviour for increse db size after complete of rebuilding index.

    I have mention for rebuilding index on maintenance plan as follows.

    Reorganize data and index page

    I have mention first option - Reorganize pages with the original amount of free space.

    In second option - change free space per page precentage to.

    please can any one give me correct option , If i choose second option how much precentage mention for change free space and Fillfactor values , currently db size 340 GB, Rebuilding jobs runs every week on sunday 3PM after competed of full db backup.

    Thanks Advance.

  • May 19, 2009 at 6:05 am

    #996858

    Normally the first option is the way to go.

    If you are worried because the database grew that is what happens in normal running. You'll find it has some free space in it now.

    You can think of it as though it copies each table in the correct reorganised sequence. When fully copied it deletes the fragmented one.

    So it needs some room to do that.

    Tim

    .

  • May 19, 2009 at 6:26 am

    #996868

    Thanks Tim for your reply....

    how to identified When fully copied it deletes the fragmented one?

    so we need to check dbcc showcoting command before rebuild index and after rebuild index then identified logical frag, level.

    Thanks

  • May 19, 2009 at 6:36 am

    #996876

    It depends what you want to do. What you are describing is just normal behaviour for the maintenance plan.

    What will happen here though is that all the tables will be rebuilt whether they need it or not.

    You can be more clever using the DBCC SHOWCONTIG and DBCC DBREINDEX commands on each table and only rebuilding when the table looks fragmented.

    If you look up DBCC SHOWCONTIG in BOL (SQL Books Online) there is some sample code on how to do it. Beware though of using DBCC INDEXDEFRAG rather than DBCC DBREINDEX - it does a huge amount of logging.

    If you are not sure, sticking with the maintenance plan is fine providing you have time to run it.

    Tim

    .

Viewing 4 posts - 1 through 3 (of 3 total)

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