Post reply

The transaction log

  • December 30, 2011 at 4:14 am

    #248287

    Hi,

    I was wondering if someone could just clarify something for me.

    When an insert/update or delete is written, as I understand this change is;

    1. The change is written to the transaction log.

    2. At the same time the change is made to memory.

    So at this point we can ensure that we can apply the change if a restart occurs.

    Then at a given point the CHECKPOINT or Lazy Writer will come along and write the changes from memory to the data file, the lazy writer will then free up the dirty pages from the memory as well.

    Is that right? I know it's at a high level, I'm just trying to make it clear in my head again.

    Thanks,

    Nic

  • December 30, 2011 at 4:18 am

    #1426855

    Pretty much.

    The order is:

    Change written to memory and log entry written into log buffer.

    Log buffer flushed to disk when the transaction commits (or sometimes earlier)

    Later the data change is written from memory to the data file by the lazy writer or checkpoint.

    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 30, 2011 at 4:22 am

    #1426858

    I knew I forgot something, the log buffer.

    Thanks for clearing that up for me Gail, we were talking about CHECKPOINT and I got myself confused (again).

    Enjoy the new year.

    Nic

  • December 30, 2011 at 6:19 am

    #1426917

    Also maybe worth noting that both the log write (when the log buffer is flushed to disk) and the data write (via checkpoint/lazywriter) can occur before the transaction commits, however it still must be log first, then data.

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

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