Checksum and TornPage Concepts in SQL Server

Checksum and TornPage Concepts are Page Verify Options. Page Verify is a SQL Server Database Property. Both are methods used to discover and report I/O problems caused by disk errors in the database.

TornPage was Microsoft’s default page verify option in SQL Server 2000. Checksum entered our lives as a more advanced page verify option along with SQL Server 2005.

Before reading more about this article, I recommend you read my article “SQL Server Storage Concepts (Page, Extents, GAM, SGAM, PFS, IAM, BCM, DCM)“.

How does Tornpage and Checksum detect I/O problems caused by disk errors?

TornPage: Stores the first two bits of each sector(It is the smallest unit that can be read and written from the disk. It consists of 512 bytes.) in a page in the page header. When the data is read from the disk again, it is compared with those two bits stored in the page header and determined whether it is the same. If not the same, it dedect corruption and warns. It cannot detect if a problem occurs in the rest of Sector.

Cheksum: a cheksum value is generated for the entire page instead of 2 bits for each sector on the page, and the data is compared with that checksum when the data is read from the disk again. When any bit is corrupted in Page, it detects and warns. In other words, it is safer than Tornpage. Performance is slower than tornpage, but this is an acceptable slowness.

You can find the page verify options for the databases on the instance with the following script.

You can change the page verify option to CHECKSUM by using the script below.

Or right-click on the database and click Properties. Go to the Options tab and change the Page Verify Option as follow.

If you have many databases you need to change the page verify option to checksum, you can change them all at once by generating the script that changes all of them. You must run all of the scripts generated as a result of the query as a new query.