SQL Server Database Page Verify Options

There are two different possible Page Verify options namely TORN_PAGE_DETECTION and CHECKSUM are available from SQL Server 2005 and above. The TORN_PAGE_DETECTION is only Page Verify option available in SQL Servers 2000 and older.  

On SQL 2005:





On SQL Server 2000:

By default the new databases created in SQL Server 2005 or above will have CHECKSUM as the Page Verify option. But for the databases that upgraded or migrated from older versions to SQL Server 2005 or above will retain the TORN_PAGE_DETECTION as page verify, and we need to modify the option using database properties with GUI or we can run the below command. 

ALTER DATABASE SET PAGE_VERIFY CHECKSUM

Functionality of TORN_PAGE_DETECTION vs CHECKSUM:

Below is the high level description of the each Page verify option and how they work from BOL. 

When TORN_PAGE_DETECTION is specified, a specific 2-bit pattern for each 512-byte sector in the 8-kilobyte (KB) database page is saved and stored in the database page header when the page is written to disk. When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information.

When CHECKSUM is specified, the Database Engine calculates a CHECKSUM over the contents of the whole page and stores the value in the page header when a page is written to disk. When the page is read from disk, the CHECKSUM is recomputed and compared to the CHECKSUM value stored in the page header.

When NONE is specified, database page writes will not generate a CHECKSUM or TORN_PAGE_DETECTION value. SQL Server will not verify a CHECKSUM or torn page during a read even if a CHECKSUM or TORN_PAGE_DETECTION value is present in the page header.

It is very important to have CHECKSUM as the page verify option on all the databases on SQL Server 2005 and above.  I want to show you how efficient is the CHECKSUM over the TORN_PAGE_DETECTION in capturing the data corruptions\modifications made outside the  SQL Server supported methods. 

Lets Start with TORN_PAGE_DETECTION:  For testing purposes I am creating a test database with TORN_PAGE_DETECTION and table, and inserting few records.

USE MASTER
CREATE DATABASE PageVerifyDB
GO
ALTER DATABASE PageVerifyDB SET PAGE_VERIFY TORN_PAGE_DETECTION ;
GO

USE PageVerifyDB
GO
CREATE TABLE PageTab
(
EmployeeID INT IDENTITY(1,1),
FirstName VARCHAR(256),
LastName varchar(256),
Gender varchar(10)
CONSTRAINT pk_pagetable PRIMARY KEY (EmployeeID)
)
GO

INSERT INTO DBO.PageTab (FirstName,LastName,Gender)
VALUES
('Don','Trump', 'M'),
('Hillary','Clinton', 'F'),
('Mike','Jackson', 'M')

This is how the data looks like on the PageTab table.

Now let's corrupt the data on this table using the HexEditor. You can learn how to use this HexEditor from here, Kendra Little has given a nice presentation on HexEditor usage. And please remember to not use this HexEditor on any production\Dev\Test\QA environments. It is strictly not a good practice. 


To know which page has the data that we looking to corrupt. We can run the DBCC IND.

DBCC IND ('PageVerifyDB','Pagetab',1)

I want to corrupt the page 283 on mdf, so to find offset value multiple Page number with (8*2014).   

Offset Value = 283*8*1024 = 2318336

And I wan to change Don Trump Gender to F. And lets use the HexEditor, before that we need to make the database offline as the HexEditor may fails to grab access on the mdf file. 

Opening the mdf file with HexEditor:

Going to the Offset:

Modifying the Values using HexEditor:

Now keep the Database back Online and try select data from it. We don't have any issue selecting the data. 

It looks like below:

So TORN_PAGE_DETECTION will allow us to change the Don Trump gender from M to F.

Now Start with CHECKSUM: I Will do the same exercise again by dropping and recreating our test DB with CHECKSUM as page verify option.

USE MASTER
Drop DATABASE PageVerifyDB
GO

USE MASTER
CREATE DATABASE PageVerifyDB
GO
ALTER DATABASE PageVerifyDB SET PAGE_VERIFY CHECKSOME;
GO

USE PageVerifyDB
GO
CREATE TABLE PageTab
(
EmployeeID INT IDENTITY(1,1),
FirstName VARCHAR(256),
LastName varchar(256),
Gender varchar(10)
CONSTRAINT pk_pagetable PRIMARY KEY (EmployeeID)
)
GO

INSERT INTO DBO.PageTab (FirstName,LastName,Gender)
VALUES
('Don','Trump', 'M'),
('Hillary','Clinton', 'F'),
('Mike','Jackson', 'M')

This is how the data looks like on the PageTab table.


Now corrupting the same data but this time it is residing on different page, now it is 242. Offset value is 1982464. Make the database offline.

Opening the mdf file with HexEditor:

Going to the Offset:

Modifying the Values using HexEditor:

Now keep the Database back Online and try select data from it. We will see red colored text in result pane, which reads like below.  

The CHECKSOME wont allow us to change the Don Trump's gender from M to F. 

This concludes how both the page verify option works and which is efficient. And using the NONE as page verify option is worst than Torn_page_detection.

There are lot of myths around the Page Verify options especially the CHECKSUMS, you can read from Paul Randol's post here.

Thanks, Hope it is helpful.

Here is the Kendra Little's article here regarding the Page Verify options, I just take her article as a reference to my post. I just reproduced her article here.