How to Monitor the Error Log

You can create a script that periodically checks the Adaptive Server error log and alerts the database administrator when a new error is written into the log. An example of such a script appears below. You can alter the sleep interval to suit your needs. You must also modify the script to identify file locations, server names, and – as necessary – to provide the equivalent syntax and path names for the shell commands used here.

WARNING! The dba_alert script is provided for your information - it is not supported at this time.

/*
 
#!/usr/bin/ksh
#set -x
###############################################################
# Ensures that this Shell Script is executed in the KORN Shell#
###############################################################
# Author:   Alan Harris                                       #
# Created:  09/15/97                                          #
###############################################################
# This script checks to make sure Sybase Server is still up.  #
# Also Checks the Server Errorlog for any Errors as well.     #
# It will check these items every hour until the server is    #
# down, at which time it terminates automatically.            #
# Just remember to add the startup instructions for this      #
# Script, which is to be started right after you have started #
# your Sybase Server.                                         #
###############################################################
# Notes on what to change so this script will work for you... #
#-------------------------------------------------------------#
# Check location of Errorlog and where you want the work      #
# and .msg files to be created.                               #
# Change all "plsql1" & "PLSQL1" to be your Sybase Server     #
#  Name so that you can make a copy of this script for        #
#  each of you Sybase Servers.                                #
# Enter E-mail Addresses where noted below inside <<  >>      #
#                                                             #
###############################################################

ERRORLOG=/usr/u/sybase/install/errorlog
DIFF_ERRORLOG=/usr/u/sybase/logs/errorlog.plsql1.diff
PRIOR_ERRORLOG=/usr/u/sybase/logs/errorlog.plsql1.prior
TAIL_ERRORLOG=/usr/u/sybase/logs/errorlog.plsql1.tail
INTERNET_ID=<<Your E-mail Address>>
INTERNET_ID_BKUP=<<Your Backup's E-mail Address>>
INTERNET_OPS_STAFF=<<Your Operations Staff E-mail Address>>
LOGS=/usr/u/sybase/logs
MSG=/usr/u/sybase/logs
MSG_ERRORLOG=/usr/u/sybase/logs/errorlog_error_mail_plsql1.msg
MSG_SERVER=/usr/u/sybase/logs/server_down_mail_plsql1.msg
MSG_SERVER_OPS=/usr/u/sybase/logs/server_down_OPS_mail_plsql1.msg
DSQUERY=PLSQL1
SYBASE=/usr/u/sybase
PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$SYBASE/bin:
     $SYBASE/install:/usr/bin/X11:/sbin:.
export SYBASE PATH ERRORLOG DIFF_ERRORLOG PRIOR_ERRORLOG LOGS MSG
export MSG_ERRORLOG MSG_SERVER DSQUERY
   
cd ${LOGS}

###############################################################
# While loop to do the following until Server is down!        #
# Checking to make sure Server is still up and Running        #
# Checking Server Errorlog for any Errors as well.            #
# Including a 60 min. sleep                                   #
###############################################################
  
while [ 1 ]
do
  
###############################################################
# Removing any previous error plsql1.msg files...             #
###############################################################
if [ -e ${MSG_ERRORLOG} ]
  then
        rm ${MSG_ERRORLOG}
  fi
    
  if [ -e ${MSG_SERVER} ]
  then
        rm ${MSG_SERVER}
  fi
    
if [ -e ${MSG_SERVER_OPS} ]
  then
        rm ${MSG_SERVER_OPS}
  fi

###############################################################
# Checking SQL Server Errorlog for Errors                     #
###############################################################
  
###############################################################
# If 1st time the shell executes then search entire errorlog  #
# otherwise search the file containing the difference than    #
# the last time the errorlog was searched.                    #
###############################################################
  if [ ! -e "${PRIOR_ERRORLOG}" ]
  then
    ERRORLOG_ERROR_YN=`grep -E "Error: 
       |infected|WARNING:|severity|encountered" 
       ${ERRORLOG} | grep -vE "1608,|21,"`
tail -n 50 ${ERRORLOG} > ${PRIOR_ERRORLOG}
      
    if [ ! -z "${ERRORLOG_ERROR_YN}"]
    then
        print "Subject: Error Messages were found in ${DSQUERY} 
           errorlog, Check Immediately! \n " > ${MSG_ERRORLOG}
        print "Error Messages were found in ${ERRORLOG}, \n \n
           Check immediately! \n ." >> ${MSG_ERRORLOG}
        grep -E "Error|infected|WARNING:|severity|encountered " 
           ${ERRORLOG} >> ${MSG_ERRORLOG}
        print "\nCheck Immediately!." >> ${MSG_ERRORLOG}
        sendmail ${INTERNET_ID} < ${MSG_ERRORLOG}
        sendmail ${INTERNET_ID_BKUP} < ${MSG_ERRORLOG}
        sendmail SYBASE < ${MSG_ERRORLOG}
     fi

 else
     tail -n 50 ${ERRORLOG} > ${TAIL_ERRORLOG}
     diff ${TAIL_ERRORLOG} ${PRIOR_ERRORLOG} | grep \< | 
        cut -c 2-200 > ${DIFF_ERRORLOG}
     cp ${TAIL_ERRORLOG} ${PRIOR_ERRORLOG}
     DIFF_ERRORLOG_ERROR_YN=`grep -E "Error: 
        |infected|WARNING:|severity|encountered " 
        ${DIFF_ERRORLOG} | grep -vE "1608,|21,"`
  
     if [ ! -z "${DIFF_ERRORLOG_ERROR_YN}"]
     then
        print "Subject: Error Messages were found in ${DSQUERY} 
           errorlog, Check Immediately! \n " > ${MSG_ERRORLOG}
print "Error Messages were found in ${ERRORLOG}, \n \n
           Check immediately! \n ." >> ${MSG_ERRORLOG}
        grep -E "Error|infected|WARNING:|severity|encountered" 
           ${DIFF_ERRORLOG} >> ${MSG_ERRORLOG}
        print "\nCheck Immediately!." >> ${MSG_ERRORLOG}
        sendmail ${INTERNET_ID} < ${MSG_ERRORLOG}
        sendmail ${INTERNET_ID_BKUP} < ${MSG_ERRORLOG}
        sendmail SYBASE < ${MSG_ERRORLOG}
      fi
  fi

###############################################################
# Checking to make sure Server is still up and Running        #
###############################################################
  SERVER_UP_YN=`ps -ef|grep SYBASE|grep dataserver|grep ${DSQUERY}`
  if [ -z "${SERVER_UP_YN}" ]
  then
       print "Subject: The ${DSQUERY} Sybase Server is Down! 
          Check immediately!. \n " > ${MSG_SERVER}
       print "The ${DSQUERY} Sybase Server is Down, \n \n
          Check immediately! \n \n ." >> ${MSG_SERVER}
       tail -n 10 ${ERRORLOG} >> ${MSG_SERVER}
       sendmail ${INTERNET_ID} < ${MSG_SERVER}
       sendmail ${INTERNET_ID_BKUP} < ${MSG_SERVER}
       sendmail SYBASE < ${MSG_SERVER}
 
print "Subject: The ${DSQUERY} Sybase Server is Down! 
          Take the following action immediately!.\n ">${MSG_SERVER_OPS}
       print "Hold all Mainframe jobs that require ${DSQUERY}
             (Production Sybase)," >> ${MSG_SERVER_OPS}
       print "until further notice. \n" >> ${MSG_SERVER_OPS}
       print "Refer to the Document, \"JCL and Procs Using OUTBOUND
          - Group Systems Team\". " >> ${MSG_SERVER_OPS}
       print "This Document can be found in the OPS Procedures 
         Manual." >> ${MSG_SERVER_OPS}
       print "Hold ALL Jobs that have Sybase in the \
          "Destination From/To\" column." >> ${MSG_SERVER_OPS}
sendmail ${INTERNET_OPS_STAFF} < ${MSG_SERVER_OPS}
       sendmail SYBASE < ${MSG_SERVER_OPS}
       break 
    fi
    sleep 3600
  done