Aug 22

Automated fix for SQL .ldf file growing out of control

Tag: Tech Stuff — Matthew Moeller @ 9:51 am

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

    9. 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.

10 Responses to “Automated fix for SQL .ldf file growing out of control”

  1. Suzie says:
    November 11th, 2008 at 5:44 am

    Hi, We have this problem and the log file reach 27 GB, so if I apply the above steps the log file size will reduce in size?..

    Thank you

  2. Matthew Moeller says:
    November 11th, 2008 at 10:38 am

    Yep, it will reduce it to probably less than 3MB when done. Give it a shot. As usual run a full backup first just in case you made some wrong selections.

  3. suzie says:
    November 12th, 2008 at 3:19 am

    thanks for your reply,
    I did the maintenance plan and the job.
    The sql server agent should be started? or it will start automatically, and shall we schedule the job or not.

  4. Matthew Moeller says:
    November 12th, 2008 at 12:05 pm

    Yes the sql server agent must be running or the maintenance plan won’t execute. And yes I would setup a schedule to run however often you need it to. We run it nightly on our high volume databases only.

  5. suzie says:
    November 13th, 2008 at 8:26 am

    Hi, Hope i’am not bothering you..I followed all the mentioned steps but it didn’t work, nothing start running, even that i schedule them all.
    do you have any idea what shall i do..if you want any details i can give you.

    thanks a lot for your help!!

  6. Alex says:
    December 24th, 2008 at 5:53 am

    For work with sql files try use-fix mdf,usually i use it,and it is free as how as i remember,program repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension),supports data extraction via the local area network,can save recovered data as SQL scripts, it is also possible to split data into files of any size,compatible with all supported versions of Microsoft Windows, such as Windows 98, Windows Me, Windows NT 4.0, Windows 2000, Windows XP, Windows XP SP2, Windows 2003 Server, Windows Vista,tool supports the following database formats: Microsoft SQL Server 7.0, 2000, 2005,also can repair .mdf files of Microsoft SQL Server 2005, repair mdf file of Microsoft SQL Server 2005 (64-bit).

  7. Harish says:
    March 11th, 2009 at 7:50 am

    Hi my dbname.mdf file is large 48 GB!!!!, where as when i take a back up its
    6GB…
    I need to reduce my dbname.mdf
    Can these steps work?

  8. kelleigh says:
    January 22nd, 2010 at 10:57 am

    Thank you for this! It worked perfectly on an .ldf that’s been giving me trouble for months!!

  9. Krugi says:
    April 28th, 2010 at 1:10 am

    This approach does not work in SQL Server 2008. I get this message: “‘TRUNCATE_ONLY’ is not a recognized BACKUP option.” Anyone who has a similar problem and found a way to shrink the file?

  10. Matthew Moeller says:
    April 28th, 2010 at 9:09 am

    You try changing SQL Server Compatibility mode to 80, not sure though.
    Available Modes are:
    60 = SQL Server 6.0
    65 = SQL Server 6.5
    70 = SQL Server 7.0
    80 = SQL Server 2000
    90 = SQL Server 2005
    100 = SQL Server 2008

Leave a Reply