Utah Web Design Blog

If you work with MS SQL server and have databases that handle lots of transactions then you no doubt have encountered the .ldf file growing to insane sizes if left unchecked. A large LDF file will result in the web application running extremely slow or most likely just timing out, not to mention affecting the performance of other databases setup on that same server. We used to manually detach the database rename the .ldf, once you re-attach the .mdf file it would start up a new .ldf log file and things would run speedy once again. While that worked it resulted in downtime, wasted resources, and unhappy clients.

Here is a solution that works perfectly and is automated. (assuming sql server 2000 or 2005 standard here)

1. Launch Enterprise Manager and open Management
2. Right click on Database Maintenance Plans > New plan
3. Pick all your options to backup your database with the schedule and save the plan
4. Drop down SQL Server Agent
5. Right click on Jobs > New Job
6. Give it a name and click on the second tab “steps“
7. Add a second step under the initial backup by clicking New
8. Choose Type TSQL, pick the database, and paste in this command

BACKUP LOG dbnamehere WITH TRUNCATE_ONLY
DBCC SHRINKFILE(dbnamehere_Log, 1)

9. Save it, click on step 1 hit adavanced and set “On success action:” to “Goto Step: 2” or it won’t run.

We have a log file that used to run to 18 to 30GB quickly and now it is 1MB on a daily basis, pretty sweet and with no real down time.  We have tried backing up the log and tried the built in shriking options with no luck, never worked.  Sure there are plenty of other methods you can use, hope this helps someone else out there facing the same problem.