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. . .
And Then There Was CHECKSUM
That was way back in the land of SQL Server 2000 . . .
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.