does it also flushes dirty pages which are related to un-committed
transactions into data file or it only flushes dirty pages related to
Yes of course, it flushes all the dirty pages:
When a checkpoint operation occurs, no matter how it’s triggered (for
instance through a manual CHECKPOINT, from a database or differential
backup, or automatically) the same set of operations occurs:
- All dirty data file pages for the database are written to disk (all pages that have changed in memory since they were read from disk or
since the last checkpoint), regardless of the state of the
transaction that made the change.
- Before a page is written to disk, all log records up to and including the most recent log record describing a change to that
page are written to disk (yes, log records can be cached in memory
too). This guarantees recovery can work and is called write-ahead
logging. Log records are written to the log sequentially, and log
records from multiple transactions will be interspersed in the log.
The log cannot be selectively written to disk, so writing a dirty
page to disk that only has a single log record affecting it may
mean writing many more previous log records to disk as well.
- Log records describing the checkpoint are generated.
- The LSN of the checkpoint is recorded in the database boot page in the dbi_checkptLSN field (see Search Engine Q&A #20: Boot pages, and
boot page corruption).
- If in the SIMPLE recovery model, the VLFs in the log are checked to see whether they can be marked inactive (called clearing or
truncating the log – both of which are terrible misnomers, as nothing
is either physically cleared or truncated).
How do checkpoints work and what gets logged by P. Randal
I am interested to how SQL Server does recovery in case of system
failure and how it depends on CHECKPOINT and are there any other
factors on which DB recovery depends?
During crash recovery, the mechanism is more complicated. The fact
that database pages are not written to disk when a transaction commits
means that there is no guarantee that the set of database pages on
disk accurately reflects the set of changes described in the
transaction log—either for committed or uncommitted transactions.
However, there is one final piece of the puzzle that I haven't
mentioned yet—all database pages have a field in their page header (a
96-byte portion of the 8192-byte page that contains metadata about the
page) that contains the LSN of the last log record that affected the
page. This allows the recovery system to decide what to do about a
particular log record that it must recover:
effect of the log record was not persisted on the page on disk and as
such does not need to be undone. Crash recovery reads through the
transaction log and ensures that all effects of all committed
transactions are persisted in the database, and all effects of all
uncommitted transactions are not persisted in the database—the REDO
and UNDO phases, respectively. Once crash recovery completes, the
database is transactionally consistent and available for use.
Understanding Logging and Recovery in SQL Server by P.Randal
Does recovery process depends on Recovery model as well?
No, it does not.
Database should be consistent in any recovery model after crash recovery.