logical consistency-based I/O error

Question

Post reply

logical consistency-based I/O error

MarvinTheAndriod

SSCarpal Tunnel

Points: 4972
More actions
January 31, 2013 at 3:57 am

#273687

SQL has reported a SQL Server detected a logical consistency-based I/O error: invalid protection option:
Having run a CHECKDB I got the following results:
page (19720:-1112196647). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -10. [SQLSTATE 42000] (Error 8939) Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -7502420219703001088 (type Unknown), page (28985:-1948769500). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -10. [SQLSTATE 42000] (Error 8939) Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -6003390234007371776 (type Unknown), page ID (1:8526519) contains an incorrect page ID in its page header. The PageId in the page header = (13237:-241215502). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -4858899631940370432 (type Unknown), page (15977:2104695145). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -1. [SQLSTATE 42000] (Error 8939) Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -1463068087270309888 (type Unknown), page (63117:570123677). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -10. [SQLSTATE 42000] (Error 8939) Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -387825629700816896 (type Unknown), page (38990:1628431355). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -14. [SQLSTATE 42000] (Error 8939) Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 281685433581568 (type Unknown), page ID (1:8526421) contains an incorrect page ID in its page header. The PageId in the page header = (50:3735602). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 281702613319680 (type Unknown), page ID (1:8526422) contains an incorrect page ID in its page header. The PageId in the page header = (51:3735602). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 281719793057792 (type Unknown), page ID (1:8526420) contains an incorrect page ID in its page header. The PageId in the page header = (49:3735602). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7706705772855885824 (type Unknown), page (11864:-1873285859). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4. [SQLSTATE 42000] (Error 8939) Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 9151979361703231488 (type Unknown), page (4778:1345208676). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -12.
Is the only option to run a REPAIR with ALLOW DATALOSS ? We have backups but this issue may have been occurring for a few weeks. Is there any other options ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please run the following and post the full and complete, unedited output

DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MarvinTheAndriod SSCarpal Tunnel Points: 4972 More actions · Answer 2

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -8578841379021586432 (type Unknown), page (19720:-1112196647). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -10.

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -7502420219703001088 (type Unknown), page (28985:-1948769500). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -10.

Msg 8909, Level 16, State 1, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -6003390234007371776 (type Unknown), page ID (1:8526519) contains an incorrect page ID in its page header. The PageId in the page header = (13237:-241215502).

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -4858899631940370432 (type Unknown), page (15977:2104695145). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -1.

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -1463068087270309888 (type Unknown), page (63117:570123677). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -10.

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -387825629700816896 (type Unknown), page (38990:1628431355). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -14.

Msg 8909, Level 16, State 1, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 281685433581568 (type Unknown), page ID (1:8526421) contains an incorrect page ID in its page header. The PageId in the page header = (50:3735602).

Msg 8909, Level 16, State 1, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 281702613319680 (type Unknown), page ID (1:8526422) contains an incorrect page ID in its page header. The PageId in the page header = (51:3735602).

Msg 8909, Level 16, State 1, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 281719793057792 (type Unknown), page ID (1:8526420) contains an incorrect page ID in its page header. The PageId in the page header = (49:3735602).

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 7706705772855885824 (type Unknown), page (11864:-1873285859). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 9151979361703231488 (type Unknown), page (4778:1345208676). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -12.

CHECKDB found 0 allocation errors and 11 consistency errors not associated with any single object.

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Page (1:8526418) is missing a reference from previous page (1:8526419). Possible chain linkage problem.

Msg 8928, Level 16, State 1, Line 1

Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data): Page (1:8526419) could not be processed. See other errors for details.

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data), page (1:8526419). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -1.

Msg 8980, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Index node page (1:8559657), slot 218 refers to child page (1:8526419) and previous child (1:8526420), but they were not encountered.

Msg 8928, Level 16, State 1, Line 1

Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data): Page (1:8526420) could not be processed. See other errors for details.

Msg 8980, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Index node page (1:8559657), slot 217 refers to child page (1:8526420) and previous child (1:8526421), but they were not encountered.

Msg 8928, Level 16, State 1, Line 1

Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data): Page (1:8526421) could not be processed. See other errors for details.

Msg 8980, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Index node page (1:8559657), slot 216 refers to child page (1:8526421) and previous child (1:8526422), but they were not encountered.

