Press "Enter" to skip to content

Set up Database Mail & Alerts

Deirdre O'Leary Deirdre O'Leary
January 16, 2022
Leave a Comment

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):


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
GO
EXEC 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

Categories:SQL Server Administration

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *