The CHECKDB That Lied: A Data-Purity Ghost Story from a SQL Server 2000 Database

Every so often you hit a bug whose scariest quality is how calm it looks. This one greeted us with a green checkmark and the words “No issues found” — while thousands of rows in a billing table were quietly invalid. Here’s how a database created back in the SQL Server 2000 era hid its corruption in plain sight, what tools we used to drag it into the light, and the one obscure boot-page flag that explained the whole thing.

The symptom: Msg 2570 in restored copies

It started the way these things usually do — not in the production database, but on the side. A client refreshes several working copies of an important database from a nightly backup. On some of those restored copies, an integrity check was thrown:

Error 2570 is a data-purity error. It doesn’t mean a page is torn or an index is inconsistent — the physical structure of the database can be perfectly healthy. It means a column value doesn’t conform to the domain of its declared data type: a decimal whose stored bytes encode something outside the legal range, a date that isn’t a real date, text with invalid byte sequences. The value is illegal at the storage level even if everything around it is fine.

Two things about 2570 matter from the very first minute:

• DBCC’s repair options can’t fix it. REPAIR_ALLOW_DATA_LOSS is useless because the engine has no way to know what the value should be. The fix is always a manual UPDATE to a correct value you determine yourself.

• If it’s in restored copies, the bad bytes were baked into the backups. Restores faithfully reproduce what was backed up, corruption included. Rolling back to an older backup doesn’t escape it unless you go back far enough to predate the damage.

The plot twist: a clean check that wasn’t checking

Here’s where it got strange. We ran a straightforward integrity check on the latest refresh:

No issues found. Clean. But older refreshes of the same database had shown thousands of 2570s. Data doesn’t usually heal itself. So either the bad rows had been fixed upstream, or — the possibility that turned out to be true — the check simply wasn’t looking.

Data-purity validation has a version-dependent history that trips up a lot of people:

• Databases created on SQL Server 2005 or later have column-value checks enabled permanently. They run automatically as part of every normal CHECKDB and can’t be turned off. You never think about them.

• Databases created on SQL Server 2000 or earlier, then upgraded, do not get these checks automatically. The engine won’t run data-purity validation on them until you’ve performed one full DBCC CHECKDB ... WITH DATA_PURITY that completes with zero errors. That first clean run “blesses” the database and turns automatic checking on from then on.

The client believed the database was created in 2005. If that were true, the clean CHECKDB would be trustworthy. So we went to the source of truth — the boot page.

The flag that explained everything: dbi_dbccFlags

You can read a database’s boot page directly:

Two fields in that output settle the entire question:

• dbi_createVersion — the internal version the database was created on. This, not the compatibility level, governs data-purity behavior. (Compatibility level controls query semantics; it has nothing to do with whether purity checks run.)

• dbi_dbccFlags — set to 2 when data-purity checking is on, 0 when it isn’t.

The result:

dbi_createVersion 539 is the SQL Server 2000 value (SQL Server 2005 is 611). Despite the assumption, this database was born on SQL Server 2000 and upgraded over the years into a modern instance. And dbi_dbccFlags = 0 meant the first, “blessing”, run never happened — automatic data-purity checking was never been turned on.

That single flag explained the whole mystery. The plain DBCC CHECKDB came back clean because it silently skipped data-purity validation entirely. “No issues found” wasn’t a statement that the data was healthy — it was a statement that everything except the thing generating the 2570s had been checked. The older refreshes that caught the errors had been run with WITH DATA_PURITY explicitly. The latest one hadn’t. Same corruption, different check.

The lesson, front and center: on any database that ever lived on SQL Server 2000, check dbi_dbccFlags. If it’s 0, your routine integrity checks are not validating column values, and a clean result is giving you false comfort. The fix is one error-free WITH DATA_PURITY run, which flips the flag to 2 permanently.

Forcing the check to look

With the flag understood, we ran the check that correctly validates column values:

The errors came flooding back — 7,709 consistency errors, nearly all in one table (BillingCycle), one column (ProRate), one data type (decimal(9,2)). A high count, but strikingly homogeneous: one column, one failure mode, which almost always means one root cause and one fix strategy rather than 7,709 individual problems.

Real value or garbage? Reading past the read path

The next question was whether these were genuinely bad numbers or legitimate values that had simply become non-conforming. That distinction decides whether you can fix them cheaply or require reconstruction.

The first surprise: querying the column with aggregates worked fine and returned entirely sane numbers, inside the legal range for decimal (9,2).  A WHERE clause hunting for out-of-range values returned nothing. If the queries all say the data is fine, why does CHECKDB disagree?

Because they use two different decoders. The query read path is lenient — it decodes stored bytes into a number and moves on. CHECKDB and DBCC PAGE use a strict decoder that validates the bytes against the legal domain and rejects anything malformed. For these rows, the two disagree: the read path produces a plausible value; the strict checker refuses. That’s exactly why no WHERE clause can find these rows — every query flows through the lenient path that can’t see the problem. Only CHECKDB, reading raw bytes, can.

