How does the sqlserver probes "Database_state" checkpoint work?

Document ID : KB000012563
Last Modified Date : 14/02/2018
Show Technical Document Details
Question:

How does the sqlserver probes "Database_state" checkpoint work and what are the default threshold values?

Environment:
This is general information and is applicable to most versions of UIM.
Answer:

The default configuration has the sqlserver probe alarm when the database state is not equal to 0 (ONLINE).

 

The enumeration is based on the SQL statement below.  The enumeration starts at zero.

0 is the first enumeration that the database state is ONLINE.

2 RECOVERING

3 RECOVERY_PENDING

4 SUSPECT

5 EMERGENCY

6 OFFLINE

 

The specific query that the database_state checkpoint uses is below:
select rtrim(name) object, state = CASE

when status & 32 = 32 then 6

when status & 64 = 64 then 3

when status & 262144 = 262144 then 3

when status & 128 = 128 then 2

when status & 256 = 256 then 4

when status & 512 = 512 then 6

when status & 2048 = 2048 then 5

when status & 4096 = 4096 then 5
when status & 32768 = 32768 then 5
else 0

END, state_desc =

CASE

when status & 32 = 32 then 'OFFLINE'

when status & 64 = 64 then 'RECOVERY_PENDING'

when status & 262144 = 262144 then 'RECOVERY_PENDING'

when status & 128 = 128 then 'RECOVERING'

when status & 256 = 256 then 'SUSPECT'

when status & 512 = 512 then 'OFFLINE'

when status & 2048 = 2048 then 'EMERGENCY'

when status & 4096 = 4096 then 'EMERGENCY'

when status & 32768 = 32768 then 'EMERGENCY'

else 'ONLINE'
END
from master..sysdatabases