Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


script for Checking database online or not


script for Checking database online or not

Author
Message
mohan.pariveda
mohan.pariveda
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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
Lowell
Lowell
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19162 Visits: 39461
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search