Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have encounter some database corruptions in the past but I knew which object has hit the road. In this situation I run a DBCC CHECKDB against a problematic SQL Server 2005 database and I get the following errors

    Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:2388) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:8807) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
Msg 8929, Level 16, State 1, Line 1
Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 281474980642816 (type In-row data): Errors found in off-row data with ID 1454243840 owned by data record identified by RID = (1:728:2)
Msg 8928, Level 16, State 1, Line 1
Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data): Page (1:2388) could not be processed.  See other errors for details.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). The off-row data node at page (1:2388), slot 0, text ID 1454243840 is referenced by page (1:728), slot 2, but was not seen in the scan.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'sys.sysobjvalues' (object ID 60).
Msg 8928, Level 16, State 1, Line 1
Object ID 393768460, index ID 1, partition ID 72057594063421440, alloc unit ID 72057594069647360 (type In-row data): Page (1:8807) could not be processed.  See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 393768460, index ID 1, partition ID 72057594063421440, alloc unit ID 72057594069647360 (type In-row data). Page (1:8807) was not seen in the scan although its parent (1:2862) and previous (1:8806) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 393768460, index ID 1, partition ID 72057594063421440, alloc unit ID 72057594069647360 (type In-row data). Page (1:10128) is missing a reference from previous page (1:8807). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'reservation_packages' (object ID 393768460).
Msg 8928, Level 16, State 1, Line 1
Object ID 1513772450, index ID 0, partition ID 72057594064994304, alloc unit ID 72057594071285760 (type In-row data): Page (1:5211) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1513772450, index ID 0, partition ID 72057594064994304, alloc unit ID 72057594071285760 (type In-row data), page (1:5211). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 63047689 and -4.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'reservation_services_log' (object ID 1513772450).
CHECKDB found 0 allocation errors and 10 consistency errors in database 'ChJ'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ChJ).

Object ID 0?? index ID -1 ?? partition ID 0?? I cannot make out anything out of it

The database is in Simple Recovery mode (unfortunately) and I have a month old backup.

I must have it up an running because it contains vital data.

I would appreciate any help you could give me.

Thanx in advance!

EDIT1:Unfortunately Red Gate's Data Compare cannot register the database while it is scanning the tables... :(

share|improve this question
    
The error Table error: Object ID 0... is troublesome :( –  Yuck Feb 20 '13 at 16:55
1  
Further, select * from sys.objects where object_id = 60 on my databases is referring to the system table sysobjvalues. This question may be better on DBA.SE and even then you may be SOL. –  Yuck Feb 20 '13 at 16:56
11  
Hopefully someone else can give you some magical answer, but I STRONGLY suspect the resolution will be to restore the month old backup and deal with the data loss. If it was vital data then it's unlikely a competent DBA allowed it to be in simple recovery with a month between backups, so there may be resume-updating as well. –  JNK Feb 20 '13 at 17:26
1  
@Yuck The database is one of our customers that declined to hire a DBA and said that he will maintain the database by himself.The hotel was closed for the season, he did not backup regularly and now he is missing reservations of one month. –  PanosPlat Feb 20 '13 at 18:44
2  
@PanosPlat You misunderstand me. By online, I meant is it in an 'online' state. Or is it showing as 'offline' or 'suspect' when you view the database in SSMS? –  Mark Storey-Smith Feb 20 '13 at 18:54
show 3 more comments

migrated from stackoverflow.com Feb 20 '13 at 17:09

This question came from our site for professional and enthusiast programmers.

2 Answers

As you have an old backup with the correct schema, the problem database is online and you've successfully queried several tables, I'd be inclined to try get a dump of the raw data as fast as possible.

  1. Shut down the applications accessing the database.
  2. BCP export the data, table by table to files. As disk corruption could be the source of your woes, export the data to a portable drive or to a fileshare on another machine.
  3. Restore your old backup to a known good server.
  4. Depending on the complexity of the database schema either truncate the data from your restored backup or script out the schema and create a new empty database.
  5. BCP the data back in.

The data may contain inaccuracies, it may be missing rows or contain spurious values. But with any luck you'll end up with more than was in the month old backup.

share|improve this answer
add comment
  1. Do not take the database offline. Also do not shut down the server. Either might make the database totally inaccessible.

  2. Take a full "copy only" backup.

  3. Try to read the backup file with Red-Gate datacompare

  4. use this query with the allocation_unit_id values you have in the checkdb output:

    SELECT o.name,p.index_id,p.partition_number FROM sys.allocation_units a JOIN sys.system_internals_partitions p ON a.container_id = p.partition_id JOIN sys.all_objects o ON p.object_id = o.object_id WHERE a.allocation_unit_id = '281474977103872'

It should show you the object name affected.

  1. restore the database with a different name (might fail)

  2. run checkdb with the repair option "REPAIR_REBUILD" on the restore. If the restore failed use the original.

  3. if nothing worked so far use the "REPAIR_ALLOW_DATA_LOSS" option of DBCC CHECKDB.

  4. if that does not work either, hire a consultant with experience in the corruption recovery field (like me)

share|improve this answer
    
Thanx Sebastian! I took a full copy-only backup but it cannot be restored. Tommorow in the office I will try the Data Compare thing. –  PanosPlat Feb 20 '13 at 20:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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