December 17, 2014 at 1:54 am
Imagine the following imaginary scenario:
- A database in SIMPLE mode.
- A table with a 10TB clustered primary key that is 99% fragmented.
- A disk with plenty of log space for normal operation (1TB).
- Unable to do an INDEX REBUILD as it would require 10-15TB of space.
- INDEX REORGANIZE would be acceptable (as would the small performance impact).
Reorganize should only require a page of free space but it also generates logging while it shuffles around the 10TB of records. This wouldn't normally be a problem in SIMPLE mode but as this is all in one index, one command, one implicit transaction, it will not mark the space in the log inactive until the reorganize is complete. This means it will fill the log disk before it can complete.
Seeing as reorganize is resumable (from what I've read), and having read some other ideas about similar problems (taking additional log backups during long-running operations which isn't applicable in this scenario), I'm testing setting up an SQL alert on the Percent Log Used counter for that database to trigger when it rises over 80%. This will then start an agent job which checks if the reorganize job is running, and if so: stops it, waits a minute, checks if the log space has cleared, and then starts the job again.
I'm just wondering if anyone else has done anything similarly dubious.
(I'm disappointed I made a spelling mistake in the title and can't edit it. Hopefully nobody will notice.)
December 17, 2014 at 6:58 am
codykonior (12/17/2014)
Imagine the following imaginary scenario:- A database in SIMPLE mode.
- A table with a 10TB clustered primary key that is 99% fragmented.
- A disk with plenty of log space for normal operation (1TB).
- Unable to do an INDEX REBUILD as it would require 10-15TB of space.
- INDEX REORGANIZE would be acceptable (as would the small performance impact).
Reorganize should only require a page of free space but it also generates logging while it shuffles around the 10TB of records. This wouldn't normally be a problem in SIMPLE mode but as this is all in one index, one command, one implicit transaction, it will not mark the space in the log inactive until the reorganize is complete. This means it will fill the log disk before it can complete.
from Microsoft's page here http://msdn.microsoft.com/en-us/library/ms177571.aspx:
...However, 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.
So you might have a better time than you expect. Just sayin!
edit: I do think your monitoring idea has merit and obviously watching the logfile during this operation is a pretty good idea.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply