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.

Some considerations if leaving the recovery method as simple:

No comments:

Post a Comment