Quick Scan Report – Page Verify Options

What this check looks for

Databases where page_verify_option = 0 in sys.databases, which is NONE.

The three values are 0 for NONE, 1 for TORN_PAGE_DETECTION and 2 for CHECKSUM. This check reports the first. The second has its own check, because obsolete is a different finding from absent.

Why it matters

With page verification off, SQL Server has no way to tell a good page from a damaged one. It reads what is on the disk and hands it to the query.

This is worse than the obsolete torn page detection setting, and considerably worse than having no DBCC CHECKDB schedule, because of where the damage goes:

• Corruption is returned as data. A query gets wrong values with no error. Nobody knows. The wrong values go into reports, into downstream systems, and into decisions.
• msdb.dbo.suspect_pages stays empty, because it is populated when a verification fails and no verification is happening. So the one record that a page level restore depends on does not exist.
• Backups carry it forward silently. A backup reads pages and writes them out. With no checksums there is nothing to detect on the way past, so the damage is copied into every backup without comment.
• RESTORE VERIFYONLY WITH CHECKSUM cannot help either, because there are no checksums in the backup to verify.

With CHECKSUM, by contrast, SQL Server computes a checksum over the whole page when it writes it and verifies it on every read. A bad page raises error 824, the page is recorded in suspect_pages, and an alert can fire. You get told.

The setting usually arrives from age rather than intent. Databases created on SQL Server 2000 default to NONE, and upgrading a database never changes it. Occasionally somebody turns it off believing it costs performance; the cost is small enough that it has been the default since 2005.

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 not showing CHECKSUM wants changing; NONE is the urgent one.

Check model, or new databases inherit it:

SELECT [name], [page_verify_option_desc] FROM sys.databases WITH (NOLOCK) WHERE [name] = 'model';

And, since nothing has been verifying these pages, find out the actual state of the data:

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

How to fix it

One statement per database. Online, instant, no locks:

ALTER DATABASE [YourDatabase] SET PAGE_VERIFY CHECKSUM;

Across everything that needs it:

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'ALTER DATABASE ' + QUOTENAME([name]) + N' SET PAGE_VERIFY CHECKSUM;' + CHAR(13)
  FROM sys.databases WITH (NOLOCK)
 WHERE [page_verify_option] <> 2 AND [database_id] > 4 AND [state_desc] = 'ONLINE';
PRINT @sql;
EXEC sp_executesql @sql;

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. On a large table that has not been written to, that could be most of the pages, indefinitely.

To get full coverage sooner, rebuild the indexes, which rewrites every page:

ALTER INDEX ALL ON [dbo].[YourTable] REBUILD;

Then run DBCC CHECKDB. It does its own consistency checking regardless of this setting, and it is the way to find out whether something is already wrong that has never been reported. Do this before assuming the databases are clean, because until now nothing has been looking.

And back up with CHECKSUM from now on, which verifies page checksums as the backup reads them, turning every backup into a partial integrity check:

BACKUP DATABASE [YourDatabase] TO DISK = N'...' WITH CHECKSUM, COMPRESSION;

Fix model, so this stops arriving with new databases.

How long it takes

About an hour for the settings across an instance. The index rebuilds that give complete coverage, and the first CHECKDB, are larger pieces of work worth scheduling.

Related reports

Related checks

Frequently asked questions

Is there a performance cost to CHECKSUM? Measurable in a benchmark, not noticeable in practice. It has been the default since SQL Server 2005 for that reason.

Why did our upgrade not change it? Upgrading a database preserves its settings. Only newly created databases get the current default, and only if model has it.

Do I have to rebuild every index? No. Without it, protection covers pages written from now on. Rebuilding is how you reach full coverage sooner, and on a large database it is a scheduling decision.

We have never had corruption. With verification off you would not have been told. That is the argument for the change rather than against it.