Sending SQL alert when WITNESS is disconnected

89 Views Asked by At

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!

0

There are 0 best solutions below