Table Consistency Errors

Question

We have encountered database corruption / consistancy issue in one of our database tables, which I'd like to run past the community.

Disclaimer: This is a MSSQL database we (the DBA team) have inherited. There are no non-corrupted backups of the database. The corruption has been there for an unknown amount of time (no checks were being performed prior to us inheriting it).

We've restored a copy of the database to a safe location, while we work on this issue. I started off by running this statement against the database table;

DBCC CHECKTABLE ('dbo.TableA') WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLOCK;

Here is the result set;

Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1436584206, index ID 1, partition ID 72057637929943040, alloc unit ID 72057603756130304 (type LOB data). The off-row data node at page (1:197568), slot 0, text ID 14217500622848 is referenced by page (1:191179), slot 0, but was not seen in the scan.

Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1436584206, index ID 1, partition ID 72057637929943040, alloc unit ID 72057603756130304 (type LOB data). The off-row data node at page (1:197569), slot 0, text ID 14217500622848 is referenced by page (1:191179), slot 0, but was not seen in the scan.

Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1436584206, index ID 1, partition ID 72057637929943040, alloc unit ID 72057603756130304 (type LOB data). The off-row data node at page (1:197570), slot 0, text ID 14217500622848 is referenced by page (1:191179), slot 0, but was not seen in the scan.

Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1436584206, index ID 1, partition ID 72057637929943040, alloc unit ID 72057603756130304 (type LOB data). The off-row data node at page (1:197571), slot 0, text ID 14217500622848 is referenced by page (1:191179), slot 0, but was not seen in the scan.

Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1436584206, index ID 1, partition ID 72057637929943040, alloc unit ID 72057603756130304 (type LOB data). The off-row data node at page (1:197572), slot 0, text ID 14217500622848 is referenced by page (1:191179), slot 0, but was not seen in the scan.

Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1436584206, index ID 1, partition ID 72057637929943040, alloc unit ID 72057603756130304 (type LOB data). The off-row data node at page (1:197573), slot 0, text ID 14217500622848 is referenced by page (1:191179), slot 0, but was not seen in the scan.

Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1436584206, index ID 1, partition ID 72057637929943040, alloc unit ID 72057603756130304 (type LOB data). The off-row data node at page (1:197574), slot 0, text ID 14217500622848 is referenced by page (1:191179), slot 0, but was not seen in the scan.

Msg 8965, Level 16, State 1, Line 1 Table error: Object ID 1436584206, index ID 1, partition ID 72057637929943040, alloc unit ID 72057603756130304 (type LOB data). The off-row data node at page (1:197575), slot 0, text ID 14217500622848 is referenced by page (1:191179), slot 0, but was not seen in the scan.

Msg 8929, Level 16, State 1, Line 1 Object ID 1436584206, index ID 1, partition ID 72057637929943040, alloc unit ID 72057638792658944 (type In-row data): Errors found in off-row data with ID 14217500622848 owned by data record identified by RID = (1:319428:15)

CHECKTABLE found 0 allocation errors and 9 consistency errors in table 'TableA' (object ID 1436584206). repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (DB1.dbo.TableA).

Armed with this information, I then proceeded to use the DBCC PAGE command to check each of the pages that were mentioned in the above error output.

DBCC TRACEON(3604)  
GO

DBCC PAGE ('DB1', 1, 197568, 1);
GO

And here was the result;

As you can see from the page information above, there is no data in these pages anymore, yet they still remain.

This prompts me to ask two questions..

1. Has anyone else experienced this issue before?
2. I understand that using the repair_allow_data_loss option will drop the pages mentioned above, but because there is no data within the pages, no data should actually be lost from the table. Is that correct thinking?

Answer 1

Okay, so it turns out that running the REPAIR_ALLOW_DATA_LOSS option caused these errors to occur. When I restored the corrupted database again, and ran DBCC CHECKDB again, I got the true, original error which was about multiple IAM pages containing allocations for the same intervals.

checkdb "Multiple IAM pages for object"

This link helped us resolve the problem, which simply required us to rebuild the table.

Posting this answer as hopefully it might be useful for someone else who is looking at corruption issues for the first time.