Msg 2570 DBCC CHECKDB

Here is how I worked through a handful of Msg 2570 errors I received from DBCC CHECKDB. Fortunatley the errors were on a DEV system were I was afforded the luxury of updating the data with no problems. If this is a production machine then you need to tread more lightly and work with the application owners to make sure you restore the data as best as possible.

Msg 2570 errors will not be fixed by running the REBUILD_REPAIR option. You have to address the issues with the data.

Sample Error:

Msg 2570, Level 16, State 3, Line 1
Page (1:2718613), slot 22 in object ID 1079167090, index ID 0, partition ID 72057594249084928, alloc unit ID 72057594292207616 (type "In-row data"). Column "column1" value is out of range for data type "decimal".  Update column to a legal value.
CHECKDB found 0 allocation errors and 6 consistency errors in table 'table_name' (object ID 1079167090).

Using the Page and Slot information from the error above you use the DBCC PAGE procedure to find the data page. The table name and database name will be listed in the DBCC error output.

DBCC PAGE ('Database_Name',1,2718613,3) WITH TABLERESULTS

Several rows we be output here. Use your slot number to target the bad data. Sample output

dbcc_BadData

Once you target the row that contains the bad data a simple UPDATE statement can be executed to fix the data. Again I was in a DEV system so I could update the data without much thought. Proceed carefully in production. Once you have fixed all the Msg 2570 errors take a database backup and re-run the DBCC CHECKDB.

Advertisements