sp_iqcheckdb Procedure

Checks validity of the current database. Optionally corrects allocation problems for dbspaces or databases.

sp_iqcheckdb reads all storage in the database. On successful completion, the database free list (an internal allocation map) is updated to reflect the true storage allocation for the database. sp_iqcheckdb then generates a report listing the actions it has performed.

If an error is found, sp_iqcheckdb reports the name of the object and the type of error. sp_iqcheckdb does not update the free list if errors are detected.

sp_iqcheckdb also allows you to check the consistency of a specified table, index, index type, or the entire database.

Note: sp_iqcheckdb is the user interface to the IQ database consistency checker (DBCC) and is sometimes referred to as DBCC.

Syntax

sp_iqcheckdb 'mode target [ … ] [ resources resource-percent ]'
This is the general syntax of sp_iqcheckdb. There are three modes for checking database consistency, and one for resetting allocation maps. The syntax for each mode is listed separately below. If mode and target are not both specified in the parameter string, Sybase IQ returns the error message:
At least one mode and target must be specified to DBCC.

Parameters

mode: { allocation | check verify } | dropleaks

target: [ indextype index-type […] ] database | database resetclocks | { [ indextype index-type ] […] table  table-namepartition  partition-name ]  […] |  index  index-name |  […]  dbspace  dbspace-name}

Allocation Mode

sp_iqcheckdb 'allocation target [ resources resource-percent ]'

Check Mode

sp_iqcheckdb 'check target [ resources resource-percent ]'

Verify mode

sp_iqcheckdb 'verify target [ resources resource-percent ]'

Dropleaks Mode

sp_iqcheckdb 'dropleaks target [ resources resource-percent ]'

Usage

Parameters

Parameter

Description

database

If the target is a database, all dbspaces must be online.

index-type

One of the following index types: FP, CMP, LF, HG, HNG, WD, DATE, TIME, DTTM, TEXT.

If the specified index-type does not exist in the target, an error message is returned. If multiple index types are specified and the target contains only some of these index types, the existing index types are processed by sp_iqcheckdb.

index-name

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, index-name must be unique.

table-name

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.

Note: If either the table name or the index name contains spaces, enclose the table-name or index-name parameter in double quotation marks:
sp_iqcheckdb 'check index "dbo.sstab.i2" resources 75'

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.

Note: If either the table name or the index name contains spaces, enclose the table-name or index-name parameter in double quotation marks:
sp_iqcheckdb 'check index "dbo.sstab.i2" resources 75'

partition-name

The partition-name parameter contains no qualifiers. If it contains spaces, enclose it in double quotation marks.

The partition filter causes sp_iqcheckdb to examine a subset of the corresponding table’s rows that belong to that partition. A partition filter on a table and table target without the partition filter are semantically equivalent when the table has only one partition.

dbspace-name

The dbspace-name parameter contains no qualifiers. If it contains spaces, enclose it in double quotation marks.

The dbspace target examines a subset of the database's pages that belong to that dbspace. The dbspace must be online. The dbspace and database target are semantically equivalent when the table has only one dbspace.

resource-percent

The input parameter resource-percent must be an integer greater than zero. The resources percentage allows you to limit the CPU utilization of the database consistency checker by controlling the number of threads with respect to the number of CPUs. If resource-percent = 100 (the default value), then one thread is created per CPU. If resource-percent > 100, then there are more threads than CPUs, which might increase performance for some machine configurations. The minimum number of threads is one.

Note: The sp_iqcheckdb parameter string must be enclosed in single quotes and cannot be greater than 255 bytes in length.

Allocation problems can be repaired in dropleaks mode.

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Description

sp_iqcheckdb checks the allocation of every block in the database and saves the information in the current session until the next sp_iqdbstatistics procedure is issued. sp_iqdbstatistics displays the latest result from the most recent execution of sp_iqcheckdb.

sp_iqcheckdb can perform several different functions, depending on the parameters specified.

Allocation Mode

Checks allocation with blockmap information for the entire database, a specific index, a specific index type, a specific partition, specific table, or a specific dbspace. Does not check index consistency.

Detects duplicate blocks (blocks for which two or more objects claim ownership) or extra blocks (unallocated blocks owned by an object).

Detects leaked blocks (allocated blocks unclaimed by any object in the specified target) for database or dbspace targets.

When the target is a partitioned table, allocation mode:
  • Checks metadata of all the table’s partition allocation bitmaps

  • Checks metadata of the tables allocation bitmap

  • Verifies that blockmap entries are consistent with the table’s allocation bitmap

  • Verifies that none of the table’s partition allocation bitmaps overlap

  • Checks that rows defined in the table’s partition allocation bitmaps form a superset of the table’s existence bitmap

  • Checks that rows defined in the table’s partition allocation bitmaps form a superset of the table’s allocation bitmap

Note: sp_iqcheckdb cannot check all allocation problems if you specify the name of a single index, index type, or table in the input parameter string.
Run in allocation mode:
  • To detect duplicate or unowned blocks (use database or specific tables or indexes as the target)

  • If you encounter page header errors

The DBCC option resetclocks is used only with allocation mode. resetclocks is used with forced recovery to convert a multiplex secondary server to a coordinator. For information on multiplex capability, see Using Sybase IQ Multiplex. resetclocks corrects the values of internal database versioning clocks, in the event that these clocks are behind. 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 allowed only with the DBCC statement allocation database. The syntax of resetclocks is:
sp_iqcheckdb 'allocation database resetclocks'

Check Mode

Verifies that all database pages can be read for the entire database, specific index, specific index type, specific table, specific partition, or specific dbspace. If the table is partitioned, then check mode will check the table’s partition allocation bitmaps.

Run in check mode if metadata, null count, or distinct count errors are returned when running a query.

Verify Mode

Verifies the contents of non-FP indexes with their corresponding FP indexes for the entire database, a specific index, a specific index type, specific table, specific partition, or specific dbspace. If the specified target contains all data pages for the FP and corresponding non-FP indexes, then verify mode detects the following inconsistencies:
  • Missing key – a key that exists in the FP but not in the non-FP index.

  • Extra key – a key that exists in the non-FP index but not in the FP index.

  • Missing row – a row that exists in the FP but not in the non-FP index.

  • Extra row – a row that exists in the non-FP index but not in the FP index.

If the specified target contains only a subset of the FP pages, then verify mode can detect only the following inconsistencies:
  • Missing key

  • Missing row

If the target is a partitioned table, then verify mode also verifies that each row in the table or table partition has been assigned to the correct partition.

Run in verify mode if metadata, null count, or distinct count errors are returned when running a query.

Note: sp_iqcheckdb does not check referential integrity or repair referential integrity violations.

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.

For information on running dropleaks mode on a multiplex, see Using Sybase IQ Multiplex.

The following examples illustrate the use of the sp_iqcheckdb procedure.

Example 1

In this example, sp_iqcheckdb checks the allocation for the entire database:
sp_iqcheckdb 'allocation database'

Example 2

In the second example, sp_iqcheckdb performs a detailed check on indexes i1, i2, and dbo.t1.i3. If you do not specify a new mode, sp_iqcheckdb applies the same mode to the remaining targets, as shown in the following command:
sp_iqcheckdb 'verify index i1 index i2 index dbo.t1.i3'

Example 3

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'

Example 4

This example checks all indexes of the type FP in the database:
sp_iqcheckdb 'check indextype FP database'

Example 5

The following example verifies the FP and HG indexes in the table t1 and the LF indexes in the table t2:
sp_iqcheckdb 'verify indextype FP indextype HG table t1 indextype LF table t2'

Example 6

The following example illustrates one of the three “LVC cells” messages in the output of sp_iqcheckdb:

sp_iqcheckdb 'check index EFG2JKL.ASIQ_IDX_T208_C504_FP'
------------------------------------
Index Statistics:
** Inconsistent Index: abcd.EFG2JKL.ASIQ_IDX_T208_C504_FP ****** FP
Indexes Checked: 1
** Unowned LVC Cells: 212 ******

The sp_iqcheckdb LVC cells messages include:

  • Unowned LVC cells

  • Duplicate LVC cell rows

  • Unallocated LVC cell rows

These messages indicate inconsistencies with a VARCHAR, VARBINARY, LONG BINARY (BLOB), or LONG VARCHAR (CLOB) column. Unowned LVC cells represent a small amount of unusable disk space and can safely be ignored. Duplicate and Unallocated LVC cells are serious errors that can be resolved only by dropping the damaged columns.

To drop a damaged column, create a new column from a copy of the old column, then drop the original column and rename the new column to the old column.

Note: LVC is a VARCHAR or VARBINARY column with a width greater than 255. LONG BINARY (BLOB) and LONG VARCHAR (CLOB) also use LVC.

DBCC performance

The execution time of DBCC varies, depending on 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 (that is, only specified tables, indexes, or index types) requires less time than checking an entire database.

The processing time of sp_iqcheckdb dropleaks mode depends on the number of dbspace targets.

This table summarizes the actions and output of the four sp_iqcheckdb modes.

Actions and output of 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

Dropleaks

Allocation errors

Allocation statistics only

4TB per hour

Output

Depending on the execution mode, sp_iqcheckdb output includes summary results, errors, informational statistics, and repair statistics. The output may contain as many as three results sets, if you specify multiple modes in a single session. Error statistics are indicated by asterisks (*****), and appear only if errors are detected.

The output of sp_iqcheckdb is also copied to the Sybase IQ message file .iqmsg. If the DBCC_LOG_PROGRESS option is ON, sp_iqcheckdb sends progress messages to the IQ message file, allowing the user to follow the progress of the DBCC operation as it executes.

Output Example

The following is an example of the output you see when you run sp_iqcheckdb 'allocation database' and there is leaked space. Leaked space is a block that is allocated according to the database free list (an internal allocation map), but DBCC finds that the block is not part of any database object. In this example, DBCC reports 32 leaked blocks.

                     Stat                      Value              Flags
=====================================|===========================|=====
DBCC Allocation Mode Report
=====================================|===========================|=====
** DBCC Status                       |Errors Detected            |*****
=====================================|===========================|=====
Allocation Summary                   |                           | 
=====================================|===========================|=====
   Blocks Total                      |8192                       | 
   Blocks in Current Version         |4954                       | 
   Blocks in All Versions            |4954                       | 
   Blocks in Use                     |4986                       | 
   % Blocks in Use                   |60                         | 
** Blocks Leaked                     |32                         |*****
                                     |                           |
=====================================|===========================|=====
Allocation Statistics                |                           | 
=====================================|===========================|=====
   Marked Logical Blocks             |8064                       | 
   Marked Physical Blocks            |4954                       | 
   Marked Pages                      |504                        | 
   Blocks in Freelist                |126553                     | 
   Imaginary Blocks                  |121567                     | 
   Highest PBN in Use                |5432                       | 
** 1st Unowned PBN                   |452                        |*****
   Total Free Blocks                 |3206                       | 
   Usable Free Blocks                |3125                       | 
   % Free Space Fragmented           |2                          |
   Max Blocks Per Page               |16                         | 
   1  Block Page Count               |97                         | 
   3  Block Page Count               |153                        | 
   4  Block Page Count               |14                         | 
   ...
   9  Block Hole Count               |2                          | 
   16 Block Hole Count               |194                        | 
                                     |                           | 
   Database Objects Checked          |1                          | 
   B-Array Count                     |1                          | 
   Blockmap Identity Count           |1                          | 
=====================================|===========================|
=====Connection Statistics                |                           |