Use sp_iqcheckdb to analyze index inconsistencies.
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 | | | =========================|==================================|=====
The DBCC output contains messages related to problems with indexes.
| 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. |