Checks the validity of the current database, and optionally repairs indexes and allocation problems.
This stored procedure reads all storage within the database. On successful completion, the database free list is updated to reflect the true storage allocation for the database, if the -iqdroplks server switch is used. 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 any errors are detected.
The sp_iqcheckdb stored procedure also allows you to check the consistency of, and optionally repair, either a specified table, index, or the entire database.
The
stored procedure sp_iqcheckdb is the
user interface to the IQ Database Consistency Checker (DBCC) and
is sometimes referred to as DBCC.
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 repair mode. The syntax for each mode is listed separately below. 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.”
mode: { allocation | check | verify } | repair
target: [ main | local ] database | database resetclocks | { table table-name | index index-name […] }
sp_iqcheckdb 'allocation target [ resources resource-percent ]'
sp_iqcheckdb 'check target [ resources resource-percent ]'
sp_iqcheckdb 'verify target [ resources resource-percent ]'
sp_iqcheckdb 'repair target [ resources resource-percent ]'
main All tables and indexes checked are from the IQ Store. In a multiplex, they are from the shared IQ Store.
local All tables and indexes checked are from the local IQ Store on a particular query server in a multiplex.
index-name 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.
table-name 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.
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'
resource-percent The input parameter resource-percent must be an integer greater than 0. 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, then 1 thread is created per CPU. If resource-percent > 100, then there are more threads than CPUs, which may increase performance for some machine configurations. The minimum number of threads is 1. The default value of resource-percent is 100.
The sp_iqcheckdb parameter
string must be enclosed in single quotes and must not be greater
than 255 bytes in length.
Allocation problems can be repaired in check, verify, and allocation mode by starting the database with the -iqdroplks server switch.
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.
The sp_iqcheckdb stored procedure can perform several different functions, depending on the parameters specified. The four modes for checking and repairing database consistency are:
Allocation mode Checks allocation with blockmap information for the entire database, a specific index, or a specific table; repairs the free list if the -iqdroplks server switch is specified. Does not check index consistency.
Note that sp_iqcheckdb cannot check or repair all allocation problems, if you specify the name of a single index or table in the input parameter string.
When to run in allocation mode:
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
The DBCC option resetclocks is used only with allocation mode. The resetclocks option is used in conjunction with forced recovery to convert a multiplex query server to a write server. 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 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'
Check mode Checks allocation with index information; performs quick index checks for the entire database, a specific index, or a specific table. Detects all types of allocation problems and most types of index inconsistencies.
When to run in check mode:
If metadata, null count, or distinct count errors are returned when running a query
Verify mode Checks allocation with index information; performs detailed index checks for the entire database, a specific index, or a specific table. Detects 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
Repair mode Performs a detailed check and repair of all indexes, a specific index, or a specific table. Does not check allocation.
When to run in repair mode:
If index errors are reported in sp_iqcheckdb check or verify mode
In this example, sp_iqcheckdb checks the allocation for the entire database:
sp_iqcheckdb 'allocation database'
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 illustrated in the following command:
sp_iqcheckdb 'verify index i1 index i2 index dbo.t1.i3'
You can combine all modes, except for repair mode, and run multiple checks on a database in a single session. In the following example, sp_iqcheckdb performs a quick check of 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 verify index i1 allocation database resources 50'
The sp_iqcheckdb stored
procedure does not check referential integrity or repair referential
integrity violations.
See Chapter 2, “System Recovery and Database Repair” in the Sybase IQ Troubleshooting and Error Messages Guide for details on using sp_iqcheckdb and more information on checking database consistency.
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 or indexes, requires less time than checking an entire database.
Table 9-2 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 output of sp_iqcheckdb contains summary results, errors, informational statistics, and repair statistics, depending on the execution mode. 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 are displayed only if errors are detected.
Repair statistics are displayed only in repair mode and only if repairs are actually made. Asterisks (*****) indicate repairs that were made, not errors. Note that if sp_iqcheckdb encounters errors and makes repairs, some of the statistics reported by DBCC in repair mode may be inaccurate.
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.
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, 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 |***** DBCC Work units Dispatched |163 | DBCC Work units Completed |163 | =====================================|===========================|===== 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 | | =====================================|===========================|===== Blocks Created in Current TXN |382 | Blocks To Drop in Current TXN |382 | 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 | | =====================================|===========================|===== | | | |
