DBCC issue on Index - 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

SQL Server 2012 Forums

SQL Server Administration (2012)

DBCC issue on Index

Author

Topic

CFleming
Starting Member

2 Posts

Posted - 2013-09-23 : 11:35:33

Hi all - I am having a consistency error on an index within 1 table. The DBCC output is below. I am thinking I can drop and re-create this index and be just fine.

- Would you agree?
- Could an error like this be caused by the definition of the index? Or would this point to more of a hardware issue>?

Msg 8944, Level 16, State 13, Line 1
Table error: Object ID 1773249372, index ID 5, partition ID 72057598131306496, alloc unit ID 72057598203068416 (type In-row data), page (1:1754958), row 27. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 4378 and 52.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1773249372, index ID 5, partition ID 72057598131306496, alloc unit ID 72057598203068416 (type In-row data). Page (1:1754947) is missing a reference from previous page (1:1754958). Possible chain linkage problem.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1773249372, index ID 5, partition ID 72057598131306496, alloc unit ID 72057598203068416 (type In-row data). Page (1:1754958) was not seen in the scan although its parent (1:1753240) and previous (1:1754946) refer to it. Check any previous errors.
Msg 8944, Level 16, State 13, Line 1
Table error: Object ID 1773249372, index ID 5, partition ID 72057598131306496, alloc unit ID 72057598203068416 (type In-row data), page (1:1754958), row 27. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 4378 and 52.
Msg 8928, Level 16, State 1, Line 1
Object ID 1773249372, index ID 5, partition ID 72057598131306496, alloc unit ID 72057598203068416 (type In-row data): Page (1:1754958) could not be processed. See other errors for details.
DBCC results for 'datTableNameHere'.
There are 311521 rows in 7500 pages for object "datTableNameHere".

Thanks for your time.

CFleming

TheSQLGuru
SQL Server MVP

10 Posts

Posted - 2013-09-23 : 12:31:47

First thing is to include the output down at the bottom of the DBCC checkdb output about the minimum level of recovery required.

Second is to validate EVERY piece of hardware between your CPUs and the bits out in the IO subsystem. ALL drivers and firmware associated with every piece should be up to date. IO driver/firmware issues are by far the most common cause of corruptions. Next is actual hardware issues.

Third is to make sure you have a backup available just in case.

Fourth is to thank your lucky stars it is just ONE NC index involved. :-)

I am doing a bit more investigation, but I think you may be able to drop the NC index and get out from this. How big is the database and how long does checkdb (or checktable on this table) take to run?

Kevin G Boles
TheSQLGuru
Indicium Resources, Inc.

TheSQLGuru
SQL Server MVP

10 Posts

Posted - 2013-09-23 : 13:33:41

Part of the problem here I think is a cross-linked page. Was this from a full DBCC CHECKDB run? Did you use WITH ALL_ERRORMSGS, NO_INFOMSGS? I wonder if the page really belongs to another object.

Given this is a corruption issue, you probably should be contacting Microsoft support.

I would drop the nonclustered index and run DBCC CHECKDB WITH ALL_ERRORMSGS, NO_INFOMSGS. If clean I would recreate the nonclustered index and thank my lucky stars. I would also be sure to do the things I mentioned previously. But I won't TELL you to do that because I don't want the responsibility for what happens if you were to do those things. :-)

Good luck in any case!

Kevin G Boles
TheSQLGuru
Indicium Resources, Inc.

CFleming
Starting Member

2 Posts

Posted - 2013-09-23 : 13:59:23

Thanks for your help. Here is the bottom of the DBCC.

CHECKDB found 0 allocation errors and 5 consistency errors in database 'DATABASENAMEHERE'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DATABASENAMEHERE).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I will look into contacting Microsoft for help here.

CFleming

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources