This section describes how to analyze index inconsistencies using sp_iqcheckdb, shows the DBCC output when index problems are detected, and describes the DBCC errors related to index problems.
The following is an example of the type of output you see when you run sp_iqcheckdb and there is index inconsistency. DBCC displays both a summary and details about the indexes checked. The Index Summary section at the top of the report indicates if any inconsistent indexes were found. The names of the inconsistent indexes and the type(s) of problems can be found in the index statistics section. The lines with asterisks (*****) contain information about inconsistent indexes.
Extra, missing, or duplicate RID errors are the most common types of errors reported. These errors are an indication that the index is misrepresentative of the data and may give incorrect results or cause other failures. These errors are generally accompanied by other errors indicating the specifics of the inconsistencies.
In this example, DBCC reports an inconsistent HNG index. Because the corresponding FP index checks are good, the FP index can be used with sp_iqrebuildindex to repair the damaged HNG index.
The command line executed for this example is sp_iqcheckdb
'verify database'.
Note that DBCC produces a detailed report, but some lines of the
output have been removed in this example.
Stat Value Flags ==============================|==================================|===== DBCC Verify Mode Report | | ==============================|==================================|===== ** DBCC Status |Errors Detected |***** DBCC Work units Dispatched |75 | DBCC Work units Completed |75 | ==============================|==================================|===== Index Summary | | ==============================|==================================|===== ** Inconsistent Index Count |1 |***** Verified Index Count |85 | ==============================|==================================|===== Index Statistics | | ==============================|==================================|===== ** Inconsistent Index |contact.DBA.idx01_HNG |***** ... Verified Index |fin_data.DBA.ASIQ_IDX_T209_C3_HG | Verified Index |fin_data.DBA.ASIQ_IDX_T209_C4_FP | ... Verified Index |employee.DBA.ASIQ_IDX_T212_C19_FP | Verified Index |employee.DBA.ASIQ_IDX_T212_C20_FP | Verified Index |iq_dummy.DBA.ASIQ_IDX_T213_C1_FP | ** Extra Index RIDs |5 |***** FP Indexes Checked |68 | HNG Indexes Checked |1 | HG Indexes Checked |17 | | |
The inconsistent index detected by sp_iqcheckdb is contact.DBA.idx01_HNG.
The following DBCC output is generated when sp_iqcheckdb is
run again to check just the inconsistent index. The command line
executed for this example is sp_iqcheckdb
'verify index DBA.contact.idx01_HNG'.
Stat Value Flags ==============================|==================================|===== DBCC Verify Mode Report | | ==============================|==================================|===== ** DBCC Status |Errors Detected |***** DBCC Work units Dispatched |1 | DBCC Work units Completed |1 | | | ==============================|==================================|===== Index Summary | | ==============================|==================================|===== ** Inconsistent Index Count |1 |***** Verified Index Count |1 | | | ==============================|==================================|===== Index Statistics | | ==============================|==================================|===== ** Inconsistent Index |contact.DBA.idx01_HNG |***** Verified Index |contact.DBA.ASIQ_IDX_T206_C1_FP | ** Extra Index RIDs |5 |***** FP Indexes Checked |1 | HNG Indexes Checked |1 | | | ==============================|==================================|=====
Messages in the DBCC output related to problems with indexes are listed in the following table. See the section “DBCC error messages” for a more extensive list of DBCC messages.
DBCC message |
Description/action |
|---|---|
Inconsistent Index Count |
The number of indexes that DBCC found to have inconsistencies. |
Inconsistent Index |
The name of an index that DBCC found to be inconsistent. |
Extra Index RIDs Missing Index RIDs Duplicate Index RIDs |
The total number of rows that are inconsistent for all inconsistent indexes. |
Bitmap Verify Errors |
The total number of inconsistent bitmaps in all database objects |
FP Lookup Table Inconsistencies |
An unrepairable error, where the 1-byte or 2-byte FP is internally inconsistent. |
Non-Completed Index Count |
The number of indexes that could not be verified, because an exception occurred while checking. |
Non-Completed Index |
The name of an index that was not verified because an exception occurred while checking. If the exception is a future version, out of memory, or out of buffers error, commit the DBCC connection and re-run DBCC. |
VDO Incorrect First Available Fields VDO Incorrect Next Available Fields VDO Incorrect Used Count Fields VDO Incorrect In-use Bitvec VDO Incorrect In-use Bitmap VDO Incorrect Partial Bitmap VDO Incorrect Deleted Bitmaps |
Unrepairable errors that can cause entire tables to be inaccessible. You must force drop the inconsistent table to resolve these errors. |
HG Missing Groups HG Extra Groups HG Extra Keys HG Missing Keys B-Tree Invalid Item Count B-Tree Invalid Item Count G-Array Empty Page Errors G-Array Bad Group Type Errors G-Array Out of Order Group Errors |
High Group index specific errors. |
