none
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

Answers

All replies

  • You might find these resources useful:

    FileSize -How to stop the log file from growing
    http://www.support.microsoft.com/?id=873235

    FileSize -Log file filling up
    http://www.support.microsoft.com/?id=110139

    FileSize -Log File Grows too big
    http://www.support.microsoft.com/?id=317375

    FileSize -Log File issues
    http://www.nigelrivett.net/TransactionLogFileGrows_1.html

    FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
    http://www.support.microsoft.com/?id=272318

     

    Thursday, March 15, 2007 11:03 PM
    Moderator
  • 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

    (a)

    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

     

    Madhu

    • Proposed as answer by hefhem Wednesday, May 28, 2014 8:58 AM
    Friday, March 16, 2007 4:49 AM
    Moderator
  • 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.

    Thanks!

    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