• You're welcome but Gail (Gila Monster) is spot on. Rebuilding all indexes is a lot of overkill and causes a huge amount of logging unless you're in the BULK LOGGED or SIMPLE recovery model (if in SIMPLE, you have bigger problems).

    Also, just arbitrarily assigning a 90% Fill Factor can be a huge waste for anything that has a proper clustered index where the key is narrow, unique, immutable, and ever increasing. It means that you've wasted 10% of the space for all data older than right about now and have slowed your queries and caused them to use 10% more IO on their best days. It's especially important to NOT waste such space on SQL Express.

    Since the code rebuilds the indexes in an OFFLINE manner, the extended run time caused by rebuilding indexes that don't actually need it means that the underlying tables aren't as available as they could be if you only rebuilt indexes that needed it. AND, rebuilding of indexes uses a shedload more resources because any index over 128 extents (that's only 8MB) will be rebuilt and committed before the old index is dropped. For the clustered indexes on large tables, that can be a huge investment in disk space used/database size especially on something small like SQL Express.

    You should also look into selectively reorganizing indexes instead of doing carpet bombing by rebuilding every thing. As Gail, also pointed out, Ola Hallengren has a good backup system that's well documented and downloadable for free.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)