DBCC CHECKTABLE - 2570 error

Question

Running DBCC Checktable on one of my tables causes several errors:

Msg 2570, Level 16, State 3, Line 1 errors, Page (1:442), slot 1 in object ID 565577053, index ID 0, partition ID 72057594041270272, alloc unit ID 72057594046906368 (type "In-row data"). Column "XXXX" value is out of range for data type "decimal". Update column to a legal value.

Column XXXX is Decimal (18,4)

I've done some investigation suggested by: https://support.microsoft.com/en-us/help/923247/troubleshooting-dbcc-error-2570-in-sql-server-2005-and-later-versions

1)

SELECT XXXX FROM Table
WHERE XXXX > 99999999999999.9999
OR XXXX < -99999999999999.9999

This is not returning any rows

2)

DBCC TRACEON ( 3604 )
DBCC PAGE ( Database, 1 , 442, 3 )

I found entry with XXXX = INVALID COLUMN VALUE
I found primary key values for it

And when I run:

Select XXXX from Table where ID = ValueFound

It returns -0.4500 - valid value for Decimal (18,4).

Is this value correct or is this some random value?

Do I understand it correctly - the only way to fix this is to provide correct values for given fieds?

Answer 1

Are you sure you got the correct ID? It should be the PK (usually column 1) directly above the "INVALID COLUMN VALUE". If indeed correct, the corrupted value might only be in the index.

1. Try to UPDATE the column again with the same value.
2. Run another DBCC CHECKTABLE ('dbo.Table') WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY
3. Try to drop and recreate the index. It's the primary key (index 0), and probably clustered index, so it requires jumping through some hoops.
4. If that doesn't work (because of corruption), your last resort is to run DBCC CHECKTABLE ('Table', REPAIR_ALLOW_DATA_LOSS ) WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY