Fixing Outdated Page Verify Settings in SQL Server

Once Upon a Time: TORN_PAGE_DETECTION

Once upon a time, there was a page verify setting known as TORN_PAGE_DETECTION. While it was a better option for checking for I/O write corruption in Microsoft SQL Server than nothing, it only served to do a lackluster job. The process only looked at the first two bytes of each 512 byte sector on a page and wrote it to the page header. When a read I/O occurred subsequently it would compare the header-saved metadata with the value in the bytes at the beginning of each sector on the page, if just those values matched then the assumption was made that there was no corruption on the page. . . because no other corruption in the remaining 510 bytes per sector would ever happen. . . right?

And Then There Was CHECKSUM

That was way back in the land of SQL Server 2000 . . . only a few still live in that realm:  the brave of heart, the weary upgraders from lands supporting healthcare and what I call "undead applications"—applications for which support staff have moved on and no one in the organization is quite sure if the applications being supported by these databases are alive or dead) or perhaps the shoppes of the moms and pops who lack the valour of a strong knight of the data table.  There they be dragons! (Well, perhaps not dragons in the true sense but it's scary place nonetheless.

Once SQL 2005 was released, we were given a much better option (which also became the default—CHECKSUM). In this process, a checksum for all the bytes in a sector is created and written to the header; that value then being checked later during subsequent read I/Os. All is much better than 2/512 after all; corruption can occur anywhere.

Identifying and Remediating Outdated Decisions

The problem is that when software vendors update their databases from previous versions of SQL Server, they don't often think about all the minutiae of the database configuration—some things remain unchanged, or even worse, the default values for older versions are assumed to be the best option for the next (or next-next-next) version of SQL Server as well, which is not always so. It's for this reason I provide you with what is a very simple script to fix out-dated or incorrectly-set page verify values in your SQL Server 2005 and later databases.

The script below identifies any database running under either TORN_PAGE_DETECTION or NOTHING and provides computed T-SQL to update it to CHECKSUM. In my environment, I run this against all of my instances on a regular basis using the functionality that exists natively in SQL Server Management Studio (SSMS) to run a script against all instances in a Registered Servers Group:

SELECT [name] AS [database_name], [page_verify_option_desc] AS [current_page_verify_option_desc], 'ALTER DATABASE [' + [name] + '] SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;' AS [SQL Command] FROM sys.[databases] WHERE [page_verify_option_desc] <> 'CHECKSUM' AND [compatibility_level] > 80 AND [name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution')ORDER BY [name];

Sure enough, every once in a while I get a hit as you can see above (I've taken the liberty to pull the results into Excel so I can manipulate the server and database names to protect the DBA). The last column returned in the script provides computed T-SQL code that you can then run to change the setting for the databases testing positive for SQL 2000 settings:

The Catch—There's Always a Catch

Keep in mind though that this setting change will not take effect for any clean pages already on disk or in the buffer. The writing of a full-sector checksum will not occur until the next time the page gets dirty and incurs a write I/O after being checkpointed. Colleen Morrow has done a very nice job of illustrating this. I suggest reading her post after this one in order to see just how CHECKSUM works.