Checkpoint是不是只写提交的Transcation页

很多人都以为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;

GO

USECheckpointTest;

GO

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

go

CREATE CLUSTERED INDEX t1c1 on t1(c1);

GO

SET NOCOUNT ON;

GO

CHECKPOINT;

GO

DBCC TRACEON(3505, -1);

GO

BEGIN TRAN;

INSERT INTO t1 DEFAULT VALUES;

GO

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