Mirroring Status Notification Email
Title
SQL Server Database Mirroring Status Notification via Email
Purpose
This solution is designed to continuously monitor the mirroring status of SQL Server databases and notify the DBA team whenever there is a failure or degraded state in the mirroring configuration.
Where Can We Use This?
- In any SQL Server environment where database mirroring is configured for high availability or disaster recovery.
- Across production, UAT, or critical test environments.
- In environments where automated, proactive monitoring and alerts are essential for database health and uptime.
What Are We Going to Achieve?
- Automated detection of unhealthy mirroring states (e.g., disconnected, suspended).
- Email alerts sent in HTML format detailing the server, database, current role, mirroring state, and partner name.
- Visibility into the mirroring configuration and quick response to issues before they impact operations.
How It Works
- A stored procedure named
MirroringStausNotificationAlertis created in themasterdatabase. - This procedure checks the
sys.database_mirroringDMV to identify unhealthy states. - If any issues are detected, a formatted HTML email is generated with relevant details.
- The procedure uses
sp_send_dbmailto send the email using a configured Database Mail profile. - A SQL Server Agent Job is scheduled to run this stored procedure every hour, ensuring continuous monitoring.
Conclusion
This solution offers a lightweight, reliable mechanism for keeping your database team informed of any mirroring issues, allowing faster remediation and increased uptime. With proper scheduling, this tool ensures you’re never caught off guard by mirroring failures.
Script:-
—– STEP 1 Create SP START —-
USE master
GO
alter PROCEDURE MirroringStausNotificationAlert
AS
BEGIN
DECLARE @EmailBody NVARCHAR(MAX)
DECLARE @Subject NVARCHAR(200)– Table variable to store mirroring status
DECLARE @MirroringStatus TABLE (
InstanceName NVARCHAR(100),
DatabaseName NVARCHAR(100),
MirroringRoleDesc NVARCHAR(50),
MirroringStateDesc NVARCHAR(50),
PartnerName NVARCHAR(255)
)– Insert mirroring status of databases in the table
INSERT INTO @MirroringStatus
SELECT
@@SERVERNAME AS InstanceName,
DB_NAME(database_id) AS DatabaseName,
CASE mirroring_role
WHEN 1 THEN ‘Principal’
WHEN 2 THEN ‘Mirror’
WHEN 3 THEN ‘Witness’
ELSE ‘Unknown’
END AS MirroringRoleDesc,
mirroring_state_desc AS MirroringStateDesc,
mirroring_partner_name AS PartnerName
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL
AND mirroring_state_desc NOT IN (‘SYNCHRONIZED’, ‘SYNCHRONIZING’) — Exclude healthy states– Check if any mirrored database has an issue
IF EXISTS (SELECT 1 FROM @MirroringStatus)
BEGIN
— Start HTML email body
SET @EmailBody =
‘<html>
<head>
<style>
table { border-collapse: collapse; width: 100%; }
th, td { border: 1px solid black; padding: 8px; text-align: left; }
th { background-color: #f2f2f2; }
</style>
</head>
<body>
<h3 style=”color: red;”>ALERT! Database Mirroring Failure Detected:</h3>
<table>
<tr>
<th>SQL Instance</th>
<th>Database</th>
<th>Role</th>
<th>Status</th>
<th>Partner</th>
</tr>’– Append database mirroring failure details
SELECT @EmailBody = @EmailBody +
‘<tr>
<td>’ + InstanceName + ‘</td>
<td>’ + DatabaseName + ‘</td>
<td>’ + MirroringRoleDesc + ‘</td>
<td style=”color: red;”>’ + MirroringStateDesc + ‘</td>
<td>’ + ISNULL(PartnerName, ‘N/A’) + ‘</td>
</tr>’
FROM @MirroringStatus– Close HTML tags and add Best Regards
SET @EmailBody = @EmailBody +
‘</table>
<br/>
<p>Regards,<br/>DBA Team</p>
</body>
</html>’– Email subject with timestamp
SET @Subject = @@servername + ‘ – SQL Server Mirroring Failure Alert (‘ + CONVERT(NVARCHAR, GETDATE(), 120) + ‘)’– Send email alert
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLDBAActivity’, — Change this to your Database Mail profile
@recipients = ‘sqldba@*****.**’, — Update with actual recipients
@subject = @Subject,
@body = @EmailBody,
@body_format = ‘HTML’ — Ensure the email is in HTML format
END
END
GO——– STEP 1 Create SP END ————– STEP 2 SQL job creation START —–
USE [msdb]
GOBEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Database Maintenance’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Database Maintenance’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’SQLMPlan_MirroringStatus’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’SQL DB Mirroring status notification via mail.’,
@category_name=N’Database Maintenance’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [MirroringStausViaMailNotification] Script Date: 4/11/2025 9:09:40 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’MirroringStausViaMailNotification’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’Use master
GO
exec MirroringStausNotificationAlert’,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’MirroringStatusNotification’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20160801,
@active_end_date=99991231,
@active_start_time=70700,
@active_end_time=190859
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO—— STEP 2 SQL job creation END —-
USE master
GO
alter PROCEDURE MirroringStausNotificationAlert
AS
BEGIN
DECLARE @EmailBody NVARCHAR(MAX)
DECLARE @Subject NVARCHAR(200)– Table variable to store mirroring status
DECLARE @MirroringStatus TABLE (
InstanceName NVARCHAR(100),
DatabaseName NVARCHAR(100),
MirroringRoleDesc NVARCHAR(50),
MirroringStateDesc NVARCHAR(50),
PartnerName NVARCHAR(255)
)– Insert mirroring status of databases in the table
INSERT INTO @MirroringStatus
SELECT
@@SERVERNAME AS InstanceName,
DB_NAME(database_id) AS DatabaseName,
CASE mirroring_role
WHEN 1 THEN ‘Principal’
WHEN 2 THEN ‘Mirror’
WHEN 3 THEN ‘Witness’
ELSE ‘Unknown’
END AS MirroringRoleDesc,
mirroring_state_desc AS MirroringStateDesc,
mirroring_partner_name AS PartnerName
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL
AND mirroring_state_desc NOT IN (‘SYNCHRONIZED’, ‘SYNCHRONIZING’) — Exclude healthy states– Check if any mirrored database has an issue
IF EXISTS (SELECT 1 FROM @MirroringStatus)
BEGIN
— Start HTML email body
SET @EmailBody =
‘<html>
<head>
<style>
table { border-collapse: collapse; width: 100%; }
th, td { border: 1px solid black; padding: 8px; text-align: left; }
th { background-color: #f2f2f2; }
</style>
</head>
<body>
<h3 style=”color: red;”>ALERT! Database Mirroring Failure Detected:</h3>
<table>
<tr>
<th>SQL Instance</th>
<th>Database</th>
<th>Role</th>
<th>Status</th>
<th>Partner</th>
</tr>’– Append database mirroring failure details
SELECT @EmailBody = @EmailBody +
‘<tr>
<td>’ + InstanceName + ‘</td>
<td>’ + DatabaseName + ‘</td>
<td>’ + MirroringRoleDesc + ‘</td>
<td style=”color: red;”>’ + MirroringStateDesc + ‘</td>
<td>’ + ISNULL(PartnerName, ‘N/A’) + ‘</td>
</tr>’
FROM @MirroringStatus– Close HTML tags and add Best Regards
SET @EmailBody = @EmailBody +
‘</table>
<br/>
<p>Regards,<br/>DBA Team</p>
</body>
</html>’– Email subject with timestamp
SET @Subject = @@servername + ‘ – SQL Server Mirroring Failure Alert (‘ + CONVERT(NVARCHAR, GETDATE(), 120) + ‘)’– Send email alert
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLDBAActivity’, — Change this to your Database Mail profile
@recipients = ‘sqldba@*****.**’, — Update with actual recipients
@subject = @Subject,
@body = @EmailBody,
@body_format = ‘HTML’ — Ensure the email is in HTML format
END
END
GO——– STEP 1 Create SP END ————– STEP 2 SQL job creation START —–
USE [msdb]
GOBEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Database Maintenance’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Database Maintenance’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’SQLMPlan_MirroringStatus’,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’SQL DB Mirroring status notification via mail.’,
@category_name=N’Database Maintenance’,
@owner_login_name=N’sa’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [MirroringStausViaMailNotification] Script Date: 4/11/2025 9:09:40 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’MirroringStausViaMailNotification’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’Use master
GO
exec MirroringStausNotificationAlert’,
@database_name=N’master’,
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’MirroringStatusNotification’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20160801,
@active_end_date=99991231,
@active_start_time=70700,
@active_end_time=190859
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO—— STEP 2 SQL job creation END —-
Sample Email Preview
When triggered, the email subject line will look like:
[YourInstanceName] - SQL Server Mirroring Failure Alert (YYYY-MM-DD HH:MM:SS)
And the body will contain a table like this:
| SQL Instance | Database | Role | Status | Partner |
|---|---|---|---|---|
| MyServer | EMPDB | Principal | DISCONNECTED | MyMirrorServer |
For any configuration or escalation, please contact the DBA Team.
Mirroring status Notification mail architecture
