Reduce SQL Log File Size


Reducing Log File size when CDC or replication is enabled

So recently I went to make a copy of a database that had CDC enabled for certain tables.  It was a non-production copy, so I didn't need CDC in play.  However because the original database had CDC I found that the Transaction Logs couldn't be truncated because there was stuff that hadn't been captured yet.

Luckily I found this Microsoft article:

After executing the following command I was able to truncate the log:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1


Reduce Transaction Log File Size

  • Background information on transaction logs
  • Reducing transaction log file size
  • Reduce disk space taken up byTransaction Log Files

The scenario

We have a non-production database at the grind that should be in Simple Recovery, but it's ummm...not.  I'm hesistant to change it though because the corresponding production database is rightfully configured for Full Recovery and we use this environment for stress testing and other scenarios.

Consequently there's a few .ldf files that house a few unchecked transaction logs and are taking up a boatload of disk space. 

So periodically we have to clean up disk.  There's four steps (described in detail here):

1. Back up the transaction logs.

2. Run a CHECKPOINT command to flush dirty buffers to disk.

3. Back up your transaction logs (yes, again)

4. Shrink the logs until the .ldf files are down to a manageable size.  (Shrinking logfiles is fine as long as you've backed them up whereas shrinking data files is bad so only do this on non-production environments.)

For steps one, two, and three do this:

Now shrink the file: 


Shrinking the datafiles (different than the transaction logs) causes massive resource overhead, so don't do it. Don't ever implement AutoShrink.

Lots of people smarter than me recommend not shrinking datafiles and here's just one of many.

Back to the subject at hand (and described above): shrinking log files. The first backup will hopefully clear the majority of unused virtual logs within the transactionlog. The second backup operation will hopefully clear the remaining few (which are more likely to be at the end of the physical file the older your SQL Server version is). I say "hopefully" because there could be a long-running transaction or someone who forgot to commit a large transaction that is tying up the Virtual Log Files and preventing you from clearing them. The CHECKPOINT operation flushes the dirty pages to disk, so by the culmination of the second backup you should have exhaustively purged the .ldf transaction log physical file whereupon it'll respond to step #4. If CHECKPOINT isn't running frequently automatically in the background, however you have bigger problems. Calling it out above is just so you're not sitting around waiting for it.

Now if you'll excuse me I should really get back to switching my recovery model...