script for Checking database online or not

• mohan.pariveda

  Ten Centuries

  Points: 1210

  More actions

  July 9, 2009 at 7:13 am

  #75501

  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

  SSC Guru

  Points: 323518

  More actions

  July 9, 2009 at 7:21 am

  #1022345

  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!