A database can report healthy activity while a damaged page waits for its next read. Page verify CHECKSUM gives SQL Server a way to detect certain page changes after disk writes, but the setting and its coverage need to be understood.
What Page Verify CHECKSUM Does
PAGE_VERIFY controls what SQL Server records when a data page is written and what it checks when that page is read later. CHECKSUM calculates a value from the page and stores it in the page header. If the page changes unexpectedly on storage, a later read can reveal a mismatch. That is an early warning worth investigating with the recovery plan ready.
I do not describe CHECKSUM as a repair. It can detect certain damage, not put the correct data back. Which databases still use an older setting? Find them before deciding how to update the standard.
SELECT name, page_verify_option_desc
FROM sys.databases
WHERE database_id > 4
ORDER BY name;Compare the Three Settings
NONE provides no page verification protection from this option. TORN_PAGE_DETECTION records information intended to detect an incomplete page write. CHECKSUM covers the page contents more broadly and is the preferred setting for modern SQL Server databases. The options do not replace storage monitoring, backups, or integrity checks. A page can be logically wrong while its stored bytes and checksum still agree.
I look for NONE and TORN_PAGE_DETECTION in inherited databases. The setting can persist through years of upgrades if nobody revisits it. A new server build does not silently rewrite every old database option. State the current value in the estate inventory and plan a reviewed change.
Switch to Page Verify CHECKSUM Deliberately
Turning on page verify CHECKSUM is a database option change. Run it under the normal change process, confirm the target, and verify the result afterward. It does not scan every existing page and stamp a checksum immediately. SQL Server writes a checksum when each page is next written under the new setting. That distinction matters when an old database has pages that have not changed for years.
The command below uses a sample database name. Do not run it against an unknown production database without the standard review. Follow it with the read-only catalog query. I keep the before and after values with the change record.
ALTER DATABASE [Sales] SET PAGE_VERIFY CHECKSUM;
SELECT name, page_verify_option_desc
FROM sys.databases
WHERE name = N'Sales';
Know What Existing Pages Mean
A page written before CHECKSUM was enabled can still carry its prior verification form until SQL Server writes it again. Enabling the option changes future writes, not the physical history of every existing page at once. Do not claim complete page-level coverage immediately after a setting change. Plan regular integrity checks and monitor errors as the database continues normal work.
I explain this point to teams that expect an instant cleanup. The setting is preventive detection for page writes and reads. It does not prove old pages are clean. If there is already a storage concern, investigate it directly rather than waiting for pages to be rewritten.
Review suspect_pages With Context
SQL Server records certain suspect page events in msdb.dbo.suspect_pages. The table includes database_id, file_id, page_id, event_type, error_count, and last_update_date. An event type can indicate a bad checksum, torn page, repair, or restore. A row is evidence to investigate, not an instruction to run a repair command immediately.
This query shows recent entries with database names where available. Preserve the error log and storage evidence too. I check whether the page is still marked suspect and whether the database has a valid restore path before choosing an action.
SELECT DB_NAME(database_id) AS database_name,
file_id, page_id, event_type,
error_count, last_update_date
FROM msdb.dbo.suspect_pages
ORDER BY last_update_date DESC;Pair It With Integrity and Backups
Run DBCC CHECKDB on an appropriate schedule and investigate any finding. CHECKDB checks database structures and consistency that a page checksum alone does not cover. Backups with CHECKSUM add another layer of detection during backup and restore, but neither option turns a backup file into a proven recovery plan. Restore tests remain essential.
I review page errors alongside storage alerts and backup history. A checksum failure can reflect a serious I/O problem. Do not repeatedly read the page and hope it becomes fine. Preserve evidence, assess impact, and use the documented recovery options with a tested backup path.
Make Page Verify CHECKSUM Part of a Baseline
Include PAGE_VERIFY in the database configuration inventory for new and existing databases. Check it after restores and migrations, especially when an old database moves to a new instance. Alert on unexpected NONE or TORN_PAGE_DETECTION values according to your policy. Record intentional exceptions and review them.
Which part of protection does this option provide? It helps detect damaged page content when the page is read. The rest of the recovery story still needs backups, integrity checks, storage health, and a practiced restore. I keep those layers separate so one green setting does not hide a broken chain.
After changing PAGE_VERIFY to CHECKSUM, do not assume every existing page instantly carries a checksum. The checksum is written when a page is next modified and persisted. I would schedule integrity checks and maintain tested backups while the database gradually cycles through writes. CHECKSUM is a detection mechanism; it does not repair damaged data or replace DBCC CHECKDB.
Review suspect_pages in msdb as one signal, then investigate the error log and storage evidence. A row there indicates a page-level problem was recorded, but it needs context and a response plan. Repeated I/O warnings or integrity errors deserve prompt attention. The right action can include restoring from a known good backup and examining the underlying storage path.
I keep PAGE_VERIFY in the database baseline so a newly restored or newly created database does not quietly retain an old setting. A configuration query is cheap; discovering the omission after corruption is expensive.
Include the database’s page verification option in build and restore acceptance checks. A restored database carries its own setting, so the instance standard does not automatically replace it. I check the page verify CHECKSUM setting before declaring a new environment ready. If the setting is corrected, note the date and continue integrity checks. The transition to checksummed pages is gradual because pages are protected on their next write.
Related reading on this blog: 5 Don'ts When Database Corruption is Detected and Quick Look at Suspected Pages.
Page verify CHECKSUM is not a backup, it is an early detector that strengthens a wider recovery plan.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
