Consistancy Error - 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

Consistancy Error

Author

Topic

JasonG
Starting Member

3 Posts

Posted - 2005-10-24 : 14:09:08

I ran into a few consistency problems this morning that I found after receiving a torn page error on our SQL 2000 box (SP4). Here’s the error messages I get from running DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS :

Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1979154096: Errors found in text ID 379493089280 owned by data record identified by RID = (1:647611:14) PK1 = 1300583.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1979154096: Errors found in text ID 379544338432 owned by data record identified by RID = (1:648136:32) PK1 = 1300903.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1979154096: Errors found in text ID 379567996928 owned by data record identified by RID = (1:648142:19) PK1 = 1301226.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1979154096: Errors found in text ID 379587067904 owned by data record identified by RID = (1:648474:43) PK1 = 1301482.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1979154096: Errors found in text ID 552407334912 owned by data record identified by RID = (1:1387313:46) PK1 = 3277593.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1979154096: Errors found in text ID 552407400448 owned by data record identified by RID = (1:1387313:47) PK1 = 3277594.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1979154096: Errors found in text ID 552407465984 owned by data record identified by RID = (1:1387313:48) PK1 = 3277595.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1979154096: Errors found in text ID 552407531520 owned by data record identified by RID = (1:1387313:49) PK1 = 3277596.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1979154096: Errors found in text ID 552407597056 owned by data record identified by RID = (1:1387313:50) PK1 = 3277597.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1979154096: Errors found in text ID 552407662592 owned by data record identified by RID = (1:1387313:51) PK1 = 3277598.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1979154096: Errors found in text ID 552407728128 owned by data record identified by RID = (1:1387313:52) PK1 = 3277599.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1979154096: Errors found in text ID 552407793664 owned by data record identified by RID = (1:1387313:53) PK1 = 3277600.
Server: Msg 8929, Level 16, State 1, Line 1
Object ID 1979154096: Errors found in text ID 552407859200 owned by data record identified by RID = (1:1387313:54) PK1 = 3277601.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:558591), slot 8, text ID 552407334912 is not referenced.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1979154096, index ID 255: Page (1:558593) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 1979154096, index ID 255, page (1:558593). 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 1979154096. The text, ntext, or image node at page (1:558593), slot 0, text ID 552407334912 is referenced by page (1:1387313), slot 46, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:558593), slot 2, text ID 552407400448 is referenced by page (1:1387313), slot 47, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:558593), slot 4, text ID 552407465984 is referenced by page (1:1387313), slot 48, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:558593), slot 6, text ID 552407531520 is referenced by page (1:1387313), slot 49, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:558593), slot 8, text ID 552407597056 is referenced by page (1:1387313), slot 50, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:558593), slot 9, text ID 379493089280 is referenced by page (1:647611), slot 14, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:558593), slot 10, text ID 379544338432 is referenced by page (1:648136), slot 32, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:558593), slot 11, text ID 379567996928 is referenced by page (1:648142), slot 19, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:558593), slot 12, text ID 379587067904 is referenced by page (1:648474), slot 43, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:558593), slot 14, text ID 552407662592 is referenced by page (1:1387313), slot 51, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:558593), slot 16, text ID 552407728128 is referenced by page (1:1387313), slot 52, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:558593), slot 18, text ID 552407793664 is referenced by page (1:1387313), slot 53, but was not seen in the scan.
Server: Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:558593), slot 20, text ID 552407859200 is referenced by page (1:1387313), slot 54, but was not seen in the scan.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:647791), slot 6, text ID 379493089280 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:648120), slot 7, text ID 379544338432 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:648356), slot 6, text ID 379567996928 is not referenced.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1979154096. The text, ntext, or image node at page (1:648497), slot 7, text ID 379587067904 is not referenced.
CHECKDB found 0 allocation errors and 33 consistency errors in table 'QTI_RESULT_DATA' (object ID 1979154096).
CHECKDB found 0 allocation errors and 33 consistency errors in database 'bb_bb60'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (bb_bb60 ).

After looking through similar posts here in the forum, I also ran a checktable on the sysindexes, sysobjects, syscolumns, and systypes tables, since they seem to be popular sources of errors. However, no error messages were reported. They seem to be all related to the one table referenced by the CHECKDB statement.

Is this something that is repairable, or will it require a restore? Thanks for the help!

Jason

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-24 : 14:25:02

The torn page is a TEXT_MIX_PAGE that is shared by various text blobs, thus the multiple errors you see for different pages and slots. Repair will delete that page, and all the text blobs referencing it, so if you have an up-to-date backup, you should restore from it to avoid data loss.

A nice simple one for a change

Thanks

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

JasonG
Starting Member

3 Posts

Posted - 2005-10-24 : 14:45:25

Paul, thanks for the ultra-quick response!

If we decide to repair, and accept the data loss, rather than restore, should (given what you've seen) a simple DBCC CHECKDB ('DBName', REPAIR_ALLOW_DATA_LOSS) statement fix the problem?

Thanks!
Jason

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-10-24 : 15:31:04

No problem.

Yes, that will do it. I suggest wrapping it in an explicit transaction so you can see how much data it deletes before actually committing the repair. You can rollback the tran if you don't like it.

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

JasonG
Starting Member

3 Posts

Posted - 2005-10-24 : 15:36:04

Will do. Thanks again for the help!

Jason

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources