The sp_iqcheckdb stored procedure

The IQ Database Consistency Checker (DBCC) performs database verification. 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.

NoteOn a secondary server sp_iqcheckdb does not check the free list. It performs all other checks.

DBCC has three modes that perform increasing amounts of consistency checking and a mode for resetting allocation maps. Each mode checks all database objects, unless individual dbspaces, tables, partitions, 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.

NoteThe sp_iqcheckdb stored procedure does not check referential integrity or repair referential integrity violations.

sp_iqcheckdb syntax

Refer to the section “sp_iqcheckdb procedure” in Chapter 7, “System Procedures,” in Reference: Building Blocks, Tables, and Procedures for the complete syntax of sp_iqcheckdb.

DBCC performance

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 Table 7-4 in Chapter 7, “System Procedures,” in Reference: Building Blocks, Tables, and Procedures for processing times of the sp_iqcheckdb modes.

For the best DBCC performance, 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.

sp_iqcheckdb check mode

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:

Examples of check mode:

Table 13-1: sp_iqcheckdb check mode examples

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

sp_iqcheckdb verify mode

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:

Examples of verify mode:

Table 13-2: sp_iqcheckdb verify mode examples

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

NoteIf 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.

sp_iqcheckdb allocation mode

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:

Examples of allocation mode:

Table 13-3: sp_iqcheckdb allocation mode examples

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

If some partitions of the table are offline, you can specify a partition target to check only part of a table.

You can combine all modes and run multiple checks on a database in a single session. In the following example, sp_iqcheckdb performs a quick check of partition p1 in table t2, a detailed check of index i1, and allocation checking for the entire database using half of the CPUs:

sp_iqcheckdb 'check table t2 partition p1 
verify index i1
allocation database resources 50'

Allocation mode options are only allowed with the DBCC command 'allocation database'.

The following allocation mode options print block numbers for affected database blocks to the IQ message file:

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'. The syntax of the resetclocks command is:

sp_iqcheckdb 'allocation database resetclocks'

sp_iqcheckdb dropleaks mode

When the Sybase IQ server runs in single-node mode, you can use dropleaks mode with either a database or dbspace target to reset the allocation map for the entire database or specified dbspace targets. If the target is a dbspace, then the dropleaks operation must also prevent read-write operations on the named dbspace. All dbspaces in the database or dbspace list must be online.

In the following example, the first statement resets allocation maps for the entire database, and the second statement resets allocation maps for the dbspace dbsp1.

sp_iqcheckdb 'dropleaks database'
sp_iqcheckdb 'dropleaks dbspace dbsp1'

NoteUse sp_iqrebuildindex to repair index errors. There is currently no support for repairing join indexes.