SQL Server Buffer Pool – When Do Transaction Log Changes Get Applied to Database Data Pages?

backupbuffer-poolcheckpointsql servertransaction-log

When a change is to be made, the data page is fetched from the database data file into the buffer and changes are made to the buffer page and are also written to the transaction log. Write ahead logging. In Simple recovery mode the changes from the buffer are applied to the database data pages at checkpoint. The checkpoint flushes the records from the transaction log. Hence transaction log backups cannot be taken in this mode. Similarly for full/bulk recovery models when a full backup hasn't yet been taken.

Question: How and when does the changes to the data page get written to the database data file in the full/bulk recovery model once the 1st full backup has been taken?

Best Answer

According to the SQL Server Transaction Log Architecture and Management Guide, data pages are modified in cache first (logical write). This occurs the same way regardless of the database recovery model, with only the level of logging detail being different. A transaction log record containing the modification is created and written to the log buffer cache. These log buffers are always physically written to disk before the modified pages (write-ahead logging).

Data pages may be written to disk by lazy writing, eager writing, and the checkpoint process. The log buffer is flushed to disk before the corresponding data pages are written to the data files by these processes.

Transaction log buffers are physically written to disk when 1) they become full, 2) during COMMIT when the log buffer contains data for a durable transaction, 3) a checkpoint, or 4) a modified page is written to disk by one of the aforementioned processes.