Search code examples
sql-server-2008transaction-log

Is there an alternate strategy for truncating a large transaction log? (> 200gb)

I am about to attempt one of the other answers for similar questions, but wanted to ask if truncating the transaction log will differ for a log that is 200gb vs a lot of the examples I've seen around 20gb?

The other questions take a 20gb transaction log and reduce it to 1mb, does the strategy differ for a transaction log that is 10x the size?

Solution

  • "Truncating the log" is an ambiguous term. I'm assuming you mean shrinking the transaction log's LDF file with DBCC SHRINKFILE.

    No, the process doesn't significantly differ.

    Remember: You'll likely need to shrink the log file twice to get it to shrink to your desired size because your transaction log is going to be in use.

    1. Run DBCC LOGINFO for the desired database, and you'll see each VLF (virtual log file) in the transaction log. Anything with a Status of 2 is active and awaiting a log backup. If you run a DBCC SHRINKFILE without doing a log backup, the minimum size you'll be able to shrink to is the largest StartingOffset + FileSize where the Status is 2.

    2. If you run a transaction log backup, the VLF that's currently being used will remain with a Status of 2. It's still being used, so it's still active. As far as I'm aware, the VLF listed in DBCC LOGINFO that has the largest FSeqNo is the VLF that is currently being used. That means that if you do a LOG BACKUP and immediately run DBCC SHRINKFILE, the minimum size is going to be the StartingOffset + Filesize of the VLF with the largest FSeqNo. You must wait for the VLF to fill up and the server switches to a new VLF before a log backup will mark this VLF as inactive, which will then allow you to DBCC SHRINKFILE smaller.

    So, the best strategy for shrinking a log file is:

    1. Analyze your database and activity to figure out what went wrong and why.
    2. Determine the optimal size for your transaction log based on your usage.
    3. Run BACKUP LOG.
    4. Run DBCC SHRINKFILE.
    5. Determine how much the log file shrank.
    6. If your log file is the correct size, you're done.
    7. Otherwise, monitor DBCC LOGINFO to see when the VLF has rolled over into a new VLF earlier in the file.
    8. Go to 3.

    If you need to generate transaction log data to speed up #6, consider running index rebuilds.

    Note that #1 & #2 above are only possible if you've been managing the server correctly. You'll need to revisit the server in the future to be able to complete them, but they're vital to preventing problems from cropping up in the future.

    I'm also assuming you're not running Simple recovery mode, because a 200 GB transaction log on a database with Simple recovery seems relatively unlikely. If by some unlikely chance you are running Simple recovery, then you can replace BACKUP LOG above with the CHECKPOINT command.