The alert fired at 03:12 on a Sunday: error 824, "SQL Server detected a logical consistency-based I/O error: incorrect checksum," on a read of one page in the order history table. By the time I was awake, the application had logged the same error forty more times — always the same page, 1:288137, in a table holding roughly 40,000 orders. The page was unreadable, everything around it was fine, and the two options on the table were a restore costing six hours of committed orders or a repair whose name contains the words ALLOW_DATA_LOSS. What we actually did was neither: we restored the backup to a second server, page-restored the single bad page back to production, and lost nothing. The whole thing took ninety minutes, and it went calmly only because the decision tree had been thought through before the incident.
This is that decision tree, plus the operational half nobody writes about: how to run CHECKDB regularly on a database that is too busy for CHECKDB. Applies to SQL Server 2016 through 2022, Enterprise edition called out where it matters.
What does CHECKDB actually check?
DBCC CHECKDB runs three families of checks against a database: allocation consistency (do the GAM, SGAM, IAM, and PFS structures agree about which pages belong to what), structural integrity (are page headers, slot arrays, and record structures well-formed, and do page checksums match), and logical consistency (do index keys match the rows they point to, do partitioning and computed-column relationships hold). The logical pass is the expensive one — it is what cross-checks every nonclustered index entry against the base table — and it is the pass that finds corruption the physical checks cannot see, like a perfectly checksummed page whose index points at a row that no longer exists. CHECKCATALOG, which validates system metadata, is included in the run.
One mechanism worth understanding because it drives the scheduling story: by default, CHECKDB does not read the live database. It creates an internal database snapshot at start and checks that consistent point-in-time image, which is why it does not block writers — and why it can fail outright if the snapshot cannot be created (sparse-file limits, full volumes, FILESTREAM quirks). WITH TABLOCK disables the snapshot and takes real locks instead, which makes the run shorter and the blocking worse. Choose deliberately, not by accident.
How do I run it on a database that never has a quiet window?
Split the work by depth and by location: run cheap physical checks frequently on production, and run the full logical check against a restored copy on a different server. The pattern that has served me on multi-terabyte databases:
-- frequent (nightly or several times a week), cheap:
DBCC CHECKDB(N'OrderHistory') WITH PHYSICAL_ONLY, NO_INFOMSGS;
-- full logical check, weekly, against a restored copy on a spare server:
RESTORE DATABASE [OrderHistory_check] FROM DISK = N'\backupOrderHistory_full.bak' WITH ...;
DBCC CHECKDB(N'OrderHistory_check') WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS;PHYSICAL_ONLY skips the logical pass but still verifies page checksums, torn pages, and allocation consistency — which covers the large majority of real corruption, because corruption usually arrives through the I/O path and checksums catch it there. The restored-copy strategy has a bonus that sells it to management: the same restore doubles as the backup verification from my backup restore testing notes, so one operation proves both the backup and the data. If you run Always On, doing full CHECKDB on a secondary helps but does not replace checking the primary — in-memory corruption on the primary can be written to the log and faithfully reproduced on secondaries, so the primary still needs its own physical checks. And know your last clean run: DBCC DBINFO WITH TABLERESULTS reports dbi_dbccLastKnownGood, the timestamp of the last corruption-free CHECKDB, which is the number that tells you how far back a clean backup must reach.
What do errors 823, 824, and 825 each mean?
They are three different severities of I/O trouble, and confusing them leads to wrong responses. Error 823 is an operating-system-level I/O failure — the read call itself failed — and it points at the storage stack: drivers, HBAs, the array. Error 824 is a logical consistency failure: the read succeeded but what came back is wrong — an incorrect checksum, a torn page, a bad page ID — which means something between the disk and SQL Server silently corrupted the bytes, the classic signature of a failing controller or a volume that lost power mid-write. Error 825 is the quiet killer: the read failed and succeeded only on retry. It logs once, the query completes, nobody pages — and the disk is telling you it is dying. An estate that alerts on 823 and 824 but not on 825 is ignoring the one warning that arrives while the hardware can still be replaced gracefully.
Every one of these errors also writes a row into msdb.dbo.suspect_pages with the database, file, page, and event type — the first table to query in any corruption incident, because it converts "we saw an error" into a list of exactly which pages are implicated. One caveat from experience: suspect_pages is capped at 1,000 rows and fills silently in a long-running incident, so query it early and archive it if the list matters.
What is the repair decision tree before touching REPAIR_ALLOW_DATA_LOSS?
Exhaust every option that preserves data, in this order, before considering the one that does not. First, identify scope: run CHECKDB on the affected database (or the restored copy) and read whether the damage is in nonclustered indexes or in data. Corruption confined to a nonclustered index is the lucky case — drop and recreate the index, done, no repair needed. Second, single corrupt data pages in a database with good backups are candidates for page restore:
RESTORE DATABASE [OrderHistory]
PAGE = '1:288137'
FROM DISK = N'\backupOrderHistory_full.bak'
WITH NORECOVERY;
-- then apply the log chain up to current, finishing with recoveryPage restore replays just that page from the backup and rolls it forward through the log — online in Enterprise edition — and it turned my Sunday incident from a six-hour outage into a ninety-minute non-event. Third, if the database will not come online at all, emergency mode (ALTER DATABASE ... SET EMERGENCY) makes it readable enough to extract data and assess damage before any repair runs. Only then does REPAIR_ALLOW_DATA_LOSS enter the conversation — and two things about it are documented and under-known: it can be wrapped in a user transaction and rolled back if the damage it does is worse than the damage it fixes, and what it actually does is deallocate the corrupt structures, which means the "data loss" is the rows on those pages, gone by design. REPAIR_REBUILD, the milder option, only rebuilds nonclustered indexes and loses nothing, but it cannot fix data-page corruption — if CHECKDB says ALLOW_DATA_LOSS is the minimum repair level, REPAIR_REBUILD is not a shortcut around it. Whatever you run, CHECKDB afterwards until it comes back clean; repair is not verified until the next full pass agrees.
What should standing monitoring catch before corruption does?
Four things, all cheap. Alert on errors 823, 824, and 825 from the error log, with 825 treated as a hardware-ticket severity rather than a database emergency. Snapshot msdb.dbo.suspect_pages on a schedule so the 1,000-row cap never silently eats your evidence. Record dbi_dbccLastKnownGood per database so "when was our last clean check" is a dashboard number, and alert when it ages past your CHECKDB cadence — a CHECKDB job that has been silently failing for months is functionally the same as never checking. And trend the CHECKDB run itself: duration growing steadily is a capacity signal for your maintenance window, and a sudden drop in duration is often the run failing early, not getting faster. The wait statistics notes cover the general discipline of trending before alerting, and corruption monitoring is exactly where that discipline pays.
Watching database integrity with MonPG when SQL Server support lands
The integrity signals that belong on a dashboard are last-known-good CHECKDB age per database, suspect_pages row counts, 823/824/825 occurrences from the error log, and CHECKDB run duration and success as a job-health fact. Corruption is rare, which is exactly why the instrumentation has to be standing — the Sunday you need dbi_dbccLastKnownGood is not the Sunday to start recording it. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and the SQL Server monitoring (coming soon) page carries the honest status. Until it ships, the queries in this article on a schedule are the whole monitoring stack.