801-545-0410

Automated fix for SQL .ldf file growing out of control

August 22, 2008

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.

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

  5. suzie

    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!!

    November 13, 2008 at 8:26 am
    Reply
  6. Alex

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

    December 24, 2008 at 5:53 am
    Reply
  9. Krugi

    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?

    April 28, 2010 at 1:10 am
    Reply
  10. Andy

    For people using SQL 2008, the “TRUNCATE_ONLY” option was deprecated.
    Instead, the method required is as follows :
    1) Determine the name of the file you wish to shrink using sp_helpfile
    2) Change the database to Simple mode.
    ALTER DATABASE SET RECOVERY SIMPLE;
    2) Shrink the database file as mentioned in the post.
    DBCC SHRINKFILE(, 100)
    (the 100 refers to the target file size after shrinking. I suggest around 10% of the MDF file size).
    3) Put the database back into full recovery mode (if it was that in the first place)
    ALTER DATABASE SET RECOVERY FULL;

    Be aware that this breaks the backup chain so subsequent transaction log backups or differential backups will fail until you perform a new, full backup.

    September 7, 2012 at 8:34 am
    Reply
  11. Andy

    Please read corrected version (previous post skipped all parenthesis).
    For people using SQL 2008, the “TRUNCATE_ONLY” option was deprecated.
    Instead, the method required is as follows :
    1) Determine the name of the file you wish to shrink using sp_helpfile
    2) Change the database to Simple mode.
    ALTER DATABASE databasename SET RECOVERY SIMPLE;
    2) Shrink the database file as mentioned in the post.
    DBCC SHRINKFILE(datafile_log, 100)
    (the 100 refers to the target file size after shrinking. I suggest around 10% of the MDF file size).
    3) Put the database back into full recovery mode (if it was that in the first place)
    ALTER DATABASE databasename SET RECOVERY FULL;

    Be aware that this breaks the backup chain so subsequent transaction log backups or differential backups will fail until you perform a new, full backup.

    September 7, 2012 at 8:35 am
    Reply

Leave a Reply