Backing up a database is useful only if the database is internally consistent. Backup always makes sure that the database is in a usable state before proceeding. However, validating a database before you perform a backup is a good idea, to ensure that the database you restore is stable. The restore program does not check for inconsistencies in the restored data, since the database may not even exist.
To validate your database, issue the following command:
sp_iqcheckdb 'check database'
The sp_iqcheckdb stored procedure, in conjunction with server startup switches, is the interface to the IQ Database Consistency Checker (DBCC).
DBCC has different verification modes that perform increasing amounts of consistency checking. There are three modes for checking database consistency and one for resetting allocation maps. Each mode checks all database objects, if you specify 'database' as the target in the sp_iqcheckdb command string. Individual tables and indexes can also be specified in the command string. If you specify individual table names, all indexes within those tables are also checked.
The following table summarizes the actions, output, and speed of the three sp_iqcheckdb verification 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 |
The database option DBCC_LOG_PROGRESS instructs sp_iqcheckdb to send progress messages to the IQ message file. These messages allow you to follow the progress of the sp_iqcheckdb procedure as it executes.
You should run sp_iqcheckdb before or after backup, and whenever you suspect a problem with the database. For details on using sp_iqcheckdb and interpreting the sp_iqcheckdb output, see Chapter 13, “System Recovery and Database Repair.” For the complete syntax of sp_iqcheckdb, see Chapter 7, “System Procedures,” in the Reference: Building Blocks, Tables, and Procedures.
Run sp_iqcheckdb only on the write server of an IQ multiplex. If you run sp_iqcheckdb on a multiplex secondary server, an error is returned.
When you run sp_iqcheckdb on an entire database, sp_iqcheckdb reads every database page in use. This procedure consumes most of the database server's time, so that the I/O is as efficient as possible. Any other concurrent activities on the system run more slowly than usual. The CPU utilization of DBCC can be limited by specifying the sp_iqcheckdb parameter resources resource-percent, which controls the number of threads with respect to the number of CPUs. For information on using the resources parameter, see the section “Resource issues running sp_iqcheckdb” in Chapter 13, “System Recovery and Database Repair.”
If other users are active when you run sp_iqcheckdb, the results you see reflect only what your transaction sees.
