In repair mode, sp_iqcheckdb performs detailed index checks and can repair many types of index inconsistencies. The DBCC output indicates which indexes, if any, were repaired. After DBCC repairs an index, you should run sp_iqcheckdb again in check mode before committing any changes. If an index is still inconsistent, drop and recreate the index as described in the section “Dropping inconsistent indexes, tables, or columns”, and then rebuild the index.
Repairing index problems using DBCC
Run sp_iqcheckdb in repair mode to verify and repair the specified targets. For example, to check and repair the indexes on the table t1:
sp_iqcheckdb 'repair table t1';
Run the stored procedure sp_iqcheckdb again in verify mode. For example, to verify the indexes on table t1 again:
sp_iqcheckdb 'verify table t1';
When sp_iqcheckdb completes, review the report for the repairs made to the database. You must issue a COMMIT command in order to commit the changes to the database. Commit the changes only if the second sp_iqcheckdb reports no errors. If an index is still inconsistent, drop and recreate the index.
DBCC displays an Index Summary section at the top of the report, which lists the number of repaired and verified indexes. The Index Statistics section provides details about the indexes repaired. Lines containing information about repaired indexes are flagged with asterisks (*****). See the section “DBCC index errors” for more information on DBCC output messages related to indexes.
In this example, DBCC repaired the inconsistent HNG index.
The command line executed for this example is sp_iqcheckdb
'repair database'.
DBCC repair mode does verify all indexes, but does not
check allocation or report allocation statistics. Some lines of
the output have been removed in this example.
Stat Value Flags ==============================|===================================|===== DBCC Repair Mode Report | | ==============================|===================================|===== ** DBCC Status |Errors Detected | DBCC Work units Dispatched |75 | DBCC Work units Completed |75 | ==============================|===================================|===== Index Summary | | ==============================|===================================|===== ** Repaired Index Count |1 |***** Verified Index Count |85 | ==============================|===================================|===== Index Statistics | | ==============================|===================================|===== ** Repaired Index |contact.DBA.idx01_HNG |***** ... 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 Repaired |5 |***** FP Indexes Checked |68 | HNG Indexes Checked |1 | HG Indexes Checked |17 | | | ==============================|===================================|=====
If an FP index is inconsistent, you may be able to rebuild the FP index from an HG or LF index on the same column. Recreate the FP index only if you are sure that the FP index is damaged (i.e., read errors).
If you specify an FP index in DBCC repair mode and a LF or HG index is present on the corresponding FP column, the FP index is recreated from the contents of that LF or HG index. Note that the FP index appears twice in the DBCC results, since the FP index verifies against the LF or HG after it is repaired.
When you recreate FP indexes, space for the original FP index is intentionally leaked. Use sp_iqcheckdb with the -iqdroplks server option to recover this space, as described in the section “Repairing allocation problems”.
Stat Value Flags ===========================|==============================|===== DBCC Repair Mode Report | | ===========================|==============================|===== ... ===========================|==============================|===== Index Statistics | | ===========================|==============================|===== ** Repaired Index |t1.DBA.ASIQ_IDX_T185_C1_FP |***** Verified Index |t1.DBA.ASIQ_IDX_T185_C1_FP | Verified Index |t1.DBA.t1c1hg | FP Indexes Checked |1 | HG Indexes Checked |1 |
Messages in the DBCC output related to repaired 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 |
|---|---|
Repaired Index Count |
The number of indexes that DBCC attempted to repair. |
Repaired Index |
The name of an index that DBCC has attempted to repair. |
Extra Index RIDs Repaired Missing Index RIDs Repaired Duplicate Index RIDs Repaired |
The total number of rows repaired for all inconsistent indexes. |
HG Missing Groups Repaired HG Extra Groups Repaired HG Extra Keys Repaired HG Missing Keys Repaired |
High Group index specific repair messages. |
The execution time of DBCC repair depends on the number of errors detected. If the number of errors reported is large, Sybase recommends dropping and recreating the index.
