21

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.

6 Answers 6

9

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.

2
  • 2
    i am doing reorganize. the recovery model is full and i do see a large transaction log size. the reason for the failure is one of the two 1. mirroring. log needs to be pushed to the secondary before space can be reclaimed 2. log backups. Even though we take backups every 15 mins it sometimes fails due to this reason.
    – Sriram Subramanian
    Jan 4, 2012 at 5:16
  • Same situation here, 2008R2, index reorganize (not rebuild), and even in simple mode (!) the tlog grows to more than the size of the biggest table which is > 40 GB. When in full recovery mode, doing 5min tlog backups doesn't help either, only one huge TRN backup file is produced during the index reorg. It doesn't seem to make sense but it's the same behavior on two different servers. Any idea how to actually split this in small batch transactions as documented? Jan 3, 2017 at 18:17
9

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).

1
  • Rule-of-thumb numbers and talking in both qualitative and quantitative terms is very helpful.
    – Robino
    May 3, 2018 at 9:56
7

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.

2
  • I'm trying to implement the 'Reorganize Check' job as Cody suggested. Would someone mind to share this job (code)?
    – Rino
    Feb 19, 2020 at 9:28
  • I looked but don’t have the source code. It is probably owned by an employer which is why I described it in detail for others to recreate. Good luck. Feb 19, 2020 at 15:54
2

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.

1

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)

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.