Validating database consistency is a critical task that should be performed regularly, using the SAP Sybase Adaptive Server Enterprise (ASE) dbcc checkstorage command. We recommend running this command nightly, if at all possible, so that you can find the small errors that sometimes occur before they become big problems.
When you run the command dbcc checkstorage, the faults it discovers are classified as either soft or hard faults. This article examines each type of fault and provides some guidelines for action you can take when you find them.
Soft DBCC faults: causes and actions
A soft fault is an inconsistency in ASE that has not been determined to be persistent. If dbcc checkstorage finds a soft fault, you should run it a second time. Typically, these faults aren’t repeated when you execute the command a second time.
Most soft faults result from temporary inconsistencies in the target database, often caused by users updating the target database during the dbcc checkstorage operation. Sometimes, soft faults result when dbcc checkstorage encounters Data Definition Language (DDL) commands.
You can reclassify soft faults by comparing the results of the first and second executions of dbcc checkstorage or by running dbcc tablealloc and dbcc checktable after dbcc checkstorage.
If the same soft faults occur in successive executions of dbcc checkstorage, they are considered “persistent” soft faults, and may indicate a corruption, or a “hard” fault (see the next section).
Note, however, that if you execute dbcc checkstorage in single-user mode, any soft faults reported are classified as “persistent” soft faults. You can resolve these faults by using sp_dbcc_differentialreport or by running dbcc tablealloc and dbcc checktable. If you use these latter two commands, you need to check only the tables or indexes that exhibited the soft faults.
Generally, soft faults that remain after dbcc checkstorage is run two or more times are not significant and you do not need to take further action.
Hard DBCC faults: causes and actions
A hard fault is a persistent corruption of ASE, but not all hard faults are equally severe. Consider, for example, that all the following situations cause a hard fault, but the results are quite different:
- A page that is allocated to a nonexistent table minimally reduces the available disk storage.
- A table with some rows that are unreachable by a scan might return the wrong results.
- A table that is linked to another table causes the query to stop.
You can correct some hard faults by simple actions such as truncating the affected table, or running a different dbcc command with the fix option. However, some hard faults can be corrected only by restoring the database from a backup.
Finding faults before disaster happens
Running the dbcc checkstorage command regularly will help you to find—and fix—faults before they become issues that impact your ability to recover successfully from disaster. Finding these soft and hard faults, and knowing how to fix them, should be a regular part of your database maintenance.
Leave a Reply