2

DBCC CHECKDB results for 'tablename':

Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1637580872, index ID 1, partition ID 72057596516040704, alloc unit ID 72057594048020480 (type LOB data), page ID (23:853329) contains an incorrect page ID in its page header. The PageId in the page header = (0:96).

Msg 8928, Level 16, State 1, Line 1
Object ID 1637580872, index ID 1, partition ID 72057596516040704, alloc unit ID 72057594048020480 (type LOB data): Page (23:853329) could not be processed. See other errors for details.

Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1637580872, index ID 1, partition ID 72057596516040704, alloc unit ID 72057594048020480 (type LOB data). The off-row data node at page (23:853329), slot 0, text ID 12904397012992 is referenced by page (16:840512), slot 0, but was not seen in the scan.

This is the only table I have a problem with.

As this issue occur when purge some old data so we don't have enough old backup to say clean. Restore option may not possible.

Now we are looking into repair. Is single table repair possible?

Can we rebuild index as problem is with clustered index?

  • No rebuilding index will not resolve corruption issue with clustered index IT MIGHT help with non clusted index. How about restoring from known clean backup please check backup with restore verifiyonly – Shanky Jul 22 '14 at 13:08
2

Never use REPAIR_ALLOW_DATA_LOSS. I would do a database restore before that.

I suggest following this order:

  1. REPAIR_FAST (works 1 of 10 times).
  2. REPAIR_REBUILD (works up to 50% of the time).
  3. Restore database.

Full syntax:

DBCC CHECKALLOC   
[  
    ( database_name | database_id | 0   
      [ , NOINDEX   
      | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]  
    )  
    [ WITH   
        {   
          [ ALL_ERRORMSGS ]  
          [ , NO_INFOMSGS ]   
          [ , TABLOCK ]   
          [ , ESTIMATEONLY ]   
        }  
    ]  
]  
-1

See if you can select into another table: select * into newtable from oldtable

If that is not possible, and you do not have a good backup, and you have COMPLETELY RUN OUT OF ANY OTHER OPTIONS, then you can run DBCC CHECKTABLE (tablename, REPAIR_ALLOW_DATA_LOSS) http://technet.microsoft.com/en-us/library/ms174338(v=sql.110).aspx

WARNING!!!! If you run DBCC CHECKTABLE with the REPAIR_ALLOW_DATA_LOSS, there is a very high probability that you WILL LOSE DATA. Also, there is no guarantee that DBCC CHECKTABLE (tablename, REPAIR_ALLOW_DATA_LOSS) will be able to repair the corruption.

BEFORE attempting ANY repair, shut down SQL Server and copy the .mdf and .ldf files off to a safe place, so you can always get back to your starting point.

  • 2
    Anything with REPAIR_ALLOW_DATA_LOSS should be your absolute final option. You will lose data running this command. – Mark Sinkinson Jul 23 '14 at 9:10
  • Neeraj commented that he doesn't have a backup and they are looking into repair. He asked if single table repair is possible. My answer is correct. – stacylaray Jan 19 '15 at 15:41
  • This answer should at least include a big warning sign that it could (will?) introduce data loss. – Tom V - Team Monica Oct 11 '16 at 14:24
  • I will edit my answer with a big, red warning sign that it WILL introduce data loss. – stacylaray Nov 17 '16 at 4:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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