Home Forums SQL Server 2005 Administering Best Index Rebuild/Reorganize and Update Statistics Strategy RE: Best Index Rebuild/Reorganize and Update Statistics Strategy

  • Just to add to what has already been said...

    You don't actually need to take a full backup to get thing back to a normal Point-In-time backup status after going to the Bulk Logged or even the SIMPLE recovery mode. You can simply change back to the FULL recovery mode if in Bulk Logged and you only need to do a DIFF backup if you were in the SIMPLE recovery mode. Of course, I do a FULL backup on my relatively small (only 200GB) databases every night anyway. Just remember that even in the Bulk Logged mode, a Point-In-Time restore can't be done for any log file where you were in the Bulk Logged mode. You can only use the whole logfile backup or not during those time frames.

    You can save a whole lot of "growth" during index rebuilds if you partition (Table Partition or Partitioned View) the large tables and related indexes because they'll be treated as much smaller individual units. It takes a bit to set them up and a bit to setup the code for automatic maintenance on them but it's well worth it. If you do some "tricks" with using different file groups on the partitions, it can also allow for "Piecemeal Restores" where you can get the core of a database backup up and running very quickly (not initially loading large log/audit tables, for example) and then loading larger less important data over time after the initial restore.

    Partitioning can also be a real time save for both index maintenance and backups. For example, if you have large audit tables, you don't have to rebuild the indexes on the temporally stagnant partitions (I divide them up by month) and you don't have to back them up but once or twice if they're in a separate file group for each partition.

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