Database Use

Consider your overall database when determining which dbcc commands to run, and when to run them.

If your SAP ASE 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. You need not coordinate this check 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 SAP ASE 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. See “Distributing Engine Resources” in the Performance and Tuning Series: Basics.

Some sites with 24-hour, high-performance demands run dbcc checks by:

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. Do not schedule dbcc checkdb (or other dbcc commands with similar side effects) to run while other business activities require the tables that are locked.