Shrinking SQL2005 log files

So when I migrated our SQL2000 server to SQL2005 – which was primarily used as our back-end Goldmine server – I stupidly configured the logging to Full restore mode. In 3 months of use the log file grew from 100MB to 28GB. This caused problems with disk utilization (obviously!)

To fix this is quite straight forward.

Firstly, open SQL Studio Manger 2005 and expand out the Maintenance folder. Right click and choose Create new Maintenance plan. Configure the maintenance plan to Backup the log file. This will require the same amount of space as your log file (the .ldf file in Windows Explorer). Once the log file is backed up, we can shrink the log file.

Right-click on the database and choose All Tasks > Shrink > Files. In the dialog box that opens, choose Logs from the drop down menu (it defaults to Data). There will be an indication of how much free space is available in the particular file – in my case 99%!

Now run the shrink. Don’t be scared to run the shrink while the server is live – there shouldn’t be any problems as the .ldf file only stores the transactions that have been performed (of course follow my procedure and backup the log file first!)

The process will run – and it shouldn’t take too long. Once this has been completed check the ldf file size… it should be significantly smaller. In my case the log file changed from 28GB to 1MB!

This list of fairly simple steps will save you plenty of server storage – however tread carefully. Never make any changes to you SQL configuration without a confirmed working backup. I always try to err on the side of caution and perform any SQL maintenance after hours while the server isn’t in heavy use.