consistency errors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

consistency errors

Author

Topic

rb1373
Yak Posting Veteran

93 Posts

Posted - 2006-01-12 : 03:30:16

I need some help with some consistency errors.

Today I recieved this alert from the SQL Server:

Error: 823, Severity: 24, State: 2
I/O error (bad page ID) detected during read at offset 0x00002245b90000 in file 'D:\MSSQL\Data\Portal.mdf'.

Running dbcc checkdb with ALL_ERRORMSGS, NO_INFOMSGS yield the following:

Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 35900555264 owned by data record identified by RID = (1:10804409:35) add_user = 'AMiller' and correspond_id = 53750.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 343740186624 owned by data record identified by RID = (1:10804652:14) add_user = 'AShallah' and correspond_id = 1457578.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 293307285504 owned by data record identified by RID = (1:10806770:23) add_user = 'CCruzon' and correspond_id = 1341479.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 335802204160 owned by data record identified by RID = (1:10807332:20) add_user = 'CLopez' and correspond_id = 1439003.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 335808167936 owned by data record identified by RID = (1:10807332:21) add_user = 'CLopez' and correspond_id = 1439019.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 249395871744 owned by data record identified by RID = (1:10807530:25) add_user = 'CrJohnson' and correspond_id = 1210177.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 335808233472 owned by data record identified by RID = (1:10808745:16) add_user = 'DKlett' and correspond_id = 1439020.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 335810330624 owned by data record identified by RID = (1:10808745:17) add_user = 'DKlett' and correspond_id = 1439026.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 335810658304 owned by data record identified by RID = (1:10808745:18) add_user = 'DKlett' and correspond_id = 1439028.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 335812231168 owned by data record identified by RID = (1:10809251:13) add_user = 'DNealy' and correspond_id = 1439030.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 335816359936 owned by data record identified by RID = (1:10809251:14) add_user = 'DNealy' and correspond_id = 1439041.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 342354165760 owned by data record identified by RID = (1:10816733:32) add_user = 'Jhawkins' and correspond_id = 1452702.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 342359015424 owned by data record identified by RID = (1:10818038:3) add_user = 'JTheis' and correspond_id = 1452722.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 333403914240 owned by data record identified by RID = (1:10818166:12) add_user = 'JThongdy' and correspond_id = 1431000.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 301036273664 owned by data record identified by RID = (1:10819716:19) add_user = 'KHaines' and correspond_id = 1359551.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 218653261824 owned by data record identified by RID = (1:10822560:19) add_user = 'MPena' and correspond_id = 1163647.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 342353838080 owned by data record identified by RID = (1:10822879:0) add_user = 'MWiley' and correspond_id = 1452698.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 342353969152 owned by data record identified by RID = (1:10822879:1) add_user = 'MWiley' and correspond_id = 1452699.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 542624976: Errors found in text ID 333405749248 owned by data record identified by RID = (1:10832505:23) add_user = 'PGutierrez' and correspond_id = 1431011.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 542624976, index ID 255: Page (1:885006) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 542624976, index ID 255, page (1:885006). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 0, text ID 35900555264 is referenced by page (1:885004), slot 0, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 1, text ID 333403914240 is referenced by page (1:10818166), slot 12, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 2, text ID 333405749248 is referenced by page (1:10832505), slot 23, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 3, text ID 335802204160 is referenced by page (1:10807332), slot 20, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 4, text ID 335808167936 is referenced by page (1:10807332), slot 21, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 5, text ID 335808233472 is referenced by page (1:10808745), slot 16, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 6, text ID 335810330624 is referenced by page (1:10808745), slot 17, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 7, text ID 335810658304 is referenced by page (1:10808745), slot 18, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 8, text ID 335812231168 is referenced by page (1:10809251), slot 13, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 9, text ID 335816359936 is referenced by page (1:10809251), slot 14, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 10, text ID 342353838080 is referenced by page (1:17300344), slot 0, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 11, text ID 342353838080 is referenced by page (1:10822879), slot 0, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 12, text ID 342353969152 is referenced by page (1:10822879), slot 1, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 13, text ID 342354165760 is referenced by page (1:10816733), slot 32, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 14, text ID 342359015424 is referenced by page (1:10818038), slot 3, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 15, text ID 343740186624 is referenced by page (1:19351761), slot 0, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:885006), slot 16, text ID 343740186624 is referenced by page (1:10804652), slot 14, but was not seen in the scan.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 542624976, index ID 255: Page (1:8398158) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 542624976, index ID 255, page (1:8398158), row 0. Test (offsetNull >= BASEOFFSET) failed. Values are 0 and 4.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:8398158), slot 0, text ID 301036273664 is referenced by page (1:8398150), slot 0, but was not seen in the scan.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 542624976, index ID 255: Page (1:9154510) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 542624976, index ID 255, page (1:9154510), row 0. Test (!(hdr->r_tagA & (VERSION_MASK | RECTAG_RESV_A | RECTAG_RESV_B))) failed. Values are 255 and 193.
Server: Msg 8944, Level 16, State 4, Line 1
Table error: Object ID 542624976, index ID 255, page (1:9154510), row 0. Test (GetRecType (pRec) >= 0 && GetRecType (pRec) <= LAST_REC_TYPE) failed. Values are 14 and 12.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 542624976, index ID 255, page (1:9154510), row 0. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 65535 and 7838.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:9154510), slot 0, text ID 218653261824 is referenced by page (1:9154493), slot 0, but was not seen in the scan.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 542624976, index ID 255: Page (1:10444815) could not be processed. See other errors for details.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:10444815), slot 0, text ID 249395871744 is referenced by page (1:10444775), slot 0, but was not seen in the scan.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 542624976, index ID 255, page ID (1:15257220). The PageId in the page header = (1:15257228).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 542624976, index ID 255: Page (1:15257220) could not be processed. See other errors for details.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:15257220), slot 0, text ID 293307285504 is referenced by page (1:15089406), slot 0, but was not seen in the scan.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 542624976, index ID 255, page ID (1:15257221). The PageId in the page header = (1:15257229).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 542624976, index ID 255: Page (1:15257221) could not be processed. See other errors for details.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:15257221), slot 0, text ID 293307285504 is referenced by page (1:15089406), slot 0, but was not seen in the scan.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 542624976, index ID 255, page ID (1:15257222). The PageId in the page header = (1:15257230).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 542624976, index ID 255: Page (1:15257222) could not be processed. See other errors for details.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:15257222), slot 0, text ID 293307285504 is referenced by page (1:15089406), slot 0, but was not seen in the scan.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 542624976, index ID 255, page ID (1:15257223). The PageId in the page header = (1:15257231).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 542624976, index ID 255: Page (1:15257223) could not be processed. See other errors for details.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:15257223), slot 0, text ID 293307285504 is referenced by page (1:15089406), slot 0, but was not seen in the scan.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:17030749), slot 0, text ID 335802204160 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:17030760), slot 0, text ID 335808167936 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:17053063), slot 0, text ID 335808233472 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:17053071), slot 0, text ID 335810330624 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:17053079), slot 0, text ID 335810658304 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:17064698), slot 0, text ID 335812231168 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:17064707), slot 0, text ID 335816359936 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:17294071), slot 0, text ID 342359015424 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:17295780), slot 0, text ID 342354165760 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:17300344), slot 0, text ID 342353838080 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:17300352), slot 0, text ID 342353969152 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:19351761), slot 0, text ID 343740186624 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:19675671), slot 0, text ID 333403914240 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 542624976. The text, ntext, or image node at page (1:19676212), slot 0, text ID 333405749248 is not referenced.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 552493147: Errors found in text ID 263269974016 owned by data record identified by RID = (1:9422783:33).
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 552493147: Errors found in text ID 263272202240 owned by data record identified by RID = (1:9422783:34).
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 552493147: Errors found in text ID 263278231552 owned by data record identified by RID = (1:9422783:35).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 552493147, index ID 255: Page (1:11912175) could not be processed. See other errors for details.
Server: Msg 8941, Level 16, State 1, Line 1
Table error: Object ID 552493147, index ID 255, page (1:11912175). Test (sorted [i].offset <= m_freeData) failed. Slot 1, offset 0xff00 is invalid.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 552493147. The text, ntext, or image node at page (1:11912175), slot 0, text ID 263269974016 is referenced by page (1:11912174), slot 5, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 552493147. The text, ntext, or image node at page (1:11912175), slot 1, text ID 263272202240 is referenced by page (1:11912174), slot 6, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 552493147. The text, ntext, or image node at page (1:11912175), slot 2, text ID 263278231552 is referenced by page (1:11912174), slot 7, but was not seen in the scan.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 942626401, index ID 10: Page (1:21868423) could not be processed. See other errors for details.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 942626401, index ID 10. Page (1:21868423) was not seen in the scan although its parent (1:21864202) and previous (1:21868422) refer to it. Check any previous errors.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 942626401, index ID 10, page (1:21868423), row 368. Test (((UNALIGNED DataRecHdr*) m_pRec)->r_tagB == 0) failed. Values are 21 and 0.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 942626401, index ID 10. Page (1:21870760) is missing a reference from previous page (1:21868423). Possible chain linkage problem.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1310679767, index ID 0, page ID (1:17968584). The PageId in the page header = (1:17968528).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1310679767, index ID 0: Page (1:17968584) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1310679767, index ID 0, page ID (1:17968585). The PageId in the page header = (1:17968529).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1310679767, index ID 0: Page (1:17968585) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1310679767, index ID 0, page ID (1:17968586). The PageId in the page header = (1:17968530).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1310679767, index ID 0: Page (1:17968586) could not be processed. See other errors for details.
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1310679767, index ID 0, page ID (1:17968587). The PageId in the page header = (1:17968531).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1310679767, index ID 0: Page (1:17968587) could not be processed. See other errors for details.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1310679767, index ID 1. Page (1:17968584) was not seen in the scan although its parent (1:17955256) and previous (1:17967263) refer to it. Check any previous errors.
Server: Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 1310679767, index ID 1. Index node page (1:17955256), slot 225 refers to child page (1:17968585) and previous child (1:17968584), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 1310679767, index ID 1. Index node page (1:17955256), slot 226 refers to child page (1:17968586) and previous child (1:17968585), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 1310679767, index ID 1. Index node page (1:17955256), slot 227 refers to child page (1:17968587) and previous child (1:17968586), but they were not encountered.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1310679767, index ID 1. Page (1:17968588) is missing a reference from previous page (1:17968587). Possible chain linkage problem.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1546018973, index ID 255, page (1:10444815), row 0. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 7709 and 2220.
CHECKDB found 0 allocation errors and 74 consistency errors in table 'correspond' (object ID 542624976).
CHECKDB found 0 allocation errors and 8 consistency errors in table 'raaaeport_run' (object ID 552493147).
CHECKDB found 0 allocation errors and 4 consistency errors in table 'coverage' (object ID 942626401).
CHECKDB found 0 allocation errors and 13 consistency errors in table 'payment_detail' (object ID 1310679767).
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID 1546018973)' (object ID 1546018973).
CHECKDB found 0 allocation errors and 100 consistency errors in database 'Portal'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Portal ).

