What is Write-Ahead Transaction Log in SQLSERVER?

Write-Ahead Transaction Log (WAL) makes sure that log record for modified data pages are written to the disk before actual data pages.

Let’s see data flow process in SQL Server.

1)  When data modification occurs, initially it is written to the buffer cache.

2) While checkpoint occurs, first associated log record for data modification is written to the disk. Log records are written to the disk when the transaction is committed.

3)   And then actual modified data pages are written to the disk from the buffer cache. This operation is called flushing the page. A page modified in the buffer cache, but not yet written the disk is called dirty page.

Use of WAL in SQLServer

It is helpful for data recovery process. We can roll back the transaction without any issue due to WAL mechanism.

If modified data page (dirty page) is flushed before the associated log record is written, the dirty page creates a modification on the disk that cannot be rolled back if the server fails before the associated log record is written to disk. SQL Server has mechanism which prevents a dirty page from being flushed before the associated log record is written.