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 table I 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 count from 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 table count 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:

    1. Restore a copy of the database from the last full backup (or full+diff) prior to the first time checkdb found the error
    2. Restore all transaction log backups, up to the latest
    3. recover the database in STANDBY mode, so we can still restore more logs
    4. Run checkdb on the restored database
    5. 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2