4

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?

2
  • How are you performing "a shrink alone"? Can you show the syntax you used and when you ran these commands? Aug 7, 2012 at 12:52
  • My answer here may be of interest, in addition to Aaron's answer.
    – Jon Seigel
    Aug 7, 2012 at 23:06

1 Answer 1

7

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';
2
  • Thanks Aaron...I had been doing this through the GUI. The script worked perfect.
    – PseudoToad
    Aug 7, 2012 at 12:55
  • Yeah don't use the UI for this. It does silly things. Aug 7, 2012 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.