Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

migrated from stackoverflow.com Jan 4 '12 at 9:16

This question came from our site for professional and enthusiast programmers.

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.

share|improve this answer
1  
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 '12 at 5:16
2  

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

share|improve this answer
    
the issue with online rebuild is that it affects the performance more than reorganize in production. – Sriram Subramanian Jan 4 '12 at 7:19
    
@SriramSubramanian: correct, but with ONLINE=ON you can mitigate this. But running out of disk or LDF space will spoil your days even more... – gbn Jan 4 '12 at 7:21
    
what is the guarantee that rebuild produces less logs – Sriram Subramanian Jan 4 '12 at 7:41
    
"On larger tables (50GB table is getting there) I've seen REORGANISE consume all transaction log space" That does not really tell anything because we do not know anything about the maximum space for the TL available in this context. – Magier Feb 4 at 8:12

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.

share|improve this answer

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)

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.