Data corruption-Errors found in off-row data- am I loosing data?
I need your help with understanding what is going on in my database. Check DB job detected some issues in one of the tables. There are two types of error messages. A lot of errors like below:
Table error: Object ID xxxxxx, index ID 1, partition ID xxxxx, alloc unit ID xxxx(type LOB data). The off-row data node at page (1:xxxx), slot 0, text ID 0 is pointed to by page (1:xxxx), slot 0 and by page (1:xxxx), slot 6.
and 3 errors like below:
Object ID xxx, index ID 1, partition ID xxx, alloc unit ID xxxx (type In-row data): Errors found in off-row data with ID xxx owned by data record identified by RID = (1:xxxx:xx)
All of them points to one table
table. If I run
select count(*) from tableI have N rows.
I recreated that DB at the dev server and run
DBCC CHECKTABLE('table', REPAIR_ALLOW_DATA_LOSS). As a result the the
countfrom that table is
N-3. At the same time (at the another copy) I simply tried to insert all records from old table to the new one. like below:
select * into new_table from tablecount of new table is
N. and the DBCC CHECKTABLE('new_table ') does not return any errors.
Does it mean that I solved a problem without data loses? Or am I missing something?
Personally, I would not trust data returned from a table CHECKDB found some problems.
Assuming there is backups (full and log) availabile, I would:
- Restore a copy of the database from the last full backup (or full+diff) prior to the first time checkdb found the error
- Restore all transaction log backups, up to the latest
- recover the database in STANDBY mode, so we can still restore more logs
- Run checkdb on the restored database
- If checkdb completed without error, compare the data between the healthy database
At this point, I would not bother fixing the existing database at the risk of facing more issues later. I would just schedule a maintenance window to restore the tail log and swap the databases (the nature and configuration of the database may influence my plans, of course).
In a situation where this option is not possible (i.e. no log backups available, or not even a reliable full backup), copy data to a new table like you tried is probably your best bet, but there is no way to insure the data you get is really what you should get.