Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Aug 7 '12 at 12:50

This question came from our site for professional and enthusiast programmers.

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

1 Answer 1

up vote 7 down vote accepted

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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