Sql-server – DBCC checkdb showing failure after a restore

dbccsql server

We restored a backup into a new database. After running a few delete queries we received the following message:

Msg 824, Level 24, State 2, Line 1 SQL Server detected a logical
consistency-based I/O error:…

I ran DBCC checkdb and saw numerous lines such as:

Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:783) in object ID 0, index ID -1, partition ID 0, alloc unit ID 72057599497994240 (type Unknown), but it was not detected in the scan.

and 

Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2083043448, index ID 1, partition ID 72057608168079360, alloc unit ID 72057608304721920 (type LOB data), page (1:307605). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2083043448, index ID 1, partition ID 72057608168079360, alloc unit ID 72057608304721920 (type LOB data). The off-row data node at page (1:307605), slot 0, text ID 1368915968 is referenced by page (1:307603), slot 0, but was not seen in the scan.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 2083043448, index ID 1, partition ID 72057608168079360, alloc unit ID 72057608304721920 (type LOB data). The off-row data node at page (1:307605), slot 1, text ID 903086080 is referenced by page (1:307648), slot 0, but was not seen in the scan.
Msg 8929, Level 16, State 1, Line 1
Object ID 2083043448, index ID 1, partition ID 72057608168079360, alloc unit ID 72057609480241152 (type In-row data): Errors found in off-row data with ID 1368915968 owned by data record identified by RID = (1:43506:13)
Msg 8929, Level 16, State 1, Line 1
Object ID 2083043448, index ID 1, partition ID 72057608168079360, alloc unit ID 72057609480241152 (type In-row data): Errors found in off-row data with ID 903086080 owned by data record identified by RID = (1:102876:38)

The final section of output was:

CHECKDB found 1 allocation errors and 12 consistency errors in database 'Clients'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Clients).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

My question is: Should I attempt to restore the database again or proceed with attempting to repair? I only hesitate because it is a rather large DB and restoring takes quite a while.

UPDATE: a bit more info.
All of the table errors are related to a single table I could just drop and recreate. However I don't understand what the IAM error really means or its impact.

Best Answer

Should I attempt to restore the database again or proceed with attempting to repair? I only hesitate because it is a rather large DB and restoring takes quite a while.

I'd probably do both, but restore it to a new name.

All of the table errors are related to a single table I could just drop and recreate. However I don't understand what the IAM error really means or its impact.

If the data in that table really, truly doesn't matter, and you can really, truly drop and recreate the table, that's probably the fastest option. Especially since "repair_allow_data_loss" will probably cause data loss, yes.

As for what the IAM error means:

Short version: Your table innards are corrupted.

Longer version: The Index Allocation Map tells SQL Server where to get the actual objects. In this case, based on the references to RIDs I'm guessing this is a table without a clustered index (in other words, a heap), and that it's telling SQL where to get the data that was actively updated inside the heap. (One of the things that happens when a heap has updates is that if the new information doesn't fit in the old space, a pointer is created that tells SQL where to get the new information.)

Here's an article on IAM pages, and here's a good video on heaps in SQL Server that came up when I was looking for IAM info for you.

Related Question