SQL Sentry template for Monitor database state (<> 0 - Offline/recovering...)

Philn

Does anyone has a template for SQL Sentry to monitor the database state i.e Off-Line, recovery, anything status <> 0 ?

Thanks in advance.

jeremymayfield

SELECT
name AS DatabaseName,
state AS StateCode,
state_desc AS StateDescription,
CASE state
WHEN 4 THEN 'CRITICAL' -- SUSPECT
WHEN 5 THEN 'CRITICAL' -- EMERGENCY
WHEN 3 THEN 'HIGH' -- RECOVERY_PENDING
WHEN 2 THEN 'HIGH' -- RECOVERING
WHEN 1 THEN 'MEDIUM' -- RESTORING
WHEN 6 THEN 'MEDIUM' -- OFFLINE
ELSE 'LOW'
END AS AlertSeverity,
recovery_model_desc AS RecoveryModel,
log_reuse_wait_desc AS LogReuseWaitReason,
GETDATE() AS CaptureTime
FROM sys.databases
WHERE state <> 0
AND source_database_id IS NULL -- Exclude database snapshots
ORDER BY state, name;