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.