How to prevent transaction log getting full during index reorganize?

We have multiple machines where we have pre allocated the size of the transaction log to 50gb. The size of the table that I am trying to reorganize is 55 - 60 gb but is going to continuously increase. The main reason I want to reorganize is to reclaim space and any performance benefit because of that is an added bonus.

The fragmentation level of the table is 30 - 35%. On some of these machines I get "transaction log full" error and reorganize fails. The transaction log size reaches upto 48gb. What is a good way to counter this? We do not have auto increment switched on and I am reluctant to do so.

I can increase the log size to a larger value but as the table size increases in the future, the value may not be enough. Also it defeats the purpose of doing reorganize to reclaim space if I am going to increase the log size equally. Any ideas on how I can effectively counter this? Using bulked mode is not an option since data loss is not acceptable.

REORGANIZE (as in ALTER INDEX ... REORGANIZE) is a very fast operation (well, mostly...), which require small amount of log, can be interrupted at any moment and resumed later, and works internally in small batch transactions:


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.

Are you sure you aren't talking about a rebuild? 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.

It seems to me that you're doing a rebuild, which is a really exceptional operation you should not do unless you have extremely well thought reason. What kind of space reclaim are you hopping for? Anything that DBCC CLEANTABLE won't handle? Have you checked the table physical structure, has it drifted from the logical structure (see SQL Server table columns under the hood for details)?

If you really have to rebuild the table then I'm afraid you have no choice but to bite the bullet and allocate the necessary log. Don't let it auto grow, it will only slow down the process. Pre-grow it to 2.5 times the size of the table.

If the table is partitioned then you can rebuild offline (and reorganize) one partition at a time. Online rebuild can only be done at the entire table level.

StackExchange Database Administrators Q#10120, answer score: 9