Check Database Integrity

Run dbcc checks and perform database backups to protect the integrity and recoverability of your SAP ASE databases.

Use dbcc checkstorage to run regular consistency checks on large databases when the amount of time you require to perform consistency checks is less than the amount of time required for dbcc checkalloc or dbcc checkdb to complete. dbcc checkstorage performs a fast consistency check, but it is not as thorough as dbcc checkalloc or dbcc checkdb. See Preparing to use dbcc checkstorage and Checking Database Consistency in the System Administration Guide, Volume 2, and the Reference Manual: Commands.

You can also run this sample C shell script, which calls several isql scripts:

#!/bin/csh -f
 if ( -e dbcc_mail.out) then
   rm dbcc_mail.out
 endif
 foreach i (*.dbcc)
 isql -Usa -Ppassword < $i > dbcc_out
 if ( ‘grep -c ‘Msg 25[0-9][0-9]’ dbcc_out’ ) then
   echo "There are errors in" $i >> dbcc_mail.out
   cat dbcc_out >> dbcc_mail.out
 else
   echo "Backing up " $i:r >> dbcc_mail.out
   isql -Usa -Ppassword < $i:r.backup
 endif
 end
 mail -s "Backup Report" jjones < dbcc_mail.out

The first set of scripts (one for each database with a file name appended with .dbcc) runs dbcc checkalloc and dbcc checkdb for each database and sends the messages to an output file called dbcc_out.

For example, the script master.dbcc runs dbcc to check the master database:

dbcc checkalloc (master)
go
dbcc checkdb (master)
go

The C shell script then runs the grep command to find 2500-level error messages in dbcc output. The results of the grep command are sent to an output file called dbcc_mail.out.

Next, the script invokes an isql backup script for each database for which no 2500-level errors occurred and adds the “Backing up database_name” line to dbcc_mail.out. For example, the script master.backup backs up the master database:

use master
go
dump database master to master_dump
go

You may want to add appropriate dump transaction commands to your scripts.

If there are 2500-level error messages, the script does not back up the database. At the end of the script, dbcc_mail.out is mailed to the system administrator “jjones,” who then has a record of fatal dbcc errors and successful backups.

You can tailor the sample shell and isql scripts to suit the needs of your installation.

To have the scripts execute automatically, edit the crontab file, and add an entry similar to this, which executes a C shell script called dbcc_ck daily at 2:00 a.m.:

00 02 * * * /usr/u/sybase/dbcc_ck 2>&1

This example executes a C shell script called dbcc_ck every morning at 2:00 a.m.