Scheduling dbcc checkverify

Verify persistent faults by running checkverify anytime after running checkstorage, even after an extended period of hours or days. However, when deciding your schedule, keep in mind that the database state changes over time, and the changes can mask both soft faults and hard faults.

For example, a page that is linked to a table but not allocated is a hard fault. If the table is dropped, the fault is resolved and masked. If the page is allocated to another table, the fault persists but its signature changes. The page now appears to be linked to two different tables. If the page is reallocated to the same table, the fault appears as a corrupt page chain.

Persistent faults that are corrected by a subsequent database change do not usually pose operational problems. However, detecting and quickly verifying these faults may locate a source of corruption before more serious problems are encountered, or before the signature of the original fault changes. For this reason, SAP recommends that you run checkverify as soon as possible after running dbcc checkstorage.

Note: When you execute checkstorage with the target database in single-user mode, there are no transient soft faults and therefore, no need to execute checkverify.

checkverify runs only one time for each execution of checkstorage. However, if checkverify is interrupted and does not complete, you can run it again. The operation resumes from where it was interrupted.

checkverify may take a long time to complete when processing very large databases. During this time, checkverify does not provide you with any indication of when it will finish.

To see progress status reports during checkverify, use the command_status_reporting command:

set command_status_reporting on

Now, when you run checkverify, you see results similar to:

dbcc checkverify (pubs2)

Verifying faults for ’pubs2’. 
Verifying faults for table 't1'. The total number of tables to verify is 5. This
is table number 1. 
Verifying faults for table 't2'. The total number of tables to verify is 5. This 
is table number 2. 
Verifying faults for table 't3'. The total number of tables to verify is 5. This 
is table number 3. 
Verifying faults for table 't4'. The total number of tables to verify is 5. This 
is table number 4. 
Verifying faults for table 't5'. The total number of tables to verify is 5. This 
is table number 5. 
DBCC CHECKVERIFY for database ’pubs2’ sequence 4 completed at Apr  9 2003 
2:40PM. 72 suspect conditions were resolved as faults, and 11 suspect conditions 
were resolved as harmless. 0 objects could not be checked.