SQL Server Database Mail & Agent Alerts are a free, easy way to get notified of corruption, errors & job failures before users report problems. The following is my process for setting up database mail on new SQL servers.
For more information, see Brent Ozar’s site (where I got pretty much all of the information for this process):
- How to Configure Database Mail
- How to Set Up a SQL Server Agent Operator
- Failsafe Operator Configuration
- How to Configure SQL Server Agent Alerts
First, the service account for the SQL Server must be given Send As permission in Exchange for the email address that Database Mail will use to send alerts (e.g. dbmail@mycompany.com). See Microsoft Docs for more information on assigning permissions.
In SSMS, check that Service Broker & Database Mail XPs are enabled on the SQL Server:
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
-- is_broker_enabled should equal 1 (default)
SELECT * FROM sys.configurations WHERE name = 'Database Mail XPs';
-- value_in_use should equal 1
If Database Mail XPs are disabled, take note of any other values will be affected by running RECONFIGURE as these may need to be fixed after enabling Database Mail XPs:
SELECT * FROM sys.configurations where value <> value_in_use;
Execute the following to enable Database Mail XPs:
EXEC sp_configure 'show advanced options', '1';
GO
RECONFIGURE
GOEXEC sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
Set up Database Mail:
- Configure Database Mail using the wizard in SSMS by expanding the Management node & right-clicking Database Mail.
- Create a profile & add a new database mail account:
- Email address: dbmail@mycompany.com
- Display name: SQL Server name
- Reply email address: dbmail@mycompany.com
- Server name: Mail server name
- Port: Mail Server port (probably 25)
- Authentication: Windows Authentication using Database Engine service credentials.
- Specify the profile as public or private as required (see Microsoft Docs for more information on the differences).
- After creating the profile, send a test email by right-clicking Database Mail.
- If any errors occur or the email is not received, review the Database Mail log for error details.


Set up Operators:
- Right-click SQL Server Agent Operators in SSMS & select “New Operator”.
- Set the operator name & email address.
- To start with, I usually set up two operators:
- One for the DBA team (DBA@mycompany.com),
- Another for the Support & DBA teams (Support@mycompany.com;DBA@mycompany.com).
- Additional operators can be added later as required.


Enable Database Mail on the SQL Agent & set up Failsafe Operator:
- Right-click SQL Server Agent in SSMS & choose Properties.
- In the Alert System tab, set the following:
- Tick “Enable mail Profile”.
- Tick “Enable fail-safe operator” & set the appropriate Operator.
- Tick E-mail for the fail-safe operator notification type.
- Restart the SQL Server Agent.
Test the Database Mail setup for the SQL Agent by setting up a test job:
- Give it a single step named “Hi” which executes
PRINT 'Hi'
. - Set the step to notify the DBA operator on completion of the job.
- Run the job & verify that an email is received.
- Delete the test job.
Set up Agent Alerts:
- Run the below script in SSMS to configure SQL Server alerts to be sent to the appropriate operator (e.g. SupportAndDBA).
- Test the alerts by executing:
RAISERROR (N'Test alert!', 20, 1) WITH LOG;
Set existing user jobs to send notifications on failure to the appropriate operator.
Script to configure SQL Server alerts:
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 016',
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=900,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 016', @operator_name=N'SupportAndDBA', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 017',
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=900,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 017', @operator_name=N'SupportAndDBA', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 018',
@message_id=0,
@severity=18,
@enabled=1,
@delay_between_responses=900,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 018', @operator_name=N'SupportAndDBA', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 019',
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=900,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 019', @operator_name=N'SupportAndDBA', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 020',
@message_id=0,
@severity=20,
@enabled=1,
@delay_between_responses=900,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 020', @operator_name=N'SupportAndDBA', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 021',
@message_id=0,
@severity=21,
@enabled=1,
@delay_between_responses=900,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 021', @operator_name=N'SupportAndDBA', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 022',
@message_id=0,
@severity=22,
@enabled=1,
@delay_between_responses=900,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 022', @operator_name=N'SupportAndDBA', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 023',
@message_id=0,
@severity=23,
@enabled=1,
@delay_between_responses=900,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 023', @operator_name=N'SupportAndDBA', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 024',
@message_id=0,
@severity=24,
@enabled=1,
@delay_between_responses=900,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 024', @operator_name=N'SupportAndDBA', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 025',
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=900,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 025', @operator_name=N'SupportAndDBA', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error Number 823',
@message_id=823,
@severity=0,
@enabled=1,
@delay_between_responses=900,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 823', @operator_name=N'SupportAndDBA', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error Number 824',
@message_id=824,
@severity=0,
@enabled=1,
@delay_between_responses=900,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 824', @operator_name=N'SupportAndDBA', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error Number 825',
@message_id=825,
@severity=0,
@enabled=1,
@delay_between_responses=900,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 825', @operator_name=N'SupportAndDBA', @notification_method = 1;
GO
Be First to Comment