• pawana.paul (8/11/2014)


    We don't have scheduled maintenance due to the nature of environment...

    Heh... I'm afraid to ask what such an environment might be, so I won't ask. 😛

    ONLINE rebuilds of partitioned indexes aren't available in 2008. That first came out in 2014 (IIRC). The best you'll be able to do online is a REORGANIZE, which is always online. The biggest differences between a REBUILD and REORGANIZE are 1) REORGANIZE won't rebuild the B-TREE and 2) REORGANIZE is always fully logged regardless of the Recovery Model being used.

    According to your claim of 1.6TB over 16 partitions, a REORGANIZE of the clustered index on such a table would cause the log file to blow out to more an 100GB. Of course, that would also get backed up to tape.

    There is a very complicated way to do this using SELECT INTO/SWITCH (out and in)/MERGE/DROP but it would cause the MDF to blow out like the log file would have and, considering the size of the partition, the fix would be worse than the problem in this case.

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