SQL Server myth: Checkpoint only writes committed transactions to disk

Source: Internet
Author: User
Tags log create database

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.