A new feature is available in SQL Server 2005 and higher versions to enabled Page Checksum for a database page to help detect errors in the IO path.
The SQL Server storage engine has already blogged about this:
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/29/Enabling-CHECKSUM-in-SQL2005.aspx
The reason I’m posting an additional article is the misconceptions related enabling Checksum for a SQL Server database. You can find out what is the PAGE VERIFY option for your database using the system catalog sys.databases with the help of the query below:
select database_id, page_verify_option, page_verify_option_desc
from sys.databases
The PAGE VERIFY option has 3 possible values:
0 = NONE
1 = TORN_PAGE_DETECTION
2 = CHECKSUM
Let’s take an example here:
You created a database without CHECKSUM enabled. So, all pages that were written to disk now do not have the Checksum property was enabled on it. After you enable Checksum on the database all the objects that were created prior to enabling Checksum on the database will not have the Checksum written into the Page header. When these pages are brought from the disk into the SQL Server Buffer Pool (RAM) and the dirty bit of the page is set (when any DML operation modifies the page), only then would the Checksum get calculated and written into the page header before it gets written back to the physical media (disk). This happens during the Checkpoint operation. So, if you brought a page into the SQL Server Buffer Pool and didn’t dirty the page (i.e. only for a SELECT operation), then the checksum will not be written onto the page header.
One of the recommendations would be to dirty all pages using operations like DBCC DBREINDEX so that all pages that didn’t have Checksums written into the header now have Checksums written onto it after enabling Checksum on a database.
Why is this by-design?
The reason for this is we cannot estimate the size of the database and the number of database pages that would need checksums calculated and written onto the disk. This would involve a huge performance degradation which is avoided by the current design.
From SQL Server Books Online:
PAGE_VERIFY
When CHECKSUM is specified, the 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 stored in the page header.
When TORN_PAGE_DETECTION is specified, a specific bit for each 512-byte sector in the 8-kilobyte (KB) database page is saved and stored in the database page header when the page is written to disk. When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information.
When NONE is specified, database page writes will not generate a CHECKSUM or TORN_PAGE_DETECTION value. SQL Server will not verify a checksum or torn page during a read even if a CHECKSUM or TORN_PAGE_DETECTION value is present in the page header.
Another interesting post by Bob Dorr (Principal Escalation Engineer – SQL Server):
TroubleshootingSQL 