SQLTeam.com | Weblogs | Forums

How to fix database Consistency errors


#1

I am using SQL Server 2008 R2 and one of my databases has loads of consistency errors. When I try to query a particular record from a table it displays an error message similar to this

Msg 824, Level 24, State 2, Line 4
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x00000000; actual signature: 0x04100410). It occurred during a read of page (1:2052727) in database ID 7 at offset 0x000003ea4ee000 in file 'C:\Program Files\Microsoft SQL\BV4.mdf'.  

When I run DBCC CHECK DB I get the following information

Msg 8939, Level 16, State 98, Line 7
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page (0:285483064). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -1.
Msg 8939, Level 16, State 98, Line 7 partition ID 0, alloc unit ID 0 (type Unknown), page (0:333637984). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -1.
Msg 8939, Level 16, State 98, Line 7
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page (0:347758808). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -1.
Msg 8939, Level 16, State 98, Line 7
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page (0:354328536). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -1.
Msg 8939, Level 16, State 98, Line 7
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page (0:354781272). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -1.
Msg 8909, Level 16, State 1, Line 7
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:1977873) contains an incorrect page ID in its page header. The PageId in the page header = (0:356303736).
CHECKDB found 0 allocation errors and 60 consistency errors not associated with any single object.
Msg 8928, Level 16, State 1, Line 7
Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data): Page (1:9383462) could not be processed.  See other errors for details.
Msg 8976, Level 16, State 1, Line 7
Table error: Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data). Page (1:9383462) was not seen in the scan although its parent (1:9365636) and previous (1:9383461) refer to it. Check any previous errors.
Msg 8928, Level 16, State 1, Line 7
Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data): Page (1:9383463) could not be processed.  See other errors for details.
Msg 8980, Level 16, State 1, Line 7
Table error: Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data). Index node page (1:9365636), slot 284 refers to child page (1:9383463) and previous child (1:9383462), but they were not encountered.
Msg 8928, Level 16, State 1, Line 7
Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data): Page (1:9383464) could not be processed.  See other errors for details.
Msg 8980, Level 16, State 1, Line 7
Table error: Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data). Index node page (1:9365636), slot 285 refers to child page (1:9383464) and previous child (1:9383463), but they were not encountered.
Msg 8928, Level 16, State 1, Line 7
Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data): Page (1:9383465) could not be processed.  See other errors for details.
Msg 8980, Level 16, State 1, Line 7
Table error: Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data). Index node page (1:9365636), slot 286 refers to child page (1:9383465) and previous child (1:9383464), but they were not encountered.
Msg 8928, Level 16, State 1, Line 7
Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data): Page (1:9383466) could not be processed.  See other errors for details.
Msg 8980, Level 16, State 1, Line 7
Table error: Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data). Index node page (1:9365636), slot 287 refers to child page (1:9383466) and previous child (1:9383465), but they were not encountered.
Msg 8928, Level 16, State 1, Line 7
Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data): Page (1:9383467) could not be processed.  See other errors for details.
Msg 8980, Level 16, State 1, Line 7
Table error: Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data). Index node page (1:9365636), slot 288 refers to child page (1:9383467) and previous child (1:9383466), but they were not encountered.
Msg 8928, Level 16, State 1, Line 7
Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data): Page (1:9383468) could not be processed.  See other errors for details.
Msg 8980, Level 16, State 1, Line 7
Table error: Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data). Index node page (1:9365636), slot 289 refers to child page (1:9383468) and previous child (1:9383467), but they were not encountered.
Msg 8928, Level 16, State 1, Line 7
Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data): Page (1:9383469) could not be processed.  See other errors for details.
Msg 8980, Level 16, State 1, Line 7
Table error: Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data). Index node page (1:9365636), slot 290 refers to child page (1:9383469) and previous child (1:9383468), but they were not encountered.
Msg 8928, Level 16, State 1, Line 7
Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data): Page (1:9383470) could not be processed.  See other errors for details.
Msg 8980, Level 16, State 1, Line 7
Table error: Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data). 
Index node page (1:9365636), slot 291 refers to child page (1:9383470) and previous child (1:9383469), but they were not encountered.
Msg 8928, Level 16, State 1, Line 7
Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data): Page (1:9383471) could not be processed.  See other errors for details.
Msg 8980, Level 16, State 1, Line 7
Table error: Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data). Index node page (1:9365636), slot 292 refers to child page (1:9383471) and previous child (1:9383470), but they were not encountered.
Msg 8978, Level 16, State 1, Line 7
Table error: Object ID 158623608, index ID 8, partition ID 72057594349813760, alloc unit ID 72057594951303168 (type In-row data). Page (1:9383472) is missing a reference from previous page (1:9383471). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 21 consistency errors in table 'ContactHistory' (object ID 158623608).
Msg 8928, Level 16, State 1, Line 7
Object ID 206623779, index ID 2, partition ID 72057594350010368, alloc unit ID 72057594951499776 (type In-row data): Page (1:9681196) could not be processed.  See other errors for details.
Msg 8976, Level 16, State 1, Line 7
Table error: Object ID 206623779, index ID 2, partition ID 72057594350010368, alloc unit ID 72057594951499776 (type In-row data). Page (1:9681196) was not seen in the scan although its parent (1:9680832) and previous (1:9681195) refer to it. Check any previous errors.
Msg 8928, Level 16, State 1, Line 7
Object ID 206623779, index ID 2, partition ID 72057594350010368, alloc unit ID 72057594951499776 (type In-row data): Page (1:9681197) could not be processed.  See other errors for details.
Msg 8980, Level 16, State 1, Line 7
Table error: Object ID 206623779, index ID 3, partition ID 72057594350075904, alloc unit ID 72057594951565312 (type In-row data). Page (1:2140777) was not seen in the scan although its parent (1:2543702) and previous (1:8408623) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 7
Table error: Object ID 206623779, index ID 3, partition ID 72057594350075904, alloc unit ID 72057594951565312 (type In-row data). Page (1:8408622) is missing a reference from previous page (1:2140777). Possible chain linkage problem.
Msg 8978, Level 16, State 1, Line 7
Table error: Object ID 206623779, index ID 3, partition ID 72057594350075904, alloc unit ID 72057594951565312 (type In-row data). Page (1:8530728) is missing a reference from previous page (1:2140776). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 18 consistency errors in table 'ContactData' (object ID 206623779).
Msg 8928, Level 16, State 1, Line 7
Object ID 658101385, index ID 1, partition ID 72057594045333504, alloc unit ID 72057594048937984 (type In-row data): Page (1:1975515) could not be processed.  See other errors for details.
Msg 8976, Level 16, State 1, Line 7
Table error: Object ID 658101385, index ID 1, partition ID 72057594045333504, alloc unit ID 72057594048937984 (type In-row data). Page (1:1975515) was not seen in the scan although its parent (1:1980843) and previous (1:1975514) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 7
Object ID 658101385, index ID 1, partition ID 72057594045333504, alloc unit ID 72057594048937984 (type In-row data): Page (1:1981605) could not be processed.  See other errors for details.
Msg 8980, Level 16, State 1, Line 7
Object ID 658101385, index ID 1, partition ID 72057594045333504, alloc unit ID 72057594048937984 (type In-row data): Page (1:2140775) could not be processed.  See other errors for details.
Msg 8980, Level 16, State 1, Line 7
Table error: Object ID 658101385, index ID 1, partition ID 72057594045333504, alloc unit ID 72057594048937984 (type In-row data). Index node page (1:2142374), slot 258 refers to child page (1:2140775) and previous child (1:2140774), but they were not encountered.
Msg 8978, Level 16, State 1, Line 7
Table error: Object ID 658101385, index ID 1, partition ID 72057594045333504, alloc unit ID 72057594048937984 (type In-row data). Page (1:2141016) is missing a reference from previous page (1:2140775). Possible chain linkage problem.
Msg 8978, Level 16, State 1, Line 7
Table error: Object ID 658101385, index ID 2, partition ID 72057594045399040, alloc unit ID 72057594049003520 (type In-row data). Page (1:1180333) is missing a reference from previous page (1:2052728). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Line 7
Object ID 658101385, index ID 2, partition ID 72057594045399040, alloc unit ID 72057594049003520 (type In-row data): Page (1:2052728) could not be processed.  See other errors for details.
Msg 8976, Level 16, State 1, Line 7
Table error: Object ID 658101385, index ID 2, partition ID 72057594045399040, alloc unit ID 72057594049003520 (type In-row data). Page (1:2052728) was not seen in the scan although its parent (1:2625714) and previous (1:604367) refer to it. Check any previous errors.
CHECKDB found 0 allocation errors and 40 consistency errors in table 'CallTransaction_Hist' (object ID 658101385).
Msg 8993, Level 16, State 1, Line 7
Object ID 1549248574, forwarding row page (1:1043918), slot 41 points to page (1:1981608), slot 14. Did not encounter forwarded row. Possible allocation error.
Msg 8993, Level 16, State 1, Line 7
Object ID 1549248574, forwarding row page (1:1753548), slot 41 points to page (1:1981608), slot 13. Did not encounter forwarded row. Possible allocation error.
Msg 8993, Level 16, State 1, Line 7
Object ID 1549248574, forwarding row page (1:1950913), slot 4 points to page (1:1981608), slot 0. Did not encounter forwarded row. Possible allocation error.
Msg 8993, Level 16, State 1, Line 7
Object ID 1549248574, index ID 0, partition ID 72057594055557120, alloc unit ID 72057594060996608 (type In-row data): Page (1:2976311) could not be processed.  See other errors for details.
Msg 8994, Level 16, State 1, Line 7
Object ID 1549248574, forwarded row page (1:2976569), slot 8 should be pointed to by forwarding row page (1:2976310), slot 6. Did not encounter forwarding row. Possible allocation error.
Msg 8994, Level 16, State 1, Line 7
Object ID 1549248574, forwarded row page (1:2976585), slot 24 should be pointed to by forwarding row page (1:2976311), slot 23. Did not encounter forwarding row. Possible allocation error.
CHECKDB found 0 allocation errors and 51 consistency errors in table 'MitelSMDRFields' (object ID 1549248574).
Msg 8928, Level 16, State 1, Line 7
Object ID 2101582525, index ID 0, partition ID 72057594043891712, alloc unit ID 72057594046971904 (type In-row data): Page (1:1977873) could not be processed.  See other errors for details.
Msg 8928, Level 16, State 1, Line 7
Object ID 2101582525, index ID 0, partition ID 72057594043891712, alloc unit ID 72057594046971904 (type In-row data): Page (1:2976380) could not be processed.  See other errors for details.
CHECKDB found 0 allocation errors and 8 consistency errors in table 'CallRecords_Hist' (object ID 2101582525).
Msg 8928, Level 16, State 1, Line 7
Object ID 2114106572, index ID 1, partition ID 72057594349617152, alloc unit ID 72057594951106560 (type In-row data): Page (1:1894807) could not be processed.  See other errors for details.
Msg 8976, Level 16, State 1, Line 7
Table error: Object ID 2114106572, index ID 1, partition ID 72057594349617152, alloc unit ID 72057594951106560 (type In-row data). Page (1:1894807) was not seen in the scan although its parent (1:1926658) and previous (1:1894806) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 7
Table error: Object ID 2114106572, index ID 1, partition ID 72057594349617152, alloc unit ID 72057594951106560 (type In-row data). Page (1:1896440) is missing a reference from previous page (1:1894807). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Line 7
Object ID 2114106572, index ID 1, partition ID 72057594349617152, alloc unit ID 72057594951106560 (type In-row data): Page (1:1981592) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 7
Table error: Object ID 2114106572, index ID 1, partition ID 72057594349617152, alloc unit ID 72057594951106560 (type In-row data), page (1:1981592). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8976, Level 16, State 1, Line 7
Table error: Object ID 2114106572, index ID 1, partition ID 72057594349617152, alloc unit ID 72057594951106560 (type In-row data). Page (1:1981592) was not seen in the scan although its parent (1:1747297) and previous (1:1979391) refer to it. Check any previous errors.
Msg 8928, Level 16, State 1, Line 7
Object ID 2114106572, index ID 1, partition ID 72057594349617152, alloc unit ID 72057594951106560 (type In-row data): Page (1:1981593) could not be processed.  See other errors for details.
Msg 8980, Level 16, State 1, Line 7
Table error: Object ID 2114106572, index ID 1, partition ID 72057594349617152, alloc unit ID 72057594951106560 (type In-row data). Index node page (1:1747297), slot 45 refers to child page (1:1981593) and previous child (1:1981592), but they were not encountered.
Msg 8928, Level 16, State 1, Line 7
Object ID 2114106572, index ID 1, partition ID 72057594349617152, alloc unit ID 72057594951106560 (type In-row data): Page (1:1981594) could not be processed.  See other errors for details.
Msg 8980, Level 16, State 1, Line 7
Table error: Object ID 2114106572, index ID 1, partition ID 72057594349617152, alloc unit ID 72057594951106560 (type In-row data). Index node page (1:1747297), slot 46 refers to child page (1:1981594) and previous child (1:1981593), but they were not encountered.
Msg 8928, Level 16, State 1, Line 7
Object ID 2114106572, index ID 1, partition ID 72057594349617152, alloc unit ID 72057594951106560 (type In-row data): Page (1:1981595) could not be processed.  See other errors for details.
Msg 8980, Level 16, State 1, Line 7
Table error: Object ID 2114106572, index ID 1, partition ID 72057594349617152, alloc unit ID 72057594951106560 (type In-row data). Index node page (1:1747297), slot 47 refers to child page (1:1981595) and previous child (1:1981594), but they were not encountered.
Msg 8928, Level 16, State 1, Line 7
Object ID 2114106572, index ID 1, partition ID 72057594349617152, alloc unit ID 72057594951106560 (type In-row data): Page (1:1981596) could not be processed.  See other errors for details.
Msg 8980, Level 16, State 1, Line 7
Table error: Object ID 2114106572, index ID 1, partition ID 72057594349617152, alloc unit ID 72057594951106560 (type In-row data). Index node page (1:1747297), slot 48 refers to child page (1:1981596) and previous child (1:1981595), but they were not encountered.
Msg 8978, Level 16, State 1, Line 7
Table error: Object ID 2114106572, index ID 4, partition ID 72057594350600192, alloc unit ID 72057594952155136 (type In-row data). Index node page (1:1742162), slot 166 refers to child page (1:1977863) and previous child (1:1977862), but they were not encountered.
Msg 8978, Level 16, State 1, Line 7
Table error: Object ID 2114106572, index ID 4, partition ID 72057594350600192, alloc unit ID 72057594952155136 (type In-row data). Page (1:1986864) is missing a reference from previous page (1:1977863). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 32 consistency errors in table 'AgentHistory' (object ID 2114106572).
Msg 8928, Level 16, State 1, Line 7
Object ID 2129442660, index ID 0, partition ID 72057594095730688, alloc unit ID 72057594148421632 (type In-row data): Page (1:1894506) could not be processed.  See other errors for details.
Msg 8993, Level 16, State 1, Line 7
Object ID 2129442660, forwarding row page (1:2142034), slot 62 points to page (1:2142059), slot 36. Did not encounter forwarded row. Possible allocation error.
Msg 8928, Level 16, State 1, Line 7
Object ID 2129442660, index ID 0, partition ID 72057594095730688, alloc unit ID 72057594148421632 (type In-row data): Page (1:2142055) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 7
Table error: Object ID 2129442660, index ID 0, partition ID 72057594095730688, alloc unit ID 72057594148421632 (type In-row data), page (1:2142055). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8928, Level 16, State 1, Line 7
CHECKDB found 0 allocation errors and 9 consistency errors in table 'Contact_Map' (object ID 2129442660).
CHECKDB found 0 allocation errors and 239 consistency errors in database 'DBV4'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DBV4).

