Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Create Mirror Alerts for All Mirrored Databases

By Jordon Pilling,

Ever come across a 64bit SQL Server with more cores than you can shake a stick at, with a but load of Mirrored databases with no mirroring alerts what so ever?

This script has 6 main parts:

  1. Delete any old DBM alerts
  2. Create a Stored Proc to create alerts for a given database, both Threshold Based alerts (which it created Default THreashold values for you) and WMI based alerts for those awkward state changes.
  3. Create a Stored Proc to Email current Mirror Status for all databases
  4. Create a Job to Run the SP in point 3 (to be used by certain alerts)
  5. Loop through all mirrored databases and call the SP in point 2
  6. Drop the Temporary Components etc

This script will create alerts for each mirrored database, the alerts created will cover:

  • Commit Overhead
  • Oldest Unsent Transaction Threshold
  • Unrestored Log Threshold
  • Unsent Log Threshold
  • Automatic Failover
  • Manual Failover
  • Mirror Connection Lost (my favourite)
  • Mirroring Suspended
  • No Quorum
  • Principal Connection Lost (my favourite)
  • Principal Running Exposed
  • Synchronizing Mirror
  • Synchronizing Principal

To use the script, simply paste it into a new query window on your principle, do a find replace on "XXXXX@YYYY.COM" and replace with your DBA address, then execute. Repeat on the Mirror.

This is version 8 of the script, based on feedback in the discussion thead.

Total article views: 3252 | Views in the last 30 days: 34
Related Articles

How get Alerts when Database Mirroring Fails

How get Alerts when Database Mirroring Fails


Mirrored Database

Mirrored Database


Database Mirroring

Database Mirroring


Set Up SQL Server Mirroring T-SQL Script

T-SQL Script to create a SQL Mirroring:- The first thing you need to do when setting up Database ...


Alert when no witness

When creating an alert

database mirroring    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones