SQL Server myth: Checkpoint only writes committed transactions to disk

Myth #15: Checkpoint only writes committed transactions to disk

Error

The misconception is that too many people have a long history of lack of comprehensive knowledge of the log and recovery system. Checkpoint will write back to disk all pages that have changed in memory since the last checkpoint (that is, dirty pages), or dirty pages that read into memory in the previous checkpoint. The pages affected by the transaction are written back to the disk when checkpoint, regardless of whether it has been committed. This is the exception for tempdb, because tempdb does not include the steps to write dirty pages back to disk in the checkpoint event cycle.

If you want to learn more, please read the following article:

Technet Magazine article: Understanding Logging and Recovery in SQL Server

Bowen: How do checkpoints work and what gets logged

Bowen: What does checkpoint do for tempdb?

You can use the following two tracking tags to see how checkpoint works.

3502: Write related information to the error log when checkpoint begins and ends

3504: Write the checkpoint to the disk when the information is written back to the error log

In order to use this trace tag, you must open it for all threads, otherwise you will not see anything in the error log. Use DBCC TRACEON (3502, 3504,-1) to open the two trace flags for all threads.

The following code can prove that checkpoint will write the uncommitted dirty pages back to disk, following the steps below.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

CREATE DATABASE checkpointtest; Go use checkpointtest; Go CREATE TABLE T1 (C1 INT IDENTITY, C2 CHAR (8000) DEFAULT ' a '); CREATE CLUSTERED INDEX t1c1 on T1 (C1); Go

SET NOCOUNT on; Go

CHECKPOINT; Go

DBCC traceon (3502, 3504,-1); Go

The following transaction produces a dirty page of 10MB, followed by a checkpoint

BEGIN TRAN; Go inserts into T1 DEFAULT VALUES; Go 1280

CHECKPOINT; Go

Log as you can see:

We can see clearly that dirty pages will still be written to disk when the transaction is not committed.