Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

script for Checking database online or not Expand / Collapse
Author
Message
Posted Thursday, July 9, 2009 7:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 2, 2010 9:07 AM
Points: 34, Visits: 128
Hi friends,

I have to check my Sql Server databases are online or not as part of my daily Server monitoring.
I am checking this by logging into each and every server.

but our requirement is we need get alert when the database is offline/down.

that means a script which will ping/ check the state of the database on repititive intervals.

Can anyone please help me for this script.

thanks,
mohan
Post #750190
Posted Thursday, July 9, 2009 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:54 AM
Points: 12,905, Visits: 32,163
that's an easy one for SQL 2005, mohan;
one of the sys views has exactly what you are looking for;
select state_desc,* from sys.databases

the state_desc is usually ONLINE or OFFLINE, but there are some onbetween statuses you might see if you were in the middle of a restore or something.


for SQL 2000, it's a bit more cryptic, since there is one field with all the bits in it;
here's a script to extract the values:
you want the "offline" True/Flase field in your case:
SELECT 'name: ' + [name]     + CHAR(13) +
'autoclose: '+ MIN(CASE status & 1 WHEN 1 THEN 'True' ELSE 'False' END) + CHAR(13) +
'SELECT into/bulkcopy: '+ MIN(CASE status & 4 WHEN 4 THEN 'True' ELSE 'False' END) + CHAR(13) +
'trunc. log on chkpt: '+ MIN(CASE status & 8 WHEN 8 THEN 'True' ELSE 'False' END) + CHAR(13) +
'torn page detection: '+ MIN(CASE status & 16 WHEN 16 THEN 'True' ELSE 'False' END) + CHAR(13) +
'loading: '+ MIN(CASE status & 32 WHEN 32 THEN 'True' ELSE 'False' END) + CHAR(13) +
'pre recovery: '+ MIN(CASE status & 64 WHEN 64 THEN 'True' ELSE 'False' END) + CHAR(13) +
'recovering: '+ MIN(CASE status & 128 WHEN 128 THEN 'True' ELSE 'False' END) + CHAR(13) +
'Falset recovered: '+ MIN(CASE status & 256 WHEN 256 THEN 'True' ELSE 'False' END) + CHAR(13) +
'offline: '+ MIN(CASE status & 512 WHEN 512 THEN 'True' ELSE 'False' END) + CHAR(13) +
'read only: '+ MIN(CASE status & 1024 WHEN 1024 THEN 'True' ELSE 'False' END) + CHAR(13) +
'dbo use only: '+ MIN(CASE status & 2048 WHEN 2048 THEN 'True' ELSE 'False' END) + CHAR(13) +
'single user: '+ MIN(CASE status & 4096 WHEN 4096 THEN 'True' ELSE 'False' END) + CHAR(13) +
'emergency mode: '+ MIN(CASE status & 32768 WHEN 32768 THEN 'True' ELSE 'False' END) + CHAR(13) +
'autoshrink: '+ MIN(CASE status & 4194304 WHEN 4194304 THEN 'True' ELSE 'False' END) + CHAR(13) +
'cleanly shutdown: '+ MIN(CASE status & 1073741824 WHEN 1073741824 THEN 'True' ELSE 'False' END) + CHAR(13) +
'ANSI null default: '+ MIN(CASE status2 & 16384 WHEN 16384 THEN 'True' ELSE 'False' END) + CHAR(13) +
'concat null yields null: '+ MIN(CASE status2 & 65536 WHEN 65536 THEN 'True' ELSE 'False' END) + CHAR(13) +
'recursive triggers: '+ MIN(CASE status2 & 131072 WHEN 131072 THEN 'True' ELSE 'False' END) + CHAR(13) +
'default to local cursor: '+ MIN(CASE status2 & 1048576 WHEN 1048576 THEN 'True' ELSE 'False' END) + CHAR(13) +
'quoted identifier: '+ MIN(CASE status2 & 8388608 WHEN 8388608 THEN 'True' ELSE 'False' END) + CHAR(13) +
'cursor close on commit: '+ MIN(CASE status2 & 33554432 WHEN 33554432 THEN 'True' ELSE 'False' END) + CHAR(13) +
'ANSI nulls: '+ MIN(CASE status2 & 67108864 WHEN 67108864 THEN 'True' ELSE 'False' END) + CHAR(13) +
'ANSI warnings: '+ MIN(CASE status2 & 268435456 WHEN 268435456 THEN 'True' ELSE 'False' END) + CHAR(13) +
'full text enabled: '+ MIN(CASE status2 & 536870912 WHEN 536870912 THEN 'True' ELSE 'False' END) + CHAR(13) + CHAR(13)
FROM master..sysdatabases
GROUP BY [name]



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #750201
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse