An "ALTER INDEX ALL REBUILD" operation on SQL Server 2012 failed because the transaction log ran out of space. The indexes have never been reorganized or rebuilt, so fragmentation is over 80% on nearly all of them.
The DB uses simple recovery model. I assumed that following each index operation performed by the "ALL" form of the command, the transaction log data would be flushed prior to the next index rebuild. Is that how it actually works, or are the index rebuilds logged as if they are part of a single transaction?
In other words, could I reduce transaction log growth by writing a script to perform each rebuild individually? Are there any other factors to consider?
Best Answer
1) Log flushing: the SIMPLE recovery model does not clear the log after every transaction, but at checkpoints. (link for more info)
2a) REBUILD ALL: yes, REBUILD ALL works as a single transaction. The index rebuilds within have their own transactions, but the overall operation isn't fully committed until the end. So yes, you might limit log file growth by rebuilding individual indexes (and possibly issuing CHECKPOINT commands).
2b) Proof! Here, have a demo script. (Built in 2016 dev) First, set up a test db, with table and indexes:
Now you can compare log activity between REBUILD ALL and rebuilding individually
Note how the first open transaction (Transaction ID 0000:000002fa for me) isn't committed until the end of the REBUILD ALL, but for the index-by-index rebuilds, they are successively committed.