Alerts for Database Mirroring

Question

Post reply

Alerts for Database Mirroring

wak_no1

SSCrazy

Points: 2400
More actions
July 15, 2013 at 8:30 am

#279913

Hi
I'm trying to get SQL Server to alert me via email when the Primary instance loses connection with Mirrored instance (i.e. mirroring becomes suspended/inactive)
So far, the methods I've found require the Service Broker to be enabled on the Mirrored DB, unfortunately, this cannot be enabled in this scenario.
Is there any other simple way that I can simply set MSSQL to notify me (via email) when Mirroring isn't active?
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 1

Hi,

If you run the following query:

select * from sysmessages where description like '%mirror%'

You'll get a list of error messages that relate to mirroring. Looks like error 1454 will be fired for the situation you describe. Using that error no. and the SQL Server Agent you can setup an alert for when that error no. occurs.

To do that, right-click alerts, new alert, and set the configuration for the error nos. you want to be alerted to. You'll need to configure an operator if you haven't already.

Thanks,

Simon

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

arnipetursson SSCertifiable Points: 6557 More actions · Answer 2

Below is code my colleagues have used to check status of mirroring:

SELECT distinct d.name,

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 m.mirroring_role_desc is not null

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

I wrote a query that solves this problem for our SQL Monitor tool. You should be able to modify it for your own uses. Here's the query.[/url]

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

wak_no1 SSCrazy Points: 2400 More actions · Answer 4

Hi

Problem with that is that the alert will not trigger because the event does not get written to the logs, this is something I've tested.

Effectively, I just need an alert to trigger when the Principal database loses connection the Mirroring database, in the event of a network issue for example.