Log (LDF) File Grows Continuously – SQL Server Backup and Log Management

backuplogssql server

We have a problem with just one of the databases on our SQL2008 Server. For some reason recently the log file is just growing and growing.

The server runs the following backups

  • Weekly – Full
  • Daily – Diff
  • Hourly – Transactional

I have followed the information here (Why Does the Transaction Log Keep Growing or Run Out of Space?) but without any luck and have confirmed the following:

  • The database is in FULL recovery mode
  • Backups are occuring as expected (Hourly, Daily, Weekly)
  • The log file is full, it's that that there is space that can be recovered
  • Looking at the log_reuse_wait_desc returns 'NOTHING'

The website associated with the database is the most used site we have, but the database and usage is still small (under 5Gb of data) a few hundred users a day, but the Log file gets over 10Gb after a couple of days.

Anyone any idea what could be causing this? The only way I can find to get around this is to switch to 'SIMPLE' recovery mode. Shrink the file, switch back to 'FULL' then do a new Full backup to start a new chain.

Thanks for any advice you can provide

Best Answer

Conceptually this is what should be happening within your database when the end of the physical file is reached:

enter image description here

However, it sounds like you have a log header that doesn't want to wrap back around to the start of the physical log file and continues to fire off auto-growth events instead. This can occur for any number of reasons, but the most common is a vlf in front of the logical log has a DBCC LOGINFO status of 2 for whatever reason, even after switching recovery models as you're doing now, but the way to fix it is the same regardless of the reason. Simply perform the following steps:

  1. Perform a Log Backup (e.g. BACKUP LOG [DBNAME]...) against the database
  2. Perform a DBCC SHRINKFILE operation on the transaction log file specifying 1 MB as the target size
  3. Perform another Log Backup against the database
  4. Perform another DBCC SHRINKFILE operation on the transaction log file specifying 1 MB as the target size
  5. Manually increase the size of the log file, incrementally per Kimberly Tripp's article, to grow the TLog back to an appropriate size.

So what's happening here?

  1. The first TLog backup simply forces a CHECKPOINT operation and flushes the log to disk.
  2. The first DBCC Shrinkfile operation shrinks the physical TLOG down to where the current logical log is located
  3. The second Log Backup issues another checkpoint and will likely backup/clear any resident log operations located in front of the logical log. These were never flushed in the past for whatever reason, but should be cleared at this point. This step is critical to fixing your issue and sometimes this backup can take a bit of time depending on how much data needs to be written down to disk.
  4. The second DBCC SHRINKFILE operation moves the logical log file to the head of the physical log file, eliminates excessive VLFs that are likely there now, and reduces the TLOG file back down to a very small size, in this case 1MB. It is imperative to manually grow the file in step 5 so you don't run into vlf issues again down the road
  5. Manually increasing the size of the tlog will allow you to control the size and number of VLFs within your physical log file. This is an opportune time to configure this properly, so take advantage.

One final note on the process. If your tlog is highly active, you may need to perform this a few times or during a window of reduced activity. High activity may result in another errant auto-growth event occurring before you execute the second log backup. This basically turns what you thought was step 3 back into step 1. Generally Steps 1 and 2 go by quickly, and step 3 takes the longest to complete. Make sure to follow-through with steps 4 and 5.

Related Question