I'm looking for some help setting an alert to fire when the 'mirroring_witness_state_desc' in the following query returns a 'DISCONNECTED' value. We've had incidents where the WITNESS server will randomly drop all connections to it's mirrored databases and I'd like to catch when this is happening.
The query I'm using to gather this information is:
SELECT d.name, d.database_id, m.mirroring_role_desc,
m.mirroring_state_desc, m.mirroring_safety_level_desc,
m.mirroring_partner_name, m.mirroring_partner_instance,
m.mirroring_witness_name, m.mirroring_witness_state_desc
FROM sys.database_mirroring m JOIN sys.databases d
ON m.database_id = d.database_id
WHERE mirroring_state_desc IS NOT NULL
I'd like to have this set as a job running every few hours throughout the day.
Thanks very much for any pointers!