Msg 8928, Level 16, State 1, Line 1

Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data): Page (1:8526422) could not be processed. See other errors for details.

Msg 8976, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Page (1:8526422) was not seen in the scan although its parent (1:8559657) and previous (1:8526423) refer to it. Check any previous errors.

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Page (1:8526494) is missing a reference from previous page (1:8526520). Possible chain linkage problem.

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Page (1:8526495) is missing a reference from previous page (1:8526522). Possible chain linkage problem.

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Page (1:8526517) is missing a reference from previous page (1:8526519). Possible chain linkage problem.

Msg 8928, Level 16, State 1, Line 1

Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data): Page (1:8526518) could not be processed. See other errors for details.

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data), page (1:8526518). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -1.

Msg 8976, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Page (1:8526518) was not seen in the scan although its parent (1:8556960) and previous (1:8526769) refer to it. Check any previous errors.

Msg 8928, Level 16, State 1, Line 1

Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data): Page (1:8526519) could not be processed. See other errors for details.

Msg 8980, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Index node page (1:8556960), slot 230 refers to child page (1:8526519) and previous child (1:8526518), but they were not encountered.

Msg 8928, Level 16, State 1, Line 1

Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data): Page (1:8526520) could not be processed. See other errors for details.

Msg 8980, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Index node page (1:8527223), slot 60 refers to child page (1:8526520) and previous child (1:8526521), but they were not encountered.

Msg 8928, Level 16, State 1, Line 1

Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data): Page (1:8526521) could not be processed. See other errors for details.

Msg 8976, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Page (1:8526521) was not seen in the scan although its parent (1:8527223) and previous (1:8526495) refer to it. Check any previous errors.

Msg 8928, Level 16, State 1, Line 1

Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data): Page (1:8526522) could not be processed. See other errors for details.

Msg 8980, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Index node page (1:8527223), slot 57 refers to child page (1:8526522) and previous child (1:8526523), but they were not encountered.

Msg 8928, Level 16, State 1, Line 1

Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data): Page (1:8526523) could not be processed. See other errors for details.

Msg 8980, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Index node page (1:8527223), slot 56 refers to child page (1:8526523) and previous child (1:8526524), but they were not encountered.

Msg 8928, Level 16, State 1, Line 1

Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data): Page (1:8526524) could not be processed. See other errors for details.

Msg 8980, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Index node page (1:8527223), slot 55 refers to child page (1:8526524) and previous child (1:8526526), but they were not encountered.

Msg 8928, Level 16, State 1, Line 1

Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data): Page (1:8526525) could not be processed. See other errors for details.

Msg 8976, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Page (1:8526525) was not seen in the scan although its parent (1:8527223) and previous (1:8526545) refer to it. Check any previous errors.

Msg 8928, Level 16, State 1, Line 1

Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data): Page (1:8526526) could not be processed. See other errors for details.

Msg 8976, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Page (1:8526526) was not seen in the scan although its parent (1:8527223) and previous (1:8526527) refer to it. Check any previous errors.

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 174623665, index ID 1, partition ID 72057804481036288, alloc unit ID 72057944368611328 (type In-row data). Page (1:8526544) is missing a reference from previous page (1:8526525). Possible chain linkage problem.

CHECKDB found 0 allocation errors and 33 consistency errors in table 'EXP_TRV' (object ID 174623665).

CHECKDB found 0 allocation errors and 44 consistency errors in database 'EXP_1'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (NetPerfMon).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You can restore a clean backup (one from before the corruption occurred), or you can run checkDB with repair allow data loss and lose a whole bunch of data from the table EXP_TRV

I recommend the backup if at all possible.

Once fixed, do some root cause analysis, something caused this corruption, most likely something in the IO subsystem somewhere. Also revise and enhance your backup strategies.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MarvinTheAndriod SSCarpal Tunnel Points: 4972 More actions · Answer 4

The EXP_TRV had a truncation run on it a few days before the error occurred. It was something suggested by our supplier, after The growth of the data in this table was queried. Could this have been the cause ? From the results is it only this table affected, if so is it possible just to run a repair on this table ? If I run a DBCC with data loss, is data loss only going to affect this table ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Nope. Corruption is something outside of SQL mangling the database. Check your IO subsystem

You need to do a full DB repair, there's corruption not associated with an object as well. Should just be the table identified.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass