Shrinking Reluctant Log Files

Question

I have several SQL Server 2008 databases with transaction logs which have gotten out of hand and filled the drive. One of these logs was 68gb yikes.

I know better than to kill off my backup chain by truncating the log but when I do a shrink alone, on several of the databases I get no reclamation of space whatsoever and on those that I do, the amount is negligible. I have verified that the client is running t-log backups hourly and that these have succeeded for the last several weeks.

Obviously I'm missing something. Can someone point me in the right direction or am I just stuck with having to manually truncate and pray nothing happens in the meantime?

Answer 1

This is one of those exceptions where it's probably okay to execute a DBCC SHRINKFILE. The log file is most likely this large not because of what is going on right now but because there was a time when hourly transaction logs were not being run (or you had an exceptionally large transaction that ran between or across log backups). I'm not sure where the misconception comes from, but backing up the log does not shrink the file for you, it only frees up space within the file.

You can perform a one-time operation like this without interfering with the log chain (or having to "hope" that "nothing happens in the meantime"), preferably right after a log backup has completed successfully:

DBCC SHRINKFILE(yourdb_log, 4000);

This should shrink the file to ~4 GB, which hopefully is plenty of room to accommodate your hourly transactions. You may want to pick a different size, I'm not sure...

If this command doesn't shrink the file as much as you expect, you can check why the log modification may be blocked using:

SELECT log_reuse_wait_desc
  FROM sys.databases
  WHERE name = N'yourdb';