Weekly jobs

These are descriptions and cron entries for weekly activities, which are run over the weekends.

Processes:
Disaster Recovery information collection
Dbccs
Swatch check of dbcc output
Index/Constraint Maintenance

Disaster Recovery collection

We run an automated collection of disaster recover information for the servers. The script is max:/export/syb_ops/weekend/dr.sh. The information collected is stored in max:/export/syb_ops/dr. This information is meant to allow a complete recovery from a master database or master device crash. We collect (where applicable based on server version):

sysusages table, ordered by dbid and vstart

sysdatabases, sp_helpdb output

sysdevices, sp_helpdevice output

bcp out of syslogins (better than selecting * from syslogins)

sp_configure output, sysconfigures

sysservers, syssegments, sysusers, sysremotelogins, syscharsets

sysloginroles (if > 4.9.2 server)

dump of master database (if > 4.9.2 server)

The max:/export/syb_ops/weekend/weekend_front.sh script called from cron includes a sybping connectivity check before executing the DR scripts; this prevents us from overwriting existing information with server connectivity error messages in the case of a down server (which might really need that DR information!)

(sybase's cron on max)
0 2 * * 6 /export/syb_ops/weekend/weekend_front.sh

back to top

DBCCs

DBCC stands for DataBase Consistency Check, and is the primary was we perform preventative maintenance on Sybase databases. We currently run the recommended set of 3 checks;

checkdb: checks all tables in a database and ensures correct page linkages, non-corrupt indexes, and consistent pointers, page-fill factors, and the like. This includes the dbcc reindex functionality.
checkalloc; checks each page in a database and ensures there are no page allocation errors (pages allocated but not used, pages used but not allocated, errors in page link chains, etc)
checkcatalog: performs consistency checks on the system tables associated with each database, looking for errors. The shortest of the three.

The script called is max:/export/syb_ops/weekend/dbcc.sh. Output is written to max:/export/syb_ops/dbcc/[date]/[server].[db].dbcc.[date]

(sybase's cron on max)
0 2 * * 6 /export/syb_ops/weekend/weekend_front.sh

back to top

Swatch check of Dbcc output

Swatch is specifically called to review output files of the dbcc jobs. This fact is also mentioned on the Swatch page under the non-perpetual topic. Whats nice is that we have configured the swatchrc file to pick up DBCC error message formats as well as normal errorlog formats, so only one swatchrc file is needed per installation.

(sybase's cron on max)
0 7 * * 1 /export/syb_ops/swatch/weekend_swatch.sh

back to top

Index/Constraint maintenance

Clustered indexes and constraints physically determine the order of data on the disk. After many inserts/updates/deletes the ordering schemes may become inefficient. Fragmentation occurs. By dropping and recreating the clustered index or constraint, we force the data to be resorted and thus optimized.

Also, we have seen cases where non-clustered indexes can get corrupted and actually reference the wrong data (or not reference all the data completely). Such corruption is not always detectable by a dbcc indexalloc or checktable. By dropping and recreating non-clustered indexes, we eliminate this problem from occuring.

If the option is set within the max:/export/syb_ops/weekend/index.pl script, the sybperl script will save the DDL it creates as it executes it to the directory max:/export/syb_ops/index_ddl/[server]/[database]/[ddl_file]

(sybase's cron on max)
0 3 * * 6 /export/syb_ops/weekend/index_front.sh

back to top