Sql-server – Real corruption

corruptiondbccsql serversql-server-2012

We have a production database for Solarwinds Orion Network Performance Monitor in which we discovered corruption in a MapStudioFiles table following DBCC CHECKDB errors.

CHECKDB Output:

Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 747149707, index ID 1, partition ID 72062477575258112, alloc unit ID 72057594049724416 (type LOB data). The off-row data node at page (1:3414), slot 0, text ID 1031262163304448 does not match its reference from page (1:412), slot 22.

Msg 8961, Level 16, State 1, Line 1
Table error: Object ID 747149707, index ID 1, partition ID 72062477575258112, alloc unit ID 72057594049724416 (type LOB data). The off-row data node at page (1:160077), slot 0, text ID 1026498368241664 does not match its reference from page (1:408), slot 11.

Msg 8929, Level 16, State 1, Line 1
Object ID 747149707, index ID 1, partition ID 72062477575258112, alloc unit ID 72062476506234880 (type In-row data): Errors found in off-row data with ID 182073438109696 owned by data record identified by RID = (1:408:11)

Msg 8929, Level 16, State 1, Line 1
Object ID 747149707, index ID 1, partition ID 72062477575258112, alloc unit ID 72062476506234880 (type In-row data): Errors found in off-row data with ID 186837233172480 owned by data record identified by RID = (1:412:22)

CHECKDB found 0 allocation errors and 4 consistency errors in table 'MapStudioFiles' (object ID 747149707).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'OrionNPM'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (OrionNPM).


We don't have a valid backup because it seems this error was happening a long time but since the server was not on our monitor tool nobody noticed the errors on DBCC job and there wasn't any e-mail being sent on failure.

On a TEST server we restored a backup just to check the repair allow data loss method and DBCC CheckTable was able to fix two rows (rows that were deleted)

CHECKTABLE fixed 0 allocation errors and 2 consistency errors in table 'MapStudioFiles' (object ID 747149707).

And then after the second execution all issues were fixed. Table has 249 rows from 251 rows before the repair.


In another test:

I've noticed that if you query the table doing a select you don't get any error and shows all the data on the table. (is this because the corruption is on a LOB column?)

So my first thought was copy the table into another table to see if any errors arise. I was able to do it without any problem using SELECT INTO, after that ran the DBCC CHECKTABLE and everything is OK and the new table has the 251 rows.

Does this mean that the corruption is gone? Or because it is on a LOB column you would only see the issue if you try to get the object out of the database or if the application queries the table?

Best Answer

Is the corruption gone?

If DBCC CHECKDB returns no errors the corruption is gone.

CHECKDB found 0 allocation errors and 0 consistency errors in database 'BFEnterprise'.

Is the loss of data a problem?

This is the real question, the only way to tell is to test in your system. Presumably if the corrupted data was not causing issues before the repair deleted it, loss of the data is not going to cause issues either.

Optimally you will want to test in your dedicated test system.