The IQ Database Consistency Checker (DBCC) performs database verification and repair functions. The sp_iqcheckdb stored procedure, in conjunction with server startup options, is the interface to DBCC. You select the different modes of check and repair by specifying an sp_iqcheckdb command string. sp_iqcheckdb reads every database page and checks the consistency of the database, unless you specify otherwise in the command string.
On a query server sp_iqcheckdb does
not check the freelist. It performs all other checks.
DBCC has three different modes that perform increasing amounts of consistency checking and a repair mode for repairing inconsistent indexes. Each mode checks all database objects, unless individual tables, indexes, or index types are specified in the sp_iqcheckdb command string. If you specify individual table names, all indexes within those tables are also checked.
The sp_iqcheckdb stored
procedure does not check referential integrity or repair referential
integrity violations.
The following command line shows the basic syntax of sp_iqcheckdb.
sp_iqcheckdb 'mode target [...] [ resources resource-percent ]'
mode: { allocation | check | verify } | repair
target: [ main | local | indextype index-type […] ] database{ dumpdups | dumpleaks | dumpunallocs } | database resetclocks | { [ indextype index-type ] […] table table-name | index index-name […] }
If both mode and target are not specified in the parameter string, IQ returns the error message “At least one mode and target must be specified to DBCC.”
The index-name parameter may contain
owner and table qualifiers: [[owner.]table-name.]index-name.
If owner is not specified, current user and
database owner (dbo) are substituted in that
order. If table is not specified, then index-name must be
unique.
The table-name parameter may contain
an owner qualifier: [owner.]table-name.
If owner is not specified, current user and
database owner (dbo) are substituted in that
order. table-name cannot be a temporary or
pre-join table.
The sp_iqcheckdb parameter
string must be enclosed in single quotes and must not be greater
than 255 bytes in length.
If either the table name or the index name contains spaces, enclose the table-name or index-name parameter in double quotes, as shown in this example:
sp_iqcheckdb 'check index "dbo.ss tab.i2" resources 75'
Refer to the section “sp_iqcheckdb procedure” in Chapter 10, “System Procedures” of the Sybase IQ Reference Manual for the complete syntax of sp_iqcheckdb.
The following table summarizes the actions and output of the four sp_iqcheckdb modes.
Mode |
Errors detected |
Output |
Speed |
|---|---|---|---|
allocation |
allocation errors |
allocation statistics only |
4TB per hour |
check |
allocation errors most index errors |
all available statistics |
60GB per hour |
verify |
allocation errors all index errors |
all available statistics |
15GB per hour |
repair |
all index errors |
repair statistics |
15+GB per hour* |
* The processing time of sp_iqcheckdb repair mode depends on the number of errors repaired.
The execution time of DBCC varies according to the size of the database for an entire database check, the number of tables or indexes specified, and the size of the machine. Checking only a subset of the database, i.e., only specified tables, indexes, or index types, requires less time than checking an entire database. Refer to the table in the previous section for processing times of the sp_iqcheckdb modes.
For the best DBCC performance, you should be as specific as possible in the sp_iqcheckdb command string. Use the 'allocation' or 'check' verification mode when possible and specify the names of tables or indexes, if you know exactly which database objects require checking.
In check mode, sp_iqcheckdb performs an internal consistency check on all IQ indexes and checks that each database block has been allocated correctly. All available database statistics are reported. This mode reads all data pages and can detect all types of allocation problems and most types of index inconsistencies. Check mode should run considerably faster than verify mode for most databases.
When to run in check mode:
If metadata, null count, or distinct count errors are returned when running a query
Examples of check mode:
Command |
Description |
|---|---|
sp_iqcheckdb 'check database' |
Internal checking of all tables and indexes in the database |
sp_iqcheckdb 'check table t1' |
Default checking of all indexes in table t1 |
sp_iqcheckdb 'check index t1c1hg' |
Internal checking of index t1c1hg |
sp_iqcheckdb 'check indextype FP database' |
Checking of all indexes of type FP in the database |
In verify mode, sp_iqcheckdb performs an intra-index consistency check, in addition to internal index consistency and allocation checking. All available database statistics are reported. The contents of each non-FP index is verified against its corresponding FP index(es). Verify mode reads all data pages and can detect all types of allocation problems and all types of index inconsistencies.
When to run in verify mode:
If metadata, null count, or distinct count errors are returned when running a query
Examples of verify mode:
Command |
Description |
|---|---|
sp_iqcheckdb 'verify database' |
Verify contents of all indexes in the database |
sp_iqcheckdb 'verify table t1' |
Verify contents of all indexes in table t1 |
sp_iqcheckdb 'verify index t1c1hg' |
Verify contents of index t1c1hg |
sp_iqcheckdb 'verify indextype HG table t1' |
Verify contents of all HG indexes in table t1 |
If you check individual non-FP indexes in check mode,
the corresponding FP index(es) are automatically verified with internal consistency
checks and appear in the DBCC results.
In allocation mode, sp_iqcheckdb checks that each database block is allocated correctly according to the internal physical page mapping structures (blockmaps). Database statistics pertaining to allocation are also reported. This mode executes very quickly. Allocation mode, however, does not check index consistency and cannot detect all types of allocation problems.
When to run in allocation mode:
To check for leaked blocks or inconsistent indexes due to multiply owned blocks
After forced recovery, run sp_iqcheckdb with the -iqdroplks server switch to reset the allocation map (must use database as the target)
To check for duplicate or unowned blocks (use database or specific tables or indexes as the target)
If you encounter page header errors
Examples of allocation mode:
Command |
Description |
|---|---|
sp_iqcheckdb 'allocation database' |
Allocation checking of entire database |
sp_iqcheckdb 'allocation database dumpleaks' |
Allocation checking of entire database and print block numbers for leaked blocks to IQ message file |
sp_iqcheckdb 'allocation table t1' |
Allocation checking of table t1 |
sp_iqcheckdb 'allocation index t1c1hg' |
Allocation checking of index t1c1hg |
sp_iqcheckdb 'allocation indextype LF table t2' |
Allocation checking of all LF indexes in table t2 |
Allocation mode options are only allowed with the DBCC command 'allocation database'. They may be used with the -droplks server startup switch, but do not require it. For example, you can start the database with -iqfrec but without -droplks, and use allocation mode to find inconsistent indexes. You may then choose to restart the server with both -iqfrec and -droplks in order to repair problems found.
The following allocation mode options print block numbers for affected database blocks to the IQ message file:
dumpleaks — leaked blocks
dumpdups — duplicate blocks
dumpunallocs — unallocated blocks
The DBCC option resetclocks is used in conjunction with forced recovery to convert a multiplex query server to a write server. The resetclocks option corrects the values of internal database versioning clocks, in the event that these clocks are slow. Do not use the resetclocks option for any other purpose unless you contact Sybase IQ Technical Support.
The resetclocks option must be run in single user mode and is only allowed with the DBCC command 'allocation database'. resetclocks does not require the -iqdroplks server startup switch. The syntax of the resetclocks command is:
sp_iqcheckdb 'allocation database resetclocks'
See the section “Replacing write servers” for more information on converting a multiplex query server to a write server.
The repair mode of sp_iqcheckdb performs detailed index checking and can repair many types of index inconsistencies. Allocation checks are not performed during repair mode. The DBCC output indicates which indexes, if any, were repaired. You must issue a COMMIT command to commit changes from repair mode to make them permanent. 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.
When to run in repair mode:
If index errors are reported in sp_iqcheckdb check or verify mode
Examples of repair mode:
Command |
Description |
|---|---|
sp_iqcheckdb 'repair database' |
Detailed check and repair of entire database |
sp_iqcheckdb 'repair table t1' |
Detailed check and repair of table t1 |
sp_iqcheckdb 'repair index t1c1hg' |
Detailed check and repair of index t1c1hg |
sp_iqcheckdb 'repair indextype HG database' |
Detailed check and repair of all HG indexes in the database |
There is currently no support for repairing join indexes.
You should back up the database before executing sp_iqcheckdb in an index repair mode.
