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.

This question already has an answer here:

I am currently in an live environment and I am trying to clear some disk space. I noticed that a SQL Server database transaction log file is kinda big (12GB).

The recovery model of that database is set to simple. How can I decrease its size without killing any data or wrecking havoc on the server?

share|improve this question

marked as duplicate by Aaron Bertrand Sep 25 '13 at 13:25

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer 1

See disclaimer at the end.

First you need to figure out if there is a reason that the log is large/growing. A quick way to do that is to run this query (see http://sqlity.net/en/556/t-sql-tuesday-25-%E2%80%93-sql-server-tips-tricks/ for more info):

SELECT log_reuse_wait_desc,name FROM sys.databases;

If that comes back with "NOTHING" in the log_reuse_wait_desc column for your database you can go ahead and shrink the log file. The easiest way to do that is using SSMS. Right-click your database and select "Tasks", "Shrink", "Files". That brings up this dialog:

Shrink File Dialog

Select "Log" as File Type and select "Release unused space". Click OK to execute.

DISCLAIMER: If you follow these steps, you are doing a fairly un-intrusive operation. However, repeated shrinking and growing can have very bad effects on your database performance. So if your file is 12GB because that is the size needed to support normal operations you are better off leaving it that way. As there is no real good way to determine the necessary size (other then letting it grow) I would leave the file alone unless you are running out of disk space. There is no negative performance impact for having a too big log file.

share|improve this answer
Well, I would say that there can be a negative performance impact on the log file being too big, especially if it got to 12 GB size one MB (or 10%) at a time (and if it continues having to grow this way). The number of virtual log files in this case will be excessive and will hurt most if you ever have to restore... –  Aaron Bertrand Sep 25 '13 at 13:26

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