How to prevent transaction log getting full during index reorganize?

Question

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.

Answer 1

Best practice is to REORGANISE below around 30% fragmentation and REBUILD above this. Simply, REBUILD makes a clean copy, REORGANISE does it in-situ.

Check what you're actually doing: you don't have a maintenance plan doing both do you?

On larger tables (50GB table is getting there) I've seen REORGANISE consume all transaction log space if you follow this rule. Not often: only one system with a certain load pattern. The REORGANISE just ran until log expanded and consumed all disk space.

We switched to REBUILD instead with no more problems, but ignore fragmenation below 25%. This worked better for us: you'll have to see if it works for you...

Answer 2

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.

Answer 3

I am assuming you're running something along the lines of:

ALTER INDEX ON REORGANIZE

Unfortunately, there is no way to run a partial organize (the way you can partially shrink a log file for example). The ways I can think around this problems are:

1) set the database to simple recovery mode while you run the reorganize, but you said that isn't acceptable

2) partition the index - if can think of a way to partition the index to get approximately equal sized partitions, you will then be able to re-organize (or rebuild with online option) each partition independently and thus limit log file growth

I am sure you're doing this, but if you're not, you may want to initiate a log file backup before and after you do any index optimizations, which will allow it to reclaim used space.