If you cannot get that d@mn log file to shrink, here’s a neat trick I learned from Christian Arheim:

1. Make a backup of the files. MS Backup will do if you do not have anything else or if you are in a hurry.
2. Open MS SQL Enterprise Manager
3. Open the tree on the left hand side to reveal the database owning the offending log file
4. Right-click it and select Properties
5. In the left-hand-side of the window appearing, select Options
6. To the right, locate Recovery model and select Simple (and – important – make a note of what it was)
7. Select OK
8. Right-click the database again and this time select Tasks – Shrink – Files
9. In the window appearing select File type to be Log and select OK
10. After shinking finishes, you must now reset the recovery model. Do this:
11. Right-click database and select Properties
12. In the left-hand-side of the window appearing, select Options
13. To the right, locate Recovery model and select what noted it to be in #5 above. You forgot to write it down? You FAIL!
14. Select OK

The log should now be down to an absolute minimum for this database.

Categories: MS SQL Server