Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Undetected SQL Server Data Corruption with Database Page Verify option set to NONE


By:   |   Read Comments   |   Related Tips: More > Database Consistency Checks DBCCs

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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
SQL Server Row to corrupt

First, we will look at the page dump for Page Id 224.

DBCC TRACEON (3604);
GO
DBCC PAGE ('PageVerifyTest', 1, 224, 3);
GO
SQL Server Page Dump Output

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.

SELECT query output

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
SQL Server DBCC CHECKDB is successful

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.

SQL Server DBCC CHECKDB error

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.

SELECT query error

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Simon Liew 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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools