none
LDF file growing too big RRS feed

  • Question

  • I have sqlserver 2008.
    My ldf file is growing too big
    Now its 126 GB and my mdf file is only 328 MB.
    I need to reduce the size of LDf file. How to do it?

    If I’m shrinking the ldf file the size is coming to 16 GB.
    And I gave the autogrowth option checked for ldf file and file growth given as 100 MB.
    i opted maximum file size as 2000 GB.

    Is this setting ok? Whenever new transaction happens the log file will grow beyond 16 GB right?
    Any help is appreciated.
    Monday, December 7, 2009 6:00 PM

Answers

  • If you don't take backups of the log files, because you don't need to have the ability to restore to a point-in-time, change the database to SIMPLE recovery and then shrink the log file.


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Monday, December 7, 2009 7:32 PM
    Moderator
  • thanks for the reply
    so you mean to say that after shrinking, change the recovery mode to Simple?
    i don't take the backups of log files.

    FYI Just be aware that if you change to simple recovery mode you can only recover to the point of your last backup. Backing up the logs allows you to recover to or roll back to a point in time. The question you need to ask yourself is "What is an acceptable amount of data loss?"
    Monday, December 7, 2009 10:50 PM

All replies

  • If your database is in the FULL recovery mode, are you taking frequent Log backups?

    If you don't need the ability to recover to a point in time, change the recovery mode to SIMPLE for the database in question.
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Monday, December 7, 2009 6:03 PM
    Moderator
  • thanks for the reply
    so you mean to say that after shrinking, change the recovery mode to Simple?
    i don't take the backups of log files.
    Monday, December 7, 2009 7:19 PM
  • If you don't take backups of the log files, because you don't need to have the ability to restore to a point-in-time, change the database to SIMPLE recovery and then shrink the log file.


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Monday, December 7, 2009 7:32 PM
    Moderator
  • thanks for the reply
    so you mean to say that after shrinking, change the recovery mode to Simple?
    i don't take the backups of log files.

    FYI Just be aware that if you change to simple recovery mode you can only recover to the point of your last backup. Backing up the logs allows you to recover to or roll back to a point in time. The question you need to ask yourself is "What is an acceptable amount of data loss?"
    Monday, December 7, 2009 10:50 PM
  • thanks for the reply
    so you mean to say that after shrinking, change the recovery mode to Simple?
    i don't take the backups of log files.

    FYI Just be aware that if you change to simple recovery mode you can only recover to the point of your last backup. Backing up the logs allows you to recover to or roll back to a point in time. The question you need to ask yourself is "What is an acceptable amount of data loss?"

    It doesn't really matter in this case since the OP doesn't take backup of the logs.  If log backup's aren't being taken - what's the point of the FULL recovery model?
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Tuesday, December 8, 2009 2:32 PM
    Moderator
  • thanks for the reply
    so you mean to say that after shrinking, change the recovery mode to Simple?
    i don't take the backups of log files.

    FYI Just be aware that if you change to simple recovery mode you can only recover to the point of your last backup. Backing up the logs allows you to recover to or roll back to a point in time. The question you need to ask yourself is "What is an acceptable amount of data loss?"

    It doesn't really matter in this case since the OP doesn't take backup of the logs.  If log backup's aren't being taken - what's the point of the FULL recovery model?
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

    I have seen many environements where full point in time recovery is desired but the "DBA" doesn't realize that the logs need to be backed up in addition to the database. Just yesterday I found a system where the ldf was 70 gigabytes and the database was 400 megabytes. It was assumed that all that was needed was the database backup. This is more common then you might know.
    Tuesday, December 8, 2009 5:37 PM