3

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;

enter image description here

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?
5
  • 1
    The corruption might be in the reverse: the rows which originally referenced this data have been dropped due to corruption. These pages are now your only record of them. up to you what you want to do with the data. option 3 of DBCC PAGE will give you all the data in the page. If you can restore a good backup then do so. Sep 21, 2022 at 14:59
  • Thanks for the reply, unfortunately we don't have a backup that we can restore which wouldn't have this same issue. I used option 2 with 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.
    – Tom
    Sep 22, 2022 at 13:12
  • 1
    Option 3 and 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/… Sep 22, 2022 at 13:24
  • Turns out the 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 :)
    – Tom
    Sep 22, 2022 at 17:13
  • Moral of the story: understand what needs fixing before you run REPAIR_ALLOW_DATA_LOSS and try fix it another way, maybe using REPAIR_REBUILD. Real moral of the story: sort out your backups. Sep 22, 2022 at 18:50

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.