An automatic monthly delete has recently grown from 15 to 20 million rows. It is now filling my 70GB T-Log completly. I don't have any space to expand the T-Log. Do I have any options other than reducing the number of rows in the delete?Is the database in full or simple recovery mode?
In either case, batch your deletes in small chunks instead of trying to do them all at once (say 1M or 100K or 10K at a time).
If you are in simple mode, when the batch finishes, a database checkpoint will occur and the log will be truncated and the space allocated for reuse.
If you are in full recovery mode, perform a tran log backup before starting the next batch and the log will be truncated and the space allocated for reuse.|||In either case, batch your deletes in small chunks instead of trying to do them all at once (say 1M or 100K or 10K at a time).
x2.
Also, you should set a performance alert on you transaction log to kick off when the log reaches xx% full. Have the action of the alert set to kick off a transaction log backup. This will NOT work unless you batch your deletes into smaller quantities, but it should work well in conjunction with batching.
For example, my db is set to do a custom defrag Sunday nights. The defrag is broken up into about 50 steps and takes about 45 minutes. During the process, about 50 GB of data is written to the t-log (which is 35 GB in size). I have the performance alert set to trigger when the t-log is 60% full with a ten minute interval set between responses.
This way, when the log file gets to about 60% full, the transaction log backup kicks off automatically. The process repeats itself until the defrags are complete.
Be SURE to set the response interval to something realistic; I once forgot and watched as the response kicked off every second (for something different) and there was nothing I could do to stop it.
Regards,
hmscott
没有评论:
发表评论