If your Adaptive Server is used primarily between the hours of 8:00 a.m. and 5:00 p.m., Monday through Friday, you can run dbcc checks at night and on weekends so that the checks do not have a significant impact on your users. If your tables are not extremely large, you can run a complete set of dbcc commands fairly frequently.
dbcc checkstorage and dbcc checkcatalog provide the best coverage at the lowest cost, and assure recovery from backups. You can run dbcc checkdb or dbcc checktable less frequently to check index sort order and consistency. This check does not need to be coordinated with any other database maintenance activity. Reserve object-level dbcc checks and those checks that use the fix option for further diagnosis and correction of faults found by dbcc checkstorage.
If your Adaptive Server is used 24 hours a day, 7 days a week, you may want to limit the resource usage of dbcc checkstorage by limiting the number of worker processes or by using application queues. If you decide not to use dbcc checkstorage, you may want to schedule a cycle of checks on individual tables and indexes using dbcc checktable, dbcc tablealloc, and dbcc indexalloc. At the end of the cycle, when all tables have been checked, you can run dbcc checkcatalog and back up the database. For information on using application queues, see Chapter 5, “Distributing Engine Resources,” in the Performance and Tuning Series: Basics.
Some sites with 24-hour, high-performance demands run dbcc checks by:
Dumping the database to tape
Loading the database dump into a separate Adaptive Server to create a duplicate database
Running dbcc commands on the duplicate database
Running dbcc commands with the fix options on appropriate objects in the original database, if errors are detected that can be repaired with the fix options
The dump is a logical copy of the database pages; therefore, problems found in the original database are present in the duplicate database. This strategy is useful if you are using dumps to provide a duplicate database for reporting or some other purpose.
Schedule dbcc commands that lock objects to run when they avoid interference with business activities. For example, dbcc checkdb acquires locks for each table on which it performs the database check and then releases the lock once it finishes and procedes to the next table. These tables are not accessible while dbcc checkdb holds the lock. You should not schedule dbcc checkdb (or other dbcc commands with similar side affects) to run while other business activities require the tables that are locked.
