One thing I still run into quite often are SQL Server 2005 and newer databases that have their Page Verify database option set to TORN_PAGE or NONE. The most common reason for this is that an older database that was originally created in SQL Server 2000 or older was upgraded to SQL Server 2005 or newer, and the Page Verify Option was left at the older and less effective TORN_PAGE value. I also run into instances where people have changed the Page Verify database option to NONE, thinking that this would have a dramatic beneficial effect on performance (which is not true).

From BOL: “When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This helps provide a high level of data-file integrity.”

Paul Randal talked about some of the myths around page verify here. Kendra Little wrote a good post that demonstrates how CHECKSUM reacts to corruption here.

In my opinion, all of your databases should be using CHECKSUM for their Page Verify database option. You can easily query sys.databases to find out the status of the Page Verify database option for all of your databases with this query:

-- Get value of page verify option for all databases
SELECT name, page_verify_option_desc
FROM sys.databases;

 

If you have just a few databases, it is pretty easy to run code like this for each one, to change this option:

-- T-SQL to change Page Verify option to CHECKSUM for a single database
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;
GO

 

If you have a large number of databases that need to be changed, you can write a query to generate the ALTER DATABASE statements for you, like this:

-- Generate ALTER DATABASE statements to change Page Verify option to CHECKSUM
SELECT N'ALTER DATABASE [' + db.name + N'] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;'
FROM sys.databases AS db 
WHERE db.page_verify_option_desc <> N'CHECKSUM';

 

After you run this query, you can copy the rows from your results grid in SSMS to a new query window, and then run your ALTER DATABASE statements when you are ready, without having to write all of the T-SQL code yourself.

Keep in mind that just changing the setting to CHECKSUM does not instantly add CHECKSUMs to your existing data pages in the database. In order for this to happen, you have to read each page into memory, make some sort of change and then write it back out to the storage subsystem. This can happen from normal INSERT/UPDATE/DELETE activity over time, or from rebuilding your indexes.

Here are some useful links on this subject:

Checksum in SQL2005

How to tell if the IO subsystem is causing corruptions?

Inside The Storage Engine: Does turning on page checksums discard any torn-page protection?

Checksum and tempdb

Performance impact of enabling page checksum and default trace