Weekly jobs

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

Disaster Recovery information collection
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


    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;

    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