The index rebuild operation runs by default in a single long-running transaction, that prevents the Transaction Log space reuse while rebuilding a large index.
Switching to the BULK_LOGGED recovery mode while doing an index rebuild operation will reduce the amount of transaction log generated. If you’re considering using the BULK_LOGGED recovery mode, beware that you lose the ability to do point-in-time recovery to ANY point covered by a transaction log backup that contains even a single minimally-logged operation. The operations you should perform if you’re going to do this are:
- In FULL recovery mode, take log backup immediately before switching to BULK_LOGGED.
- Switch to BULK_LOGGED and do the index rebuild.
- Switch back to FULL and immediately take a log backup.
Yes, rebuilding indexes will take a toll on your transaction log file. Particularly if you are in full recovery. Basically, an index rebuild copies the index to another place. And if it is a clustered index, the entire table is copied.
An index REBUILD is slow, expensive, consumes a humongous amount of log (if is not offline and cannot be minimally logged, online rebuild cannot be minimally logged), is a single giant transaction and cannot be interrupted without loosing all the work.
On larger tables (50GB table is getting there) I’ve seen REORGANIZE consume all transaction log space if you follow this rule. Not often: only one system with a certain load pattern. The REORGANIZE just ran until the log expanded and consumed all disk space.
The problem was caused by transactional replication. Apparently, the log cannot be backed up until the REORGANIZE operation is finished. I read somewhere that it was a known problem by Microsoft, but I’m not sure where.
Reorganize vs Rebuild
Best practice is to REORGANIZE below around 30% fragmentation and REBUILD above this. Simply, REBUILD makes a clean copy, REORGANIZE does it in-situ.
Comments