CHECKPOINT or COMMIT writes to disk?

Question

Let's say, for SQLServer2008R2 and higher, with full recovery mode databases.

I always thought :

1. When a transaction is commited (COMMIT), the transaction is written to the transaction log in RAM.

2. When a CHECKPOINT occurs (after some time and/or some transactions and other criterias), the transactions between the last CHECKPOINT and the current are written to disk.

3. When a BACKUP LOG happens, the datas are written to the MDF file.

Am I correct? Some of my collegues says I'm wrong, and it's hard to find the correct answer, even with the BOL.

Thanks!

Answer 1

Unfortunately there are a number of errors in the answers so far with regard to how COMMIT works, so I'll add another one. See How It Works: Bob Dorr's SQL Server I/O Presentation for details and SQL Server 2000 I/O Basics. Here is how it works:

• All fully logged data writes (changes) occur in the exactly following sequence (see Understanding How SQL Server executes a Query: Writing Data):

  • The data page is latched exclusively
  • A log record describing the change is added to to log, in memory. New log record generates a new LSN, see What is an LSN: Log Sequence Number.
  • The data page is modified (both data record and last_update_lsn on the page). This is now modified ('dirty') page.
  • The data page latch is released
  • nothing gets written to disk directly as the result of the update

• A COMMIT does the following

  • adds a new log record describing the COMMIT to the log, in memory
  • all log records not flushed to disk, up to and including the one generated above, are flushed (written to disk)
  • thread blocks waits until the OS reports the above write as durable (IO completes)
  • COMMIT statement (or DML statement with implicit commit) completes

• A CHECKPOINT does the following (simplified), see How do checkpoints work and what gets logged:

  • All dirty pages in memory are written to disk
    • For each dirty page, before starting to write to disk, the log up to and including the LSN that is the last_update_lsn on that page is flushed (written to disk). Note that flushin any LSN implies all previous LSNs are also flushed, so for the most dirty pages this is a no-op since it's own last_update is likely already flushed.
  • log record describing the checkpoint is written to the log and flushed
  • the database boot page is update with the LSN of the record generated above

Writes work differently for minimally logged operations, see Operations That Can Be Minimally Logged. Roughly the minimally logged operations act as following (simplified):

• Before inserting rows in a page as part of a minimally logged operation, a log record describing the fact that the page participates in minimally logged operations is being generated and appended to the log (in memory)
• The minimally logged page is being updated, as many inserts are being written on it is it fit. Nothing is logged, nothign is written to disk.
• When a minimally logged operation commits, before it commit it is required that all pages that participated in minimally logged operations in that transaction are being written to disk. Onyl after this writes completes, the COMMIT log record can be appended to the log (in memory) and and the log, up to and including this newly added commit log record, is flushed (written) to disk.

Answer 2

When a transaction is commited (COMMIT), the transaction is written to the transaction log in RAM.

A transaction is written to the transaction log before even changing the page or data as requested by query. This is called write ahead logging (WAL). If SQL Server crashes while a page is being updated in memory WAL ensures that the DB engine can read the transaction log and rollback the transaction. This is an ACID property of a RDBMS.

When a CHECKPOINT occurs (after some time and/or some transactions and other criterias), the transactions between the last CHECKPOINT and the current are written to disk.

A checkpoint flushes dirty pages from buffer to disk. It behaves a little differently for tempdb. A dirty page is one which has changed since being read from disk. This checkpoint process creates a mark in the transaction log up to the point at which transactions have been committed. After a failure, recovery knows that all transactions up to that mark had committed. You can issue checkpoint manually with a TSQL command.

When a BACKUP LOG happens, the datas are written to the MDF file.

No, when backup log happens SQL Server copies transaction log information from the database log file to the disk where you are writing the backup. A backup operation reads data from disk and writes data on disk.

I would like you to read below links

Understanding Logging and recovery in SQL Server Already pointed by Mark

SQL Server 2008 Internals and troubleshooting Book

Transaction Log Architecture and Management

Answer 3

Below will clarify things for you :

When a transaction is commited (COMMIT), the transaction is written to the transaction log in RAM.

• COMMIT (or BEGIN) has nothing to do with the recovery model. It is at the transaction level. A transaction has to either fully complete or fail (remember ACID properties). So essentially, a COMMIT will mark the end of a successful transaction (either implicit or explicit). A COMMIT statement will guarantee that all of the transaction's modifications are made a permanent part of the database.
• In order to make any changes, SQL Server will use WAL (Write-ahead Logging) wherein it will first describe in the log any change it is about to make, before modifying the data.

When a CHECKPOINT occurs (after some time and/or some transactions and other criterias), the transactions between the last CHECKPOINT and the current are written to disk.

• A checkpoint will always write all the pages that have changed (called dirty pages) since the last checkpoint. It doesn't matter whether the transaction that changed a page has committed or not – the page is written to disk regardless. The only exception is for tempdb, where data pages are not written to disk as part of a checkpoint.

When a BACKUP LOG happens, the datas are written to the MDF file.

• A log backup is ALL the log generated since the last log backup.

Refer to :

• How do checkpoints work and what gets logged
• How to read and interpret the SQL Server log