• capnhector (11/26/2012)


    ScottPletcher (11/26/2012)


    Of course don't rebuild needlessly; check the stats and only rebuild those indexes that require it.

    Also, with a reasonably well-tuned tempdb, you should strongly consider using the:

    SORT_IN_TEMPDB = ON

    option. This can help reduce fragmentation of the data file as well.

    Even then, you may get a nasty suprise -- after you rebuild the indexes, the db file might be close to the same size it was originally! So be sure to check after rebuilding.

    i think "SORT_IN_TEMPDB = ON" is enterprise only (although i may be completely wrong and only an online rebuild is enterprise only) also i would go ahead and rebuild all the indexes after you shrink the DB file as shrinking the DB file may mess with every index in the DB. just make the rebuild time part of the maintenance down time if you can afford it.

    SORT_IN_TEMPDB = ON does not require Enterprise Edition.

    Yes, the shrink may/might have affected all indexes -- or it may/might have affected none, or only a single, small index. Rebuilding a large index that didn't require it is not only a large waste of resources, but it would also increase data space used, reducing the gains from the shrink, which was the whole point to begin with.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.