sqlserver - database state query does not trigger alerts when databases are OFFLINE

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

We would like to generate alarms when MS SQL databases are OFFLINE with custom query below, but alarms are not being generated. 

SET DEADLOCK_PRIORITY LOW  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
select rtrim(name) object, state, rtrim(state_desc) state_desc from master.sys.databases
Where state_desc NOT IN ('OFFLINE')

Resolution

1. Change the custom query as shown below:

SET DEADLOCK_PRIORITY LOW  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
select rtrim(name) object, state, rtrim(state_desc) state_desc from master.sys.databases
Where state_desc NOT IN ('ONLINE')

2. Click on Threshold object name and select state and set Condition = 6

 

Additional Information

See the following MS Article with additional database status values: MS sys.databases (Transact-SQL)