0

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?

marc_s
  • 8,932
  • 6
  • 45
  • 51
Martin
  • 1

1 Answers1

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.

Sebastian Meine
  • 9,125
  • 1
  • 27
  • 32
  • 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