DOES LAZY WRITER OVERRIDES WRITE AHEAD LOGGING MECHANISM?

Question

I READ THAT LAZY WRITER DOES NOT MAKE ANY ENTRY IN LOG, BUT FLUSHES DIRTY PAGES TO DISK, SO IF SERVER SHUTDOWNS IN BETWEEN A LAZYWRITE AND NEXT CHECKPOINT, THE LOG CACHE DATA LOST, BUT DIRTY DATA ALREADY WRITTEN TO DISK BUT CORRESPONDING LOG IS LOST, DURING RECOVERY HOW THE DATA IS VALID? IT MAY BE OUT OF SYNC WITH LOG

Answer 1

Hi @Subbarao DBA ,

Regardless of the recovery mode, SQL Server will scan the log to the last checkpoint during crash recovery, rolls forward all data changes at that point, and then rolls back uncommitted transactions.

According to the official doc:SQL Server Transaction Log Architecture and Management Guide

SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written

Write ahead logging will guarantee that no data modifications are written to disk before the associated log record is written to disk. All data modifications are first written to the log, and then dirty pages written afterward

https://dba.stackexchange.com/questions/287463/how-does-recovery-work-when-server-is-shutdown-before-a-checkpoint
https://www.sqlshack.com/sql-server-checkpoint-lazy-writer-eager-writer-and-dirty-pages-in-sql-server/

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If that happens, the pages will be undone to their earlier state. Note that these pages will always be for uncommitted transactions.