To see the truth we went to the storage itself, using the page and slot coordinates straight out of the error messages:

And there it was, in the row dump:

Every other column in the row is decoded cleanly. The physical length was exactly right for decimal (9,2), which ruled out a “column was narrowed from something wider” theory — the bytes were the right size but encoded an illegal value, a classic fingerprint of numeric data persisted by the SQL Server 2000 engine that the modern strict check rejects. Crucially, this confirmed we could not trust the value the read path returned as the true original.

Finding all 7,709 rows without a WHERE clause

If value-based queries can’t see these rows, how do you assemble the full list to fix? The answer is to work in physical coordinates and translate them back to keys. Two tools make this a set-based operation instead of a page-by-page slog:

1. Capture CHECKDB output as data with WITH TABLERESULTS, so every page and slot becomes a queryable row:

From there, filter to Error = 2570 and parse file/page/slot out of the message text (bulletproof across SQL versions, since the message shape Page (f:p), slot s in object is stable).

2. Map physical locations back to rows with sys.fn_PhysLocCracker, which expands each row’s %%physloc%% locator into file/page/slot. Join that to the corrupt-location list — and select every column except the corrupt one, so nothing trips the invalid read:

One essential caveat: physical locations are only valid for the exact state the database was in when CHECKDB ran. A page split, an index rebuild, even an autostats-triggered write can shift a row to a different slot, and then those coordinates resolve to the wrong row with no error raised. The defense is to do all of this on a static, restored copy that nothing writes to, and to validate the result two ways: spot-check a handful of the located rows with DBCC PAGE to confirm they really show INVALID COLUMN VALUE, and reconcile the row count against the number of 2570 messages. When both agree, you know the mapping held.

Deciding the correct value

We now had every corrupt row pinned to a primary key. But what should ProRate be? For financial data, “whatever the read path returns” is not an acceptable answer — it might be a lenient decode of genuinely wrong bytes.

The rows told a story: most were old billing cycles from 2009, flagged IsCancelled = 1, and the read path rendered them as 0.00. That’s suggestive but not proof — malformed decimal bytes very commonly collapse to zero through the lenient decoder, so “it reads 0.00” can be an artifact rather than the truth.

What made zero defensible was a business rule confirmed against clean data: cancelled cycles that were not corrupt (unflagged rows elsewhere in the table) consistently carried ProRate = 0.00. Once uncorrupted cancelled records establish that cancelled cycles are supposed to be zero, setting the corrupt cancelled ones to zero stops being a guess and becomes a reconstruction grounded in the data’s own rules. The takeaway: determine the correct value independently — from business logic or clean sibling rows — never from the bytes you’re trying to fix.

The fix: keyed, reconciled, and reversible

With verified keys and a verified target value, the correction itself is a careful UPDATE. The principles that kept it safe:

•     Match on the full key (not a partial key that could sweep in unflagged rows), and write an explicit literal (SET ProRate = 0.00) rather than round-tripping the suspect value (SET ProRate = ProRate), which would trust the lenient decode.

•     Stage remote key lists into local temp tables before opening the transaction. Pulling them local first avoids promoting the work into a distributed (MSDTC) transaction and keeps every join local and predictable.

•     Reconcile counts inside the transaction. Compute how many rows you expect to change, capture @@ROWCOUNT after the UPDATE, and roll back automatically if they don’t match. Match means you touched exactly the intended rows and nothing else.

•     Preview by default. Wrap everything in a transaction that rolls back unless a @Commit flag is flipped, so you can inspect the reconciliation counts and result grids before anything is permanent. Wrap it in TRY/CATCH with SET XACT_ABORT ON so any runtime error rolls the whole thing back cleanly.

•     Do it on the restored copy first, end to end, before it ever touches production. Then back up production and run the same script there, ideally in a quiet window.

The finishing move: flip the flag

The last step is the one that prevents a sequel. After the corrected values are in place, run:

If it completes with zero errors, two good things happen at once: you’ve confirmed the corruption is gone, and that clean run flips dbi_dbccFlags to 2. From that moment on, every routine CHECKDB validates column values automatically. The database can no longer tell you “No issues found” while hiding invalid data.

Takeaways

• Msg 2570 is a data-purity error — an illegal value, not structural corruption. DBCC repair can’t fix it; you determine and write the correct value yourself.

• Data-purity checks are not automatic on databases created before SQL Server 2005. Until one clean WITH DATA_PURITY run blesses the database, plain CHECKDB silently skips them.

• dbi_createVersion tells you where a database was really born, and it — not the compatibility level — governs this behavior. A value of 539 means SQL Server 2000.

• dbi_dbccFlags = 0 means your integrity checks are lying by omission. Check it on any legacy database. Get it to 2.

• The query read path and the strict checker can disagree. When they do, value-based queries can’t find the bad rows — reach for DBCC PAGE, WITH TABLERESULTS, and sys.fn_PhysLocCracker, all against a static restored copy.

• On financial data, reconstruct the correct value from business rules or clean sibling rows — never from the corrupt bytes — and make every fix keyed, count-reconciled, and reversible.