Post reply

Transaction log full with simple recovery mode and log_reuse_wait_desc= 'active_transaction'

  • February 8, 2013 at 8:01 am

    #394146

    Hi,

    we have a little db with recovery mode=simple.

    So, with this mode, transaction log suppose to be truncate after each checkpoint.

    I have a msg in the error log saying this: "The transaction log for database 'X' is full. Error 9002, Severity 17 state 4.

    So, I run "select name, log_reuse_wait_desc from sys.databases" and the result show me than my "X database" have log_reuse_wait_desc = 'active_transaction'.

    After I run "dbbc opentran" to see the active transaction, the result is "no active transaction".

    So, I don't understand. log_reuse_wait_desc= 'active_transaction' and dbcc opentran= "no active transaction" ?

    Do you have an idea ?

    thanks and regards,

  • February 8, 2013 at 1:16 pm

    #1586126

    Check for an active transaction in the other databases - especially tempdb.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • February 9, 2013 at 2:12 am

    #1586210

    Run checkpoint and check sys.databases again. The log_reuse doesn't update instantly. Probably the transaction ran out of log space and was rolled back, hence why it's no longer active.

    Maybe take a read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

    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

Viewing 3 posts - 1 through 2 (of 2 total)

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