The CHECKDB That Lied
Some bugs announce themselves with a crash. This one announced itself with a green checkmark. A client's database was throwing "No issues found" on a routine integrity check — while thousands of rows in a billing table were quietly corrupt underneath. Here's how that happened, and how it got fixed.
The symptom
The trouble surfaced not in production but in restored copies used for testing. Some of those copies threw Msg 2570, a data-purity error: a stored value doesn't match the legal range of its declared type — in this case, a decimal(9,2) column called ProRate. In this case, the page structure was intact; the problem was an invalid stored column value rather than a page-structure failure. It just means the bytes decode to something the type shouldn't allow. Two things matter immediately about 2570: DBCC's automatic repair options can't fix it (the engine has no way to guess the "right" value), and if it shows up in restores, the bad bytes are already baked into the backups — rolling back doesn't help unless you go back far enough to predate the damage.
The plot twist
A plain DBCC CHECKDB on the latest refresh came back completely clean. But older refreshes of the same database had shown thousands of these errors. Data doesn't heal itself, so something else was going on: the check wasn't actually looking.
It turns out data-purity validation has quirky history. Databases created on SQL Server 2005 or later always run these checks — no way to disable them. But databases created on SQL Server 2000 or earlier (even if since upgraded) don't get automatic purity checks until they complete one clean CHECKDB ... WITH DATA_PURITY run. That first clean pass "blesses" the database permanently. The client believed their database was a 2005-era database. The boot page said otherwise.
The flag that explained everything
Querying the boot page directly (DBCC DBINFO) revealed two telling fields: dbi_createVersion = 539 (the SQL Server 2000 signature — 2005 would read 611) and dbi_dbccFlags = 0 (meaning purity checking had never been turned on). The database had been born on SQL Server 2000 and silently carried that legacy status through years of upgrades. The "clean" CHECKDB wasn't lying exactly — it was just never checking column values in the first place.
Re-running with WITH DATA_PURITY explicit brought the errors flooding back: 7,709 of them, nearly all in one table, one column, one data type — a strong sign of a single root cause.
Real values or garbage?
Oddly, querying the column directly returned only sane, in-range numbers. That's because SQL Server uses two different decoders: a lenient one for ordinary reads, and a strict one inside CHECKDB that rejects malformed bytes. The lenient path can produce a plausible-looking number from bytes that are actually invalid — which is exactly why no WHERE clause could ever find these rows. Only DBCC PAGE, reading the raw bytes, showed the truth: ProRate = INVALID COLUMN VALUE.
Finding all 7,709 rows
Since value-based queries were useless, the fix had to work in physical coordinates. Capturing CHECKDB's output with WITH TABLERESULTS turned every error into a queryable row (file, page, slot). Joining that to sys.fn_PhysLocCracker mapped physical locations back to primary keys — carefully selecting every column except the corrupt one, to avoid tripping the same invalid read. The catch: physical locations are only valid for the exact state of the database when CHECKDB ran, so this had to happen on a static, unwritten restored copy, with results cross-checked against DBCC PAGE and the original error count.
Deciding — and applying — the correct value
Most corrupt rows were old, cancelled billing cycles. The read path rendered them as 0.00, but that's not proof — corrupted decimal bytes often collapse to zero anyway. What made zero defensible was checking uncorrupted cancelled cycles elsewhere in the table, which consistently carried ProRate = 0.00. That business rule, not the suspect bytes themselves, justified the fix.
The actual update followed careful discipline: match on full keys, write explicit literals rather than trusting the old value, stage everything locally first, reconcile row counts inside a transaction, default to a rollback-unless-committed preview, and rehearse the whole thing on the restored copy before ever touching production.
Closing the loop
The final step: re-run CHECKDB ... WITH DATA_PURITY. A clean result confirmed the fix worked — and, just as importantly, permanently flipped dbi_dbccFlags to 2. From then on, every routine CHECKDB validates column values automatically. The database can no longer claim "no issues found" while hiding invalid data.
