Post reply

LOG_REUSE_WAIT_DESC is ACTIVE_TRANSACTION but dbcc opentran shows nothing

  • September 17, 2013 at 8:41 am

    #395499

    I was trying to shrink the log on a database in simple recovery.

    It shrunk by about 10Gb, and then wouldn't shrink any further. in the sys.databases Log_Reuse_Wait_Desc column the reason was "Active_Transaction", however dbcc opentran showed no open transactions.

    On running a CHECKPOINT and trying the shrink again, the log could be shrunk and the Reuse_wait_desc changed to NOTHING.

    I cant figure out what happened here, and why a checkpoint would clear any active transactions. Can anyone shed light on this?

  • September 17, 2013 at 10:58 am

    #1650863

    The checkpoint didn't clear the active transactions. It can't do that.

    log_reuse is not updated the instant that something changes. Sometimes you need to do something like a checkpoint to see the current value, rather than the value from a couple seconds before.

    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 2 posts - 1 through 1 (of 1 total)

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