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.

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.

share|improve this question
add comment

migrated from serverfault.com Aug 25 '13 at 22:38

This question came from our site for professional system and network administrators.

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
2  
I ran the repair_allow_data_loss option, then I simply deleted all the remaining records out of that table. After running dbcc checkdb again it came back and said all was good. –  Chris Lively Aug 23 '13 at 19:56
    
How do you know if your data integrity has been preserved, you only know that the database integrity is ok. –  mdpc Aug 24 '13 at 3:43
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.