Thursday, 22 August 2013

Reducing MSSQL log file size, easy peasy!

I've been doing a lot more MSSQL maintenance lately and have a little script I run to clear up space where the log files have grown excessively large. Copy and paste the code below into a new query within Microsoft SQL Server Management Studio, changing DBname to the database name with the large log files and DBname_log to the name of the log file.

If you can, take a backup first. Obviously you're about to shrink the logs for a reason and if they're large a backup may not be an option if you've run out of disk space, but do try to if you can.

use DBname;
alter database DBname set recovery simple;
dbcc shrinkfile (DBname_log, 1);
alter database DBname set recovery full;


Now go take another backup!


You may want to review recovery methods to find which may work the best for you.
http://technet.microsoft.com/en-us/library/ms189275(v=sql.105).aspx


Some considerations if leaving the recovery method as simple:
http://technet.microsoft.com/en-us/library/ms178052(v=sql.105).aspx


No comments:

Post a Comment