This Portal database is 180GB with 75% of that being blob data.

The only new software change to the SQL server is the addition of Redgate SQL Backup 2 weeks ago. No hardware has been added or modified. However, the server was bounced this past Sunday when it became unresponsive.

When I ran DBCC checkdb a few hours ago, there were only 44 consistency errors. Now that there are 100 is troubling.

Any ideas on how to resolve these consistency errors?

Thanks,
Ray

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)

May 3 2005 23:18:38

Copyright (c) 1988-2003 Microsoft Corporation

Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2006-01-12 : 08:51:12

Check option like

REPAIR_ALLOW_DATA_LOSS
REPAIR_FAST
REPAIR_REBUILD

With DBCC CHECKDB. For more information you can check Books online.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-01-12 : 13:36:15

Bad advice. Running repair is the worst thing you can do for these. Sounds like when you bounced SQL Server you caused a bunch of torn pages - how did you bounce it? Power-cycle the machine?

Repair will delete every row mentioned in the CHECKDB output along with the torn pages themselves (which seems to be all text pages) - you can tell this from the explanations of these errors in the updated versions of BOL since 2004. Your alternative here is to restore from your backups without loss of the corrupt data but possibly data loss depending on when your last log backup was.

Thanks

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-01-15 : 00:58:55

I would also get the details on how this server was bounced. Did you just turn the server off? Is the data file on an external enclosure or SAN, or on internal disks? If external, is there a write cache enabled? Was the disk enclosure also powered off? Were there any errors (or are there any currently) in the event logs or SQL Server logs?

Did any dumps get produced from the OS or SQL Server during this timeframe?

Biggest question....got backups???

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources