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

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 2

Best practice is to REORGANIZE below around 30% fragmentation and REBUILD above this. Simply, REBUILD makes a clean copy, REORGANIZE 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 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.

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

REBUILD may affect the performance more than REORGANIZE in production, but this can sometimes be mitigated with the ONLINE option (Enterprise Edition required).

Answer 3

I've had this issue before.

• You have a big database and a small log drive. You want to reorganize (for a variety of reasons).
• When you attempt this on a large fragmented table the log fills until the log drive is full and then the command aborts.
• If it's in simple mode other transactions may fail until the log is cleared in the next checkpoint, and if it's in full mode other transactions may fail until the next log backup. Outage!
• If you're in full mode you increase your log backup frequency but it doesn't help avoid the issue because the reorganize is done in an implicit transaction, the log doesn't clear until that transaction finishes or aborts or is stopped.
• And you REALLY want that reorganize to run to completion.

That's a little counter-intuitive because you know if you abort the reorganize it can continue from where it left off, it's just that an abort commits the transaction rather than rolling back.

Here's what you do. It's a little long but straightforward.

• Pre-grow your log file to a relatively large size, but not the maximum. Basically you want to leave enough space to do useful work, plus for some small growths if they occur, so that normal operations won't stop.
• Create a job to run your index reorganize ('Reorganize').

• Create an agent WMI alert ('Reorganize Relief Valve') on a performance condition.

  • Object: SQLServer:Databases
  • Counter: Percent Log Used
  • Instance: (your big database name)
  • Alert if counter rises above: 80
  • Response: Execute job ('Reorganize Check')

• Create a job ('Reorganize Check')

  • In the job check msdb.dbo.sysjobactivity to see if the 'Reorganize' job is running. And if it is...
  • Stop the job and poll until it stops. This can take a few seconds.
  • (If you're in full mode) Trigger your log backup job and confirm when it finishes.
  • Double-check the sys.dm_os_performance_counters that your log free space counter has reduced below your threshold.
  • Start the 'Reorganize' job.

• Test this all somewhere, even a development sandbox, to make sure it performs properly before sticking it onto your production server.

What you'll see is the 'Reorganize' job starts and begins filling the log. When the log hits a percent full it triggers the WMI alert (within about 30s) which runs your other job which sees that the 'Reorganize' job is running and so likely at fault. It then stops 'Reorganize', does a backup, confirms the log free space is back to a reasonable value, then starts your 'Reorganize' job again which will pick up where it left off.

So as you can the reason you pre-size your log to a reasonable figure in this scenario is to reduce the number of growth / trigger / job / stop / restarts, so that it can be more efficient, and also keep enough space for the occasional growths that aren't caught in time.

This is a kind of weird scenario. I'm pretty sure I'd have baulked at this a few years ago and obviously there are fundamental underlying issues at hand here. But if you deal with hundreds of servers a few edge cases like this will crop up that cannot be dealt with in any way, for whatever business reason, except by MacGyvering a temporary solution that gets the job done.

As long as it's safe, logical, tested, and well documented, there should be no problem.

Answer 4

I had the same issue as the question author, and looking at his comments, I can say I had the same setup. While I was trying to do a REORGANIZE, the log grows to full, regardless of size, even to multiple times the size of the entire table.

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.

Once I disabled transactional replication, log backups worked normally again, and doing log backups every 30 seconds while reorganizing worked well for me.

Answer 5

This is what I usually did (I also have a few tables with 80+GB large each) for index reorg (because index reorg can be stopped any time without loosing the previous reorg work).

1. During the index reorg I will increase the tlog backup from my regular 30 min frequency to every 10 min frequency
2. I have another session doing tlog free space check every 1 min, and if the tlog free space is below a threshold, I will stop the index reorg session and start (or wait) the tlog backup. Then restart the index reorg.

In my index maintenance framework, I categorize the indexes to two groups, one is for index rebuild and another for index reorg. For index rebuild, I will use somewhat different approach because I do not want to stop an index rebuild session(which will cause an rollback and lose all the previous work). During index rebuild, if my monitoring session notices a tlog file free space used up scenario, the monitoring session will auto pre-increase the tlog file, and in worst scenario (i.e. disk is full), my monitoring session will create another log file (but later I will drop it) on another drive (the backup drive)

Answer 6

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.