3

I'm working on getting full grasp of logging architecture.
Please correct me if I'm wrong here:
I understand that a checkpoint occurs (also lazywriter) to write the dirty pages in the memory to the disk. Disk here means transaction log file no matter what recovery model the database is in. Then the dirty pages become clean, i.e. data on the disk is up-to-date with memory pages.
I also understand logging must happen after commit and before sending the acknowledgement to the client.
What cloudy is, what happens after the data is written to disk on the transaction log file? What moves the data from ldf to mdf (or ndf)? In case a lazywriter removes the pages off the memory, the data will be in the transaction log but not in the data file (yet) or no longer in the memory. What happens when that page is queried?
Also a brief explanation into relationship between checkpoints and simple-full recovery models is appreciated. How does the behaviour change?
Thanks for helping out.

1

Dirty pages are written directly to the data files. There is no need to write them to the log because their modifications have been logged already. A dirty page never hits disk until all log records that record its modification have been written already. After the log has been hardened SQL Server is at leisure to write dirty data pages whenever it wants (or never - if the machine loses power for example). After a page has been written is is simply marked not dirty.

A transaction commit leads to a corresponding log record. The very fact of hardening that log record is what makes the transaction considered committed (except for delayed durability in 2014+).

The recovery model has little to do with this. It controls whether the log is eligible for truncation after a checkpoint has completed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.