What is the need for log cache in MSSQL server?



    • If every transaction record is immediately hardened in transaction log file, then what is the need for a log cache?
    • If a transaction record remains in log cache when there is a system failure, then that record will be lost and database will be inconsistent right?
    • Is there any other reason or purpose for having a log cache?


  • If every transaction record is immediately hardened in transaction log file, then what is the need for a log cache?

    No, it is not "immediately" hardened but there are series of steps which transaction go through before logs record is actually flushed to disk. Just read https://docs.microsoft.com/en-US/troubleshoot/sql/admin/logging-data-storage-algorithms Read the part "SQL Server and the WAL".

    For every database you get a log cache a contiguous memory area which holds log information and are flushed to disk when transaction commits. A log cache can have information about multiple transactions and every commit triggers a flush to disk to harden the log record. AFAIK the size is 60K ( might have changed in new versions not quite sure about the size)

    If a transaction record remains in log cache when there is a system failure, then that record will be lost and database will be inconsistent right?

    If the log cache record is not flushed to disk/ not hardened, yes it will be lost

    Is there any other reason or purpose for having a log cache?

    Read the blog I have shared above. I quote with example from above blog

    BEGIN TRANSACTION
     INSERT INTO tblTest VALUES (1)
    COMMIT TRANSACTION
    

    Next, break down the activity into simplistic logging steps, as described in the following table.

    Statement                          Actions performed
    BEGIN TRANSACTION    **Written to the log cache area.** However, it is 
                         not necessary to flush to stable storage 
                         because the SQL Server has not made any 
                         physical changes.
    

    INSERT INTO tblTest
    1. Data page 150 is retrieved into SQL Server data
    cache, if not already available.
    2. The page is latched, pinned, and marked dirty,
    and appropriate locks are obtained.
    3. An Insert Log record is built and added to the
    log cache.
    4. A new row is added to the data page.
    5. The latch is released.
    6. The log records associated with the transaction
    or page does not have to be flushed at this point
    because all changes remain in volatile storage.

    COMMIT TRANSACTION
    1. A Commit Log record is formed and the log
    records associated with the transaction must be
    written to stable storage. The transaction is
    not considered committed until the log records
    are correctly assigned to stable storage.
    2. Data page 150 remains in SQL Server data
    cache and is not immediately flushed to stable
    storage. When the log records are correctly
    secured, recovery can redo the operation, if it
    is necessary.
    3. Transactional locks are released.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2