We have alerts for when a witness has lost it's connection. However there have been times when we are reviewing our mirror setup and find some databases with disconnected witnesses. I have come up with this query to check if there are any db's that have a disconnected witness.
WHERE mirroring_guid is not null and mirroring_witness_state != '1'
If it returns any amount of rows then their are databases without witnesses that should have one. My boss wanted me to have something that could check if a witness has been disconnected for a certain amount of time, but I don't see any date fields in the mirroring tables.
So I just want to run this code every few hours and if it does return any amount of rows then I want it to send an email alert, otherwise if it returns nothing, do nothing. I was thinking about using an IF statement that would send an email if any rows where returned with the sp_dbmail but that feature isn't on by default and I don't want to go turning on advanced features on all our servers. Appreciate any tips, thanks.