Post reply

Transaction Log Details

  • February 25, 2016 at 4:53 am

    #311720

    Hi,

    If possible please could someone recommend a link to an accurate description of role of the Transaction Log when a transaction is processed. Unfortunately, the books /articles I've read all seem to contradict each other.

    For example, when a data change is made:

    "Data Modification information is written to a log page IN MEMORY"

    Another Source:

    "Data Modification information is written directly to the transaction log ON DISK"

    So, is the T-Log entry made on Disk or are the T-Log pages stored in memory, like the data pages?

    Thanks in advance for any suggestions.

  • February 25, 2016 at 4:58 am

    #1860999

    Log records are written first in memory and then to disk.

    Every RDBMS uses WAL (Write-Ahead-Logging) to ensure consistency, so every operation that generates log records is said to be completed when the log has been successfully written to disk. The fact that is has to go from memory to disk is irrelevant.

    -- Gianluca Sartori

  • February 25, 2016 at 5:04 am

    #1861000

    Thanks for the reply.

    Would you agree with the following event taking place when a modification is made:

    1. A BEGIN TRAN record is written to the T-Log in buffer memory

    2. Data Modification information is written to the T-Log pages in memory

    3. Data Modifications are written to the data pages in buffer memory

    4. A COMMIT TRAN record is written to the T-Log in buffer memory

    5. T-Log records are written to the T-Log on Disk

    6. A COMMIT acknowledgement is sent to the client

    Would you also agree that the Log Page is flushed to disk in the following situations:

    - When we commit/rollback a transaction

    - When a log block hits its maximum size of 60 KB

    - When a data page is being written to disk – all the log records up to including the last one affecting this page must be written to disk regardless of which transactions they are part of

    Thanks for your help.

  • February 25, 2016 at 5:53 am

    #1861010

    Close enough.

    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

  • February 25, 2016 at 6:04 am

    #1861014

    Thanks

  • February 25, 2016 at 6:05 am

    #1861015

    Kalen Delaney's book, SQL Server Internals, covers all this extremely well.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • February 25, 2016 at 6:06 am

    #1861016

    Ok thanks.

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

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