Checkpoint是不是只写提交的Transcation页-优快云博客

本文通过一个具体的测试案例，澄清了一个常见的误解：Checkpoint不仅会写入已提交事务产生的脏页，还会写入所有未提交事务产生的脏页。这表明Checkpoint的主要目的是确保数据库的所有更改都被持久化，而不仅仅局限于已提交的事务。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

很多人都以为Checkpoint只是将已经完成Transcation的脏页写入磁盘，其实不是这样的：

One commonmisconception about checkpoints is that they only write out pages with changesfrom committed transactions. This is not true—a checkpoint always writes outall dirty pages, regardless of whether the transaction that changed a page hascommitted or not.（来自：http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx）

这里开启Traceflag 3503做个测试：

CREATEDATABASECheckpointTest;

USECheckpointTest;

CREATE TABLE t1(c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'a');

CREATE CLUSTERED INDEX t1c1 on t1(c1);

SET NOCOUNT ON;

CHECKPOINT;

DBCC TRACEON(3505, -1);

BEGIN TRAN;

INSERT INTO t1 DEFAULT VALUES;

CHECKPOINT;

Errorlog中看到的结果：

2012-07-2610:14:29.180 spid57 Ckpt dbid 7 started (8)
2012-07-26 10:14:29.180 spid57 About to log Checkpoint begin.
2012-07-26 10:14:29.190 spid57 Ckpt dbid 7 phase 1 ended (8)
2012-07-26 10:14:29.190 spid57 FlushCache: cleaned up 6 bufs with 6 writes in 3ms (avoided 0 new dirty bufs)
2012-07-26 10:14:29.190 spid57 average throughput: 15.63 MB/sec, I/Osaturation: 5, context switches 6
2012-07-26 10:14:29.190 spid57 last target outstanding: 2, avgWriteLatency 0
2012-07-26 10:14:29.190 spid57 About to log Checkpoint end.
2012-07-26 10:14:29.190 spid57 Ckpt dbid 7 complete

可以看到虽然TRAN没有Commit但是脏页已经被写到磁盘了。

Trace flag3505请参考：http://support.microsoft.com/kb/815436