July 9, 2009 at 7:13 am
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
July 9, 2009 at 7:21 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply