Wednesday, April 24, 2013

MSSQL: Shrink transaction log

Common issue, right. Let's do it.

1. Go to your database in Management Studio.
2. Right click on the database and select Tasks -> Backup. Choose Transaction Log as Backup type.

3. When backup is taken, right click on database, select Tasks -> Shrink -> Files. Choose File type Log.
In 95% of cases that will do the trick.

In 5% of unlucky events, log file is not getting less, but there is no error displayed. If you have that, click Script on the previous screenshot (Shrinking log file). And then run script to do the job, and click on Messages at Output window.
You will see smth like this: 
Cannot shrink log file 2 because the logical log file located at the end of the file is in use.

Then you need some extra steps.
2a. Right-click your database and select Properties. At Options tab set Recovery model to Simple.
Do Step 3.
3a. Set recovery model back to Full. 

Good luck!

2 comments:

  1. Worked. Although, as the database had never been backup up before, I had to do a full backup and not just a log backup.
    2.3Gb freed in a vm; 10/10 would do again.

    ReplyDelete