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.

  • Thank you. So is there multiple checkpoints? One from buffer to log and one from log to data files? – Stackoverflowuser Nov 24 '14 at 9:47
  • 1
    @usr "Dirty pages are flushed directly to the data files", its not actually correct. Word "Flush" sounds like, these dirty pages are copied to mdf or ndf and are cleaned from memory. No, practically after every checkpoint updated pages are copied to mdf or ndf, but these pages still remains in buffer cache for reuse. – aasim.abdullah Nov 24 '14 at 9:48
  • Wording clarified. – usr Nov 24 '14 at 10:43
  • Hi Assim. Are there multiple checkpoints one to write to ldf from memory and one to write to mdf? – Stackoverflowuser Nov 24 '14 at 11:21
  • 1
    Nop. Read this detail answer by Remus dba.stackexchange.com/questions/75821/… – aasim.abdullah Nov 24 '14 at 11:31

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.