patternsqlMinor

What is the need for log cache in MSSQL server?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

thewhatlogneedmssqlcacheforserver

Problem

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?

Solution

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 SQL Server and the WAL 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.

  [1]: https://learn.microsoft.com/en-US/troubleshoot/sql/admin/logging-data-storage-algorithms

Code Snippets

BEGIN TRANSACTION
 INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION

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.

  [1]: https://learn.microsoft.com/en-US/troubleshoot/sql/admin/logging-data-storage-algorithms

Context

StackExchange Database Administrators Q#308104, answer score: 7

Revisions (0)

No revisions yet.