SQL Server misunderstanding about 30 days 15th days CheckPoint will only write committed transactions to the disk

Misunderstanding #15: CheckPoint only writes committed transactions to the disk

Error

This misunderstanding is due to the lack of comprehensive understanding of logs and recovery systems for a long time. CheckPoint writes all pages that have changed in memory since the last CheckPoint back to the disk (Note: Dirty pages), or writes the dirty pages read from the memory in the previous CheckPoint to the disk. Whether or not the transaction has been committed, the affected pages will be written back to the disk at Checkpoint. However, the exception is that the event cycle of the TempDB Checkpoint does not include the steps for writing dirty pages back to the disk.

For more information, see the following article:

• Technet magazine article: Understanding Logging and Recovery in SQL Server

• Blog: How do checkpoints work and what gets logged

• Blog: What does checkpoint do for tempdb?

You can use the following two trace tags to view how the CheckPoint works.

• 3502: When the CheckPoint starts and ends, write the relevant information to the error log.

• 3504: Write the information on the page written back to the disk during CheckPoint to the error log.

To use this trace flag, you must enable it for all threads. Otherwise, you will not see anything in the error log. Use dbcc traceon (3502,350 4,-1) to enable these two trace tags for all threads.

The following code proves that the Checkpoint will write uncommitted dirty pages back to the disk and follow the steps below.
Copy codeThe Code is as follows:
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,350 4,-1); GO



The following transaction will generate a dirty page of 10 MB, followed by the CheckPoint
[Code]
Begin tran; go insert into t1 DEFAULT valuees; GO 1280
CHECKPOINT; GO
[Html]


As you can see:

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