October 20, 2011 at 11:38 pm
Hello,
I am working on a corrupt database I downloaded it from Sqlskills.com named "CorruptDemoRestoreOrRepair.bak." Below is the errors returned from DBCC CHECKDB WITH ALL_ERRORMSGS, NO_INFOMSGS. Am using SQL Server 2008 R2.
I've read articles where people extract/copy data from the corrupt table into a new one/database. I've tried doing an EXPORT Data, COPY Database, INSERT Into, but they all bomb due I:O error messages. How did they accomplish this?
Running a DBCC repair_allow_data_loss fixes things, but this is a last resort
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:158) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
Msg 8928, Level 16, State 1, Line 1
Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594042384384 (type In-row data): Page (1:158) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594042384384 (type In-row data). Page (1:158) was not seen in the scan although its parent (1:154) and previous (1:157) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 2073058421, index ID 1, partition ID 72057594038386688, alloc unit ID 72057594042384384 (type In-row data). Page (1:159) is missing a reference from previous page (1:158). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'sales' (object ID 2073058421).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'Demo'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Demo).
October 21, 2011 at 5:14 am
Query for small ranges of data using the clustered index key. Lots of work, but you can avoid the corrupt portions of the tables that way.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2011 at 2:18 pm
Thanks for the advice Gail. When I ran a simple SELECT *, it only returned 980 records, stopped due to I:O error, but when repaired actually held ~5200 records. Doing a SELECT COUNT(*) from Table bombs without returning a result.
Would you happen to know how to query this db for the total records it actually contains? (5200) Or would I have to do a guess and check avoiding the corrupt table sections as described above?
October 24, 2011 at 2:27 pm
invulnarable27 (10/24/2011)
Thanks for the advice Gail. When I ran a simple SELECT *, it only returned 980 records, stopped due to I:O error, but when repaired actually held ~5200 records. Doing a SELECT COUNT(*) from Table bombs without returning a result.
Of course. As soon as it hits a corrupt page, you get a severity 24 error and the session is killed. These kinds of errors can't be ignored or stepped over.
Would you happen to know how to query this db for the total records it actually contains? (5200) Or would I have to do a guess and check avoiding the corrupt table sections as described above?
You can try sys.partitions, but there's absolutely no guarantee it's accurate in this situation. Otherwise, as I said above.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2011 at 8:35 pm
Thanks for the info Gail. Just double checking to be completely sure 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply