0

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?

Improve this question
1
  • I see you have asked multiple questions recently about data and transaction log writing and database recovery. This is all good info to know but I suggest you first read the documentation as the all answers lie within.
    – Dan Guzman
    Commented Oct 15, 2018 at 10:02

2 Answers 2

Reset to default
3

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.

Improve this answer
5
  • +1 for Eager Writer. I failed to mention that. This would be my chosen answer.
    – DimUser
    Commented Oct 15, 2018 at 10:14
  • How about delayed durability feature in 2014 - does that need to be part of the answer?
    – variable
    Commented Oct 15, 2018 at 10:46
  • @variable, I already have that in my answer ("when the log buffer contains data for a durable transaction").
    – Dan Guzman
    Commented Oct 15, 2018 at 11:06
  • How does the system decide between lazy writing, eager writing, and the checkpoint process?
    – variable
    Commented Oct 15, 2018 at 12:57
  • @DimUser's answer specifies the checkpoint frequency and when lazy writes may occur. See this answer for more detail on eager writing. Checkpoints are used to reduce recovery time, eager writes to improve bulk load performance, and lazy writer to write dirty pages between checkpoints when free pages are needed.
    – Dan Guzman
    Commented Oct 15, 2018 at 19:53
1

There are two key processes that write "dirty" pages to disk: Lazy Writer, and Checkpoint.

Checkpoint

Checkpoint writes dirty pages to disk at specified intervals, configurable via the setting 'recovery interval (min)'. The purpose of this is to reduce crash recovery time in the event of a system restart. Checkpoints can be issued manually, but generally this is left to SQL Server to maintain.

Lazy Writer

The lazy writer process writes dirty pages to disk at times when SQL Server detects memory pressure. I think this is done at SQL Server's discretion, and is not configurable.

Here is a good link that describes the two processes in a little more detail.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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