Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Unanswered: Full T-Log: options?

    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?

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    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.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by tomh53
    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
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •