Post reply

The transaction log for database is full.

  • October 17, 2013 at 5:08 am

    #299318

    Hi Guys,

    The following error i am getting when i insert or update and delete operation on database. please suggest me how can i solved this ASAP.

    The transaction log for database is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

    --chalam

  • October 17, 2013 at 5:15 am

    #1659234

    What is the recovery model ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • October 17, 2013 at 5:20 am

    #1659237

    Full.

    --chalam

  • October 17, 2013 at 5:26 am

    #1659241

    and do you have tlog back scheduled ? because if transaction log has not been backed up recently, backup might be what is preventing log truncation

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • October 17, 2013 at 5:26 am

    #1659242

    When running a database in FULL recovery mode it is required to take regular LOG backups. Without running LOG backups the space inside the LOG-file can not be re-used and results in a full file (when max growth is set) or results in a full disk.

    FULL recovery is required if you need to be able to restore the database to a specific point-in-time. If this requirement is not necessary for your database, consider changing the recovery model to SIMPLE.

    So, schedule regular (every 15 - 180 minutes depending on the activity of the database) LOG backup or change the recovery model of the database to SIMPLE.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • October 17, 2013 at 9:07 am

    #1659370

    Take a read through these: http://www.sqlservercentral.com/articles/Transaction+Logs/72488/

    http://www.sqlservercentral.com/articles/Administration/64582/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 17, 2013 at 9:39 am

    #1659389

    Read the articles Gail mentioned.

    As noted, you need log backups. The transaction log will keep all data around on transactions until it is backed up (some exceptions for mirroring or replication), so you need log backups.

    If you aren't sure how often to run log backups, ask someone how much data you can lose in a disaster. They will say none, but that costs $$$. many orgs can survive an hour or two of data loss, but whatever the number, that's the frequency of your log backups.

    We have a great script here [/url]to help with this that is better than maintenance plans, but you can use a maintenance plan if you like. Make sure you are only doing the log backups for those databases with full recovery model.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply