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 three different verification modes that perform increasing amounts of consistency checking. 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 and 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 2, “System Recovery and Database Repair” in the Sybase IQ Troubleshooting and Error Messages Guide. For the complete syntax of sp_iqcheckdb, see Chapter 9, “System Procedures” in the Sybase IQ Reference Manual.
Run sp_iqcheckdb only on the write server of an IQ multiplex. If you run sp_iqcheckdb on a multiplex query 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 2, “System Recovery and Database Repair” of the Sybase IQ Troubleshooting and Error Messages Guide.
If other users are active when you run sp_iqcheckdb, the results you see reflect only what your transaction sees.
