How to reduce .ldf log file size RRS feed

  • Question

  • I have a SQL Server 2005 database with a data file that's 156MB. It's corresponding .ldf file is 5.6GB!! At most, I need about 1% of the data that's in there. Is there anything I can do to reduce the size of this file? I've looked everywhere I can think of for settings to reduce what's logged and how long the logs are kept. I've set what few things I can control to the minimums. What I'd prefer is to be able to periodically flush it and start over.

    Thursday, March 15, 2007 8:24 PM


All replies

  • You might find these resources useful:

    FileSize -How to stop the log file from growing

    FileSize -Log file filling up

    FileSize -Log File Grows too big

    FileSize -Log File issues

    FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE


    Thursday, March 15, 2007 11:03 PM
  • This is a common problem in sql server. You need to understand few things in sql server like  Recovery model, Transaction log physical architecture, How to keep the TL size under control and how to shrink the transaction log. If you are not taking Transaction log backup and you don't need to recover to a point in time ... then first thing you should do is change the recovery model to simple... If you want to have point in time recovery (STOPAT) then you should schedule the transaction log backup more frequently to keep the size within limit. Now since the log size is grwon over a period of time and if you need to reduce the size then follow these steps


    backup log Somedatabase with truncate_only -- This statement will mark inactive part of log


    (b) dbcc shrinkfile(DevGrantPlan_Log)   -- This statement will release the marked inactive space to OS


    (c) soon after this , the first step you must do is to backup the database. otherwise the backup chain is broken and you will not be able to use the TL backup for recovery


    read the article mentioned by Arnie and it will give you more clear picture



    • Proposed as answer by hefhem Wednesday, May 28, 2014 8:58 AM
    Friday, March 16, 2007 4:49 AM
  • This options works well for me!!! the log file was 185GB and reduced to 1MB..

    Monday, February 4, 2013 1:54 PM
  • It works great for me. I could reduce a log file from 190 Gb. to 15 Mb.


    Monday, May 20, 2013 3:21 PM
  • Thanks a lot, you clears the confusion i have since 3 years.
    Tuesday, May 13, 2014 5:02 AM
  • *winks* problem solved. Thanks man
    Wednesday, May 28, 2014 8:59 AM