Analyzing index errors

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.

Sample of output with inconsistent index

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                                 | 
                              |                                  | 
==============================|==================================|=====

DBCC index errors

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.

Table 13-4: DBCC index errors

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.