Post reply

Log full due to 'ACTIVE_TRANSACTION'

  • December 10, 2015 at 11:33 am

    #310105

    Hi,

    I am receiving the error

    The transaction log for database 'ITT' is full due to 'ACTIVE_TRANSACTION'.

    when executing a procedure

  • December 10, 2015 at 11:49 am

    #1845312

    Ok, so you're filling the log with a transaction. What's the question?

    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

  • December 10, 2015 at 12:19 pm

    #1845320

    I receive

    The transaction log for database is full due to 'ACTIVE_TRANSACTION'.

    and

    Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

    I notice the G drive where the log is, is full .

  • December 10, 2015 at 12:41 pm

    #1845331

    Ok, and?

    The log is full, you've seen that. It's full because a transaction is open and hence the space in the log can't be reused. The procedure with the transaction is failing with an error, fully expected in this situation. Either do fewer data modifications in the transaction or put the log on a larger drive.

    http://qa.sqlservercentral.com/articles/books/94938/

    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

  • December 10, 2015 at 10:08 pm

    #1845402

    First, I set up the database recovery model as SIMPLE

    Then, delete some old files which gave the log file more space to grow.

  • December 11, 2015 at 2:00 am

    #1845423

    johnwalker10 (12/10/2015)

    First, I set up the database recovery model as SIMPLE

    Which will not do a thing in this situation, as the log is full due to an active transaction.

    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

  • December 11, 2015 at 8:44 am

    #1845526

    I temporarily moved the log file to a different drive.

    As of now , auto growth/maxsize is set to By 10%,Limited to 2097152 MB. Can we change anything here.

    Thanks,

    PSB

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

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