Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Monitoring SQL Server Database Mirroring with Email Alerts

By:   |   Last Updated: 2009-10-15   |   Comments (9)   |   Related Tips: More > Database Mirroring

The number of Database Mirror pairs in our enterprise is increasing at an alarming rate. With the ease of setup, resilience and ability to seamlessly failover and failback, database mirroring has taken over from log shipping as the DR solution of choice. We need a basic script that we can execute on all servers participating in mirroring that will alert us if any Principals or Mirrors are in an abnormal state. The script needs to work on both Principal and Mirror server.

The Catalog View sys.database_mirroring contains one row for each database in the instance of SQL Server and also contains state information of all mirrored databases.  We'll query this Catalog View and raise an email alert for each mirrored database that we find in an abnormal state. We don't utilize a witness server in any of our mirrored pairs so we rely on manual failover.


  1. A valid Database mail profile
  2. A valid login that has permission to send email i.e. a member of the DatabaseMailUserRole role in the msdb database
  3. At least one pair of mirrored databases to monitor

The Script

Substitute in the below script your Database Mail profile and a suitable email address to receive the alerts.





DECLARE @databases TABLE (DBid INT, mirroring_state_desc VARCHAR(30))


-- get status for mirrored databases

INSERT @databases

SELECT database_id, mirroring_state_desc

FROM sys.database_mirroring

WHERE mirroring_role_desc IN ('PRINCIPAL','MIRROR')



-- iterate through mirrored databases and send email alert

WHILE EXISTS (SELECT TOP 1 DBid FROM @databases WHERE mirroring_state_desc IS NOT NULL)


SELECT TOP 1 @DbId = DBid, @State = mirroring_state_desc

FROM @databases

SET @string = 'Host: '+@@servername+'.'+CAST(DB_NAME(@DbId) AS VARCHAR)+ ' - DB Mirroring is '+@state +' - notify DBA'

EXEC msdb.dbo.sp_send_dbmail 'valid_mail_profile', '[email protected]', @body = @string, @subject = @string

DELETE FROM @databases WHERE DBid = @DbId



--also alert if there is no mirroring just in case there should be mirroring :)

SELECT @DbMirrored = COUNT(*)

FROM sys.database_mirroring

WHERE mirroring_state IS NOT NULL

IF @DbMirrored = 0


SET @string = 'Host: '+@@servername+' - No databases are mirrored on this server - notify DBA'

EXEC msdb.dbo.sp_send_dbmail 'valid_mail_profile', '[email protected]', @body = @string, @subject = @string



To verify the alert let's pause mirroring through the SQL Server Management Studio (SSMS). In Object Explorer right click your mirrored database -> Properties -> Mirroring option -> Pause button as follows:

Now, if the query finds a mirrored database in an abnormal state as shown below, it will send out an alert email alert:

The script will also output the following information to notify you that an alert has been sent to be processed by Database Mail.

Mirroring Alert Message


Host: SERVERXXX.MirrorTest - DB Mirroring is Suspended - notify DBA


Mail queued.



Mirroring Alert Message


Host: SERVERXXX.sp_config_ssp2 - DB Mirroring is DISCONNECTED - notify DBA


Mail queued.


Email received that clearly shows the host name, database name and the abnormal state of the mirroring so that the DBA team can investigate further.

Next Steps

Last Updated: 2009-10-15

get scripts

next tip button

About the author
MSSQLTips author Alan Cranfield Alan Cranfield is a versatile SQL Server DBA with over 10 years experience managing critical systems in large distributed environments.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Monday, February 16, 2015 - 11:37:10 AM - Rajan Dubey Back To Top

how to implement this query for alert.

Friday, November 07, 2014 - 11:59:49 AM - smt Back To Top

Where should I execute the above code?

Monday, August 13, 2012 - 3:38:37 AM - Marko Schustek Back To Top

Thanks for that great Script. Saves me a lot of work!





Wednesday, March 21, 2012 - 2:36:08 PM - Laura Back To Top

In our company WMI is disabled and thus I cannot do anything through WMI.  I am trying to find examples of Mirror Alerts that do not use WMI.

Sunday, January 08, 2012 - 4:11:38 AM - Praveen Singh Back To Top

Hey Alan, thanks for your quick way to get the alert. I was wondering can I add multiple email addresses to receive the alert?

Thursday, October 15, 2009 - 12:10:55 PM - --cranfield Back To Top

yes, this tip is mentioned twice in my tip. Its a good one!

Thursday, October 15, 2009 - 12:08:17 PM - --cranfield Back To Top

Thanks for reading.  True, there are built-in alerts but they require some configuring and the document link you sent will show you how to do that.  I'm just showing DBAs another a way of configuring a quick dynamic alert when that all you need.

 Monitoring performance of your mirroring is a deeper subject.

Thursday, October 15, 2009 - 11:29:58 AM - admin Back To Top

Here is another tip that talks about using WMI as referred to in the above post



Thursday, October 15, 2009 - 8:34:21 AM - ptheriaultdba Back To Top

While I agree with you that db mirroring must be monitored, I completely disagree with how your getting it done.  There are already built in alerts for this.  Just create an alert using WMI provider and you can query any state for the db mirror change.  Here is the link to how is should be done.  A DBA should also be looking for unsent and unrestored log thresholds...





Learn more about SQL Server tools