Does anyone has a script that checks if any database on a particular sybase server is offline and sends an email alert ( listing the database/s if any database/s is offline)?

Thanks,
Kishor

i have one something like below.

Not sure if it helps u.....

#!/bin/ksh
#stty -echo
. /u02/sybase/SYBASE.sh
if [ $# -ne 1 ]
then
echo "Usage dbdump.sh <SERVER NAME>"
exit
fi
export logdir=/home/sybase/dbawork/logs
export scriptdir=/home/sybase/dbawork/scripts
export DSQUERY=$1
export USERID=sa
export PASSWD=`cat "$scriptdir/.ABCDEFG_sa.dat" `
isql -U$USERID -Jiso_1 -S$DSQUERY -X -P$PASSWD -w5000 <<EOF> ${logdir}/dbnames.log
set nocount on
go
select name from sysdatabases where status2=-32752
go
EOF
cat ${logdir}/dbnames.log |egrep -v "name|-" > ${logdir}/dblist.log
for j in `cat ${logdir}/dblist.log`
do
echo "Database ${j} - is offline" >> (redirect to a file which can be monitored or else using isql/dbcc to print in errorlog)
done

(04-27-2010 02:33 AM)Kishor Wrote:  Does anyone has a script that checks if any database on a particular sybase server is offline and sends an email alert ( listing the database/s if any database/s is offline)?

Thanks,
Kishor

Hi,

Thanks.However the status2=-32752 in the query below

select name from sysdatabases where status2=-32752

indicates 'Database has some portion of the log which is not on a log-only device'.
So, not sure if it is going to work.

I would suggest using the below to find out the databases that are offline.


where status2=16 indicates 'Database is offline'

Hi Mate,

I agree with you, but checking for status2=16 does not show the name of offline databases if it has both data and log on the same devices.

It is always a better practice to have data and log on seperate devices but its feasible only in production environments.

In some cases testmachines and UAT machines may have such setups with data and log on the same device.

status2=-32752 indicates both offline and data/log on same device. (-32768+16=-32752)

when status2=-32768 it indicates data/log on same device and status2=0 indicates data/log on seperate devices...

so for offline databases it is status2=-32752(same devices) or status2=16(seperate devices)

hope the below on should be a better query to resolve this.

select name from sysdatabases where status2 in (-32752,16)
go

Cheers,
Karthik

(04-30-2010 06:24 PM)Joshi Wrote:  Hi,

Thanks.However the status2=-32752 in the query below

select name from sysdatabases where status2=-32752

indicates 'Database has some portion of the log which is not on a log-only device'.
So, not sure if it is going to work.

I would suggest using the below to find out the databases that are offline.

Code:

select name from master..sysdatabases where status2=16

where status2=16 indicates 'Database is offline'

Karthik,

Thanks.You rock.
I didn't realize this before.