Quick Scan Report – Obsolete Torn Page Detection

What this check looks for

Databases where page_verify_option = 1 in sys.databases, which is TORN_PAGE_DETECTION. The three possible values are 0 for NONE, 1 for TORN_PAGE_DETECTION and 2 for CHECKSUM.

Why it matters

Torn page detection was superseded in SQL Server 2005 and it detects far less than CHECKSUM does.

The difference is in what each one actually verifies:

  • Torn page detection stores 2 bits from each 512 byte sector of the page and checks them on read. It was designed for one specific failure: a power loss partway through writing an 8 KB page, leaving some sectors from the new page and some from the old. It does that job, and nothing else. Any corruption that leaves those particular bits intact goes unnoticed.
  • CHECKSUM computes a checksum over the entire page when it is written and verifies it on every read. It catches torn writes, and also bit flips from failing memory, damage introduced by a storage controller or a driver, and anything else that changes a byte of the page between write and read.

The failures CHECKSUM catches that torn page detection does not are the more common ones now. Enterprise storage has largely solved the interrupted-write problem with battery backed cache and journalling. What has not gone away is silent corruption in the path between SQL Server and the platter, and that is exactly what a whole page checksum finds and a 2-bits-per-sector marker does not.

There is a second consequence. msdb.dbo.suspect_pages is only populated when a page verification fails. With the weaker verification, fewer failures are detected, so the record of corruption that a restore would depend on is thinner than it looks.

CHECKSUM is the default from SQL Server 2005 onwards. A database still set to torn page detection was almost certainly created on SQL Server 2000 and has carried the setting through every upgrade since, because upgrading a database does not change it.

How to confirm it yourself

SELECT [name],
       [page_verify_option],
       [page_verify_option_desc],
       [compatibility_level],
       [create_date]
  FROM sys.databases WITH (NOLOCK)
 WHERE [page_verify_option] <> 2
 ORDER BY [page_verify_option], [name];

Anything other than 2 is worth changing. NONE is worse than torn page detection and has its own check.

What has already been detected:

SELECT db.[name] AS [database_name],
       sp.[file_id], sp.[page_id], sp.[event_type],
       sp.[error_count], sp.[last_update_date]
  FROM msdb.dbo.suspect_pages AS sp WITH (NOLOCK)
  LEFT JOIN sys.databases AS db WITH (NOLOCK)
         ON db.[database_id] = sp.[database_id]
 ORDER BY sp.[last_update_date] DESC;

How to fix it

One statement per database. It is online, instant, and takes no locks:

ALTER DATABASE [YourDatabase] SET PAGE_VERIFY CHECKSUM;

Understand what it does and does not do. From that moment, every page written gets a checksum, and every page read that has one is verified. Pages already on disk are not retroactively checksummed. A page that has not been written since the change still carries no checksum and is still unverified, and on a large table that could be most of the pages, indefinitely.

So the change on its own gives you protection for new writes only. To get the whole database covered:

  • Rebuild the indexes, which rewrites every page and so checksums it.
  • Or accept that coverage improves gradually as pages are written.

Then run a CHECKDB. It performs its own consistency checking regardless of the page verify setting, and it is the way to establish whether anything is already wrong that the weaker verification never reported.

DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;

Fix model too, so databases created in future start with CHECKSUM.

How long it takes

About an hour for the setting across an instance. The index rebuilds that give you full coverage are a separate, larger piece of work.

Report Why you would go there
Suspect Pages Corruption already recorded, though with fewer entries than it should have.
Last DBCC CheckDB Known Good by Database When each database was last verified.
Database Overview The database settings including page verify.
Backup Status What you would restore from if this finds something.
Index Fragmentation The rebuilds that would checksum existing pages.
Check
Page verify option Databases with page verification set to NONE, which is worse.
DBCC CHECKDB Corruption Errors Found What to do when verification does fail.
DBCC CheckDB never run Whether anything is checking these databases at all.
Backups without checksum The same verification question applied to backups.

Frequently asked questions

Is there a performance cost to CHECKSUM? It is measurable in a benchmark and not noticeable in practice. It is the default for every version since SQL Server 2005 for that reason.

Why did the setting survive our upgrade? Upgrading a database keeps its existing settings, so a database created on SQL Server 2000 carries torn page detection forever unless somebody changes it. Only new databases get the new default.

Do I have to rebuild all the indexes? No, and on a large database you may not want to. Without it, protection covers only pages written after the change. Rebuilding is how you get complete coverage sooner.

We have never had corruption, so does this matter? With torn page detection you would only know about a subset of it. That is the argument for changing the setting: not that corruption is likely, but that the current setting reports less of it.