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
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
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;

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?
Asked by Tom
(221 rep)
Sep 21, 2022, 01:57 PM
Last activity: Sep 22, 2022, 05:11 PM
Last activity: Sep 22, 2022, 05:11 PM