By: Simon Liew | Comments (2) | Related: > Database Console Commands DBCCs
Problem
This tip is a continuation of the SQL Server Page Verify CHECKSUM vs NONE Performance tip to show undetected corruption when Page Verify is set to NONE.
A typical question was “Even if I leave the database Page Verify to NONE, wouldn’t DBCC CHECKDB detect when a page is corrupt? That is what DBCC CHECKDB does right?”.
Solution
When database Page Verify option is set to CHECKSUM, SQL Server will calculate a checksum value from the entire page and the value is stored in the page header during a write operation. When the page is read from disk, the checksum value is again calculated and compared to the value stored in the page header.
But when database Page Verify option is set to NONE, then this page CHECKSUM would not need to be calculated and SQL Server will skip the verification of page content correctness. Corruption can occur unexpectedly and most of the time it is outside of SQL Server. Say an integer value in a data page is overwritten with an incorrect value of 0 due to memory or disk corruption. Subsequently when DBCC CHECKDB is ran, it would run successful because SQL Server would not be able to distinguish the corrupted page since 0 is well within the boundary of an integer. So, this type of corruption can go undetected unlike other types of corruption such as a date value is overwritten with character values which will cause DBCC CHECKDB to fail.
SQL Server Corruption Demo
To demo the scenario stated in the solution, we will use the [PageVerifyTest] database from the previous tip where the database Page Verify option was set to NONE.
For this corruption demo, we will pick Page Id 1:224:1 highlighted in screenshot below to corrupt. Here, we will override the content in column BigCol which carries the value “a” with value 0x0.
The output of the query below tells us the Page Id of interest is in data file id 1, page id 224 and slot 1.
SELECT TOP 10 sys.fn_PhysLocFormatter(%%physloc%%) PageId, * FROM [dbo].[mssqltips] GO
First, we will look at the page dump for Page Id 224.
DBCC TRACEON (3604);
GO
DBCC PAGE ('PageVerifyTest', 1, 224, 3);
GO
We will look for Slot 1 and Column 4 from the page dump output. To over-write Slot 1 Column 4 of Page Id 224, we need to calculate its page offset which is 0x467 + 0x1c (1127 + 28 = 1155).
With the offset, we will use the undocumented command DBCC WRITEPAGE to override the value in Column 4 with 0x0 as below.
-- WARNING!! THIS COMMAND CAUSES IRREPAIRABLE DAMAGE. DO NOT RUN ANYWHERE NEAR A PRODUCTION ENVIRONMENT
USE master;
GO
ALTER DATABASE [PageVerifyTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DBCC WRITEPAGE ('PageVerifyTest', 1, 224, 1155, 1, 0x0, 1)
GO
ALTER DATABASE [PageVerifyTest] SET MULTI_USER;
GO
If you query the [dbo].[mssqltips] table again, it will show BigCol now is blank for increment = 2. This scenario can potentially happen in real life, for example when a data page content value is overwritten incorrectly by a filter driver when being written to the disk.
SQL Server Database Integrity Check
If you perform a database integrity check, it will execute successfully. This is the scenario described in the solution description where SQL Server has no mechanism to distinguish if the value in BigCol is a corrupted value since “blank” is also a legitimate value within the definition of the column.
DBCC CHECKDB('PageVerifyTest') WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
Setting SQL Server Database Page Verify to CHECKSUM
If you repeat the same steps, but on a database with Page Verify set to CHECKSUM. When you run a database integrity check, SQL Server will output an error instead.
If the error from the database integrity check can be very long and hard to read. If you try to query the table, a slightly different error message is displayed. The error is displayed because Page Id 224 is read from disk, its checksum value is re-calculated and then compared to the value stored in the page header. When the actual and the expected value doesn’t match, SQL Server knows that the page content has been somehow been corrupted.
Conclusion
SQL Server does not calculate the page checksum on existing pages when you change the database Page Verify setting to CHECKSUM from NONE. A page checksum is only recalculated when a page is read into memory, modified and then written back to disk.
Even on a modern storage subsystem, there are just too many good reasons to not have the database Page Verify option set to CHECKSUM.
Next Steps
- Set the PAGE_VERIFY Database Option to CHECKSUM
- SQL Server Page Verify CHECKSUM vs NONE Performance
- CHECKSUM and Tempdb
About the author
Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
