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..
- Has anyone else experienced this issue before?
- 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?
DBCC PAGE
will give you all the data in the page. If you can restore a good backup then do so.DBCC PAGE
and that returned a memory dump of data, option 3 resulted the same results as above. I wonder if it's possible to find what specific data is in specific pages.WITH TABLERESULTS
should give you the actual data in the page. It's LOB data, so it's going to be confusing to sort through. You need to be aware of how LOB pointers work etc, see aboutsqlserver.com/2013/11/05/…REPAIR_ALLOW_DATA_LOSS
caused these errors. I have added an answer for this question giving more details, but wanted to thank you @Charlieface for your help. I actually learnt a lot during this process :)REPAIR_ALLOW_DATA_LOSS
and try fix it another way, maybe usingREPAIR_REBUILD
. Real moral of the story: sort out your backups.