Could you please help me figure out the solution as I don't want to use repair_allow_data_loss as the data is very critical


#2

The best option is to restore from a known good backup - and apply all transaction log backups from that point forward to current point in time.

You also need to insure that the page verify option on all of your databases has been changed to CHECKSUM and you want to enabled CHECKSUM on your backups as well. This won't help the current situation but it will help identify corruption issues better.

And finally - you need to fix the IO problems with the server and/or storage as that is the cause of the problems.


#3

This error generally indicates Corruption's in Database and occur when someone runs the damaged pages. Queries fails with high severity errors. In your case messages indicates corruption within a database is due to the corruption of clustered index's leaf pages or to the heap, it means that data has been lost.
Most, Probably you may try to recover your database from the recent healthy backup available. If no, recent backup available. The best you may try (link removed) to repair corrupt sql server.


#4

If not too late: Take a (Tail) Log Backup before you start the restore ... assuming that your database is in Full Recovery Model.


#5

Dear Jason,

I had the same error, and I downloaded your software. It was unable to fix this my issue. I had bad luck because the backup copy was not updated. I also go through one more SQL recovery tool and tried its demo version from here: (link removed)

I scanned my file and showed the preview of my records in the demo version. It worked for me.

By this incident, I learnt that the backup is the best way to get back the data. In this case, it's all my luck that the software worked for me. I will suggest to all DBA to maintain their disaster recovery plan.


#6

I could NOT recommend Stellar Data Recovery! I had a HORRIBLE experience with this product - more specifically, the not-so stellar support and I could not recommend this product.

I used their SQL Database recover product to try to repair a 166 GB MS SQL Database. It scanned for a long time, counting up millions of records, and ending with "Selected MS SQL database repaired successfully. Click "OK" to register your copy and save repaired database" It was a convincing prompt.. I could see tables in the navigation pane. After having purchased the software to save the "successfully repaired database", it was an unusable 33 MB database. I contacted support and they had me jump through a variety of hoops which was unsuccessful. They requested remote access to assist in the recovery but still no success. I requested a refund and support directed me to their refund department. Having explained everything, they denied the refund and asked that I contact support for assistance, which I had already explained I had and they sent me to the refund department. Conceding, I did a second time, with the same results.. And round-and-round we went. I soon figured out that this is simply a canned email response, as I've seen this same email many times in pursuit of a refund:

"Thank you for letting us know about your recent experience with Stellar Data Recovery.. We apologize if our services did not meet your expectations and appreciate you for taking time and sharing your valuable feedback.

Our goal is to get our client's valuable data back and, provide a consistently reliable & an exemplary level of customer service.

Therefore, we request to kindly co-operate with us and take our technical support assistance so that, they can help in resolving your issue.
Your cooperation will be appreciated."

Frustrated, I tried a competitors product. It too scanned for a long time, but in the end, it was forthcoming that it was unable to repair the database successfully, and didn't try to seduce me into buying their product. I suspect that Stellar's scan will ALWAYS say "Selected MS SQL database repaired successfully. Click "OK" to register your copy and save repaired database" - DON'T BELIEVE IT! Be sure to interrogate the log. If the recovery is successful, good for you; if not, be warned that there is nothing you can do or say to get your money back. In short, forget about any satisfaction guarantee or their refund policy.


#7

There are some resolutions that discussed below to help users to fix SQL database consistency errors.

Windows System Event Log

Windows System Event Log errors indicate possible I/O problems that can be associated with inconsistency of database. It contains various Event IDs depend upon the inaccessibility of data. Every event ID has different resolution to fix the occurrence of an error.

Integrity of File System

Many times, the users face the inconsistency error due to the file system integration. To check the integrity of file system, user can use the chdsk command. This command helps to create and displays a status report for disk. It also lists all the errors on the disks. It is available with different parameters from the command prompt.