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

By:   |   Comments (2)   |   Related: > Database Console Commands DBCCs


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?”.


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

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

DBCC PAGE ('PageVerifyTest', 1, 224, 3);
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.


USE master;
DBCC WRITEPAGE ('PageVerifyTest', 1, 224, 1155, 1, 0x0, 1)

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.

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.


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


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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, July 11, 2023 - 8:27:46 PM - Simon Liew Back To Top (91376)
Hi Lex, if you wanted to simulate a database corruption with error message, you can refer to this tip https://www.mssqltips.com/sqlservertip/5706/using-undocumented-dbcc-writepage-to-instigate-sql-server-database-corruption/
After performing steps outlined in the tip, execute "DBCC CHECKDB('CorruptionTest') WITH NO_INFOMSGS, ALL_ERRORMSGS;" and you should see the failure.
This tip is to show how corruption are undetected when page verify is set to NONE.

Tuesday, July 11, 2023 - 2:38:28 PM - Lex Back To Top (91375)
I did as in the article, but the database is not corrupt, why? I perform a database integrity check, it will execute successfully. Tried in both none and checksum modes.

get free sql tips
agree to terms