codykonior (12/17/2014)
Imagine the following imaginary scenario:- A database in SIMPLE mode.
- A table with a 10TB clustered primary key that is 99% fragmented.
- A disk with plenty of log space for normal operation (1TB).
- Unable to do an INDEX REBUILD as it would require 10-15TB of space.
- INDEX REORGANIZE would be acceptable (as would the small performance impact).
Reorganize should only require a page of free space but it also generates logging while it shuffles around the 10TB of records. This wouldn't normally be a problem in SIMPLE mode but as this is all in one index, one command, one implicit transaction, it will not mark the space in the log inactive until the reorganize is complete. This means it will fill the log disk before it can complete.
from Microsoft's page here http://msdn.microsoft.com/en-us/library/ms177571.aspx:
...However, the defragmentation is performed as a series of short transactions, so a large log is unnecessary if log backups are taken frequently or if the recovery model setting is SIMPLE.
So you might have a better time than you expect. Just sayin!
edit: I do think your monitoring idea has merit and obviously watching the logfile during this operation is a pretty good idea.