Enabling and Configuring Database Mail in SQL Server

One great feature of SQL Server is the ability to get alerts when there are issues.  The alert process can send email notifications using Database Mail after you have configured your SMTP settings and setup your jobs and alerts to use this account.

Database Mail is not active by default. To use Database Mail, you must explicitly enable Database Mail by using either the Database Mail Configuration Wizard, the sp_configure stored procedure, or by using the Surface Area Configuration facet of Policy-Based Management.

Below is the step by step procedure for Enabling and configuring Database Mail in SQL Server.

Step 1: Create Profile and Account

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node.

This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:

Enabling and configuring Database Mail in SQL Server

Enabling and configuring Database Mail in SQL Server

Enabling and configuring Database Mail in SQL Server

Enabling and configuring Database Mail in SQL Server

Enabling and configuring Database Mail in SQL Server

Enabling and configuring Database Mail in SQL Server

Enabling and configuring Database Mail in SQL Server

Enabling and configuring Database Mail in SQL Server

Enabling and configuring Database Mail in SQL Server

Enabling and configuring Database Mail in SQL Server

Step 2: Configure Email

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Enabling and configuring Database Mail in SQL Server
Step 3: Send Email

After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USE msdb
GO
EXEC sp_send_dbmail @profile_name='Email Profile',
@recipients='test@Example.com',
@subject='Test message',
@body='This is the body of the test message.'

Enabling and configuring Database Mail in SQL Server

Quick troubleshooting queries for Database Mail

Check to see if the service broker is enabled (should be 1):
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'
Check to see if Database Mail is started in the msdb database:
EXECUTE dbo.sysmail_help_status_sp
…and start Database Mail if necessary:
EXECUTE dbo.sysmail_start_sp
Check the status of the mail queue:
sysmail_help_queue_sp @queue_type = 'Mail'
Check the Database Mail event logs:
SELECT * FROM sysmail_event_log
Check the mail queue for the status of all items (including sent mails):
SELECT * FROM sysmail_allitems

<<Click here to see all posts>>

Leave a Reply

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

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.