SQL Server – Does CHECKPOINT or COMMIT Write to Disk?

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.