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.

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.

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:

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:

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 in dropleaks mode 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:

sp_iqcheckdb 'allocation database'

sp_iqcheckdb 'allocation database dumpleaks'

sp_iqcheckdb 'allocation table t1'

sp_iqcheckdb 'allocation index t1c1hg'

sp_iqcheckdb 'allocation indextype LF 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:

• dumpleaks — leaked blocks

• dumpdups — duplicate blocks

• dumpunallocs — unallocated blocks

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'

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'