Skip to main content
Our Tech Ideas

Configuring Database Mail in SQL Server

SQL Server Database Mail facilitates email communication directly from the database engine. This capability is critical for transmitting query results, error alarms, and reports, as well as keeping you informed about SQL Server performance. We’ll walk you through configuring Database Mail in this brief guide. We’ll walk you through every step of the process, from setting it up to generating email profiles and accounts. This blog post will provide you with the information and skills you need to effectively exploit this important feature, whether you’re a database administrator, developer, or IT professional. Take charge of your email communication today and improve your SQL Server experience!

Prerequisites to Enable Database Mail

Before Configure Database mail we must enable Service Broker for MSDB database and also need to check SQL Server Agent service running or not.

Check Service Broker status

Execute the following query to check that the service broker is enabled or not.

USE master 
go 
 
SELECT database_id AS 'Database ID', 
       NAME        AS 'Database Name', 
       CASE 
         WHEN is_broker_enabled = 0 THEN 'Service Broker is disabled.' 
         WHEN is_broker_enabled = 1 THEN 'Service Broker is Enabled.' 
       END         AS 'Service Broker Status' 
FROM   sys.databases 
WHERE  NAME = 'msdb'

If it is disabled, then enable the service broker by executing the below queries

Use master
go
alter database [MSDB] set single_user with rollback immediate
GO
alter database [MSDB] set Enable_Broker
GO
alter database [MSDB] set multi_user with rollback immediate
GO

SQL Server Agent service running status

Execute below command  to check the status of agent service

EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'

If the SQL Server Agent service is ‘Stopped’, start the SQL Server Agent service. Use SQL Server configuration manager to start it.

Or Run ‘services.msc’ to open Services. Search for SQL Server Agent. Right-click on SQL Server Agent and select Start.

Configuring Database Mail by SSMS

In Object Explorer, Expand Management and right-click the select Configure Database Mail

Welcome screen, Click Next

Select the Setup Database Mail by performing the following tasks button and click Next.

Enabling the database email feature

By default, the database email feature is not enabled. It must be enabled, simply click ‘Yes’

Creating a profile

In Profile name provide a profile name, here we have given ‘oti‘. The description is optional and click the Add button to create an SMTP account.

Creating an account

In Account name, provide Account Name, Description (optional). In Email address we are using any email, in the Display Name, we are using the same as Account Name & Reply Email (Optional). In SMTP server and Port Number, we are using sendgrid SMTP server & port.

Adding profile and account

SMTP accounts will be listed in the SMTP accounts grid view.

Configure the database email profile public

Click Next to configure the database mail profile security. We will configure the database email profile public. Any database user or role can send emails using Public profile. Users must be created in the MSDB database, and those users must be a member of a database role named DatabaseMailUserRole to send an email. We can also make the profile as a default profile. Once profile security sets, click Next to configure the system parameter.

Configure System Parameter

Configure System Parameter window, we can set a number of retry attempts, maximum attachment file size, list of file extensions that are prohibited from sending as an attachment.

Review the entire configuration and list of actions. Click Finish to complete.

You may view the report, then close.

Sending test email using SSMS

To send a test email using SSMS, Open SQL Server Management Studio >> Expand Management in SQL Server Management >> Right-click  Database Mail and select Send Test Mail.

In the Database Mail Profile drop-down, select the name of the profile used to send an email (Database Mail Profile), In the To text box, provide the email address of the email recipient and click Send Test Mail.

Click ‘OK’

Check your email inbox.

Check the Status of EMail Messages Sent With Database Mail – Click Here For TSQL

Send test mail using the T-SQL

We can also send test emails using the T-SQL code. Execute the following T-SQL script

use MSDB
go
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'oti'
  , @recipients = 'sanjay.humania@gmail.com'
  , @subject = 'Automated Test Results | Date: 17/11/2019 (Successful)'
  , @body = 'Hi please visit https://www.ourtechideas.com.'
Go

Configuring Database Mail by TSQL

Enabling the database email feature

By default, the database email feature is not enabled. It must be enabled, simply execute the below TSQL script. In the SQL Server Management Studio, execute the following statement.

Enable/disable advanced options

-- Enable / disable advanced options

sp_configure 'show advanced options', 1; -- enable or disable ( 0 or 1)
GO
RECONFIGURE;
GO

Enable/disable Database Mail XPs

-- Enable / disable Database Mail XPs

sp_configure 'Database Mail XPs', 1; -- enable or disable ( 0 or 1)
GO
RECONFIGURE
GO

Creating a profile by TSQL

To create “MyMailProfile” using the sysmail_add_profile procedure to create a Database Mail profile. Execute the statement below.

-- Create a Database Mail profile  

EXECUTE msdb.dbo.sysmail_add_profile_sp  
    @profile_name = 'oti',  -- Profile name
    @description = 'Profile used for sending outgoing notifications using Xmail.' ;  -- Description
GO

Grant the Database Mail profile access to the msdb public database role

Now use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile. Please execute the statement below.

-- Grant access to the profile to the DBMailUsers role  

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp  
    @profile_name = 'oti',  -- Profile name
    @principal_name = 'public',  
    @is_default = 1 ;
GO

Creating account by TSQL

Now Account can be enabled by using the sysmail_add_account procedure. We are going to create the account, “Gmail” using ‘smtp.gmail.com’ as the mail server and a ‘valid e-mail address as the e-mail account. Please execute the statement below.

-- To create a new Database Mail account holding information about an SMTP account
-- Create a Database Mail account  

EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'account_oti',  
    @description = 'Mail account for sending outgoing notifications.',  
    @email_address = 'Use a valid e-mail address',  -- Use a valid e-mail address from where mail will be send
    @display_name = 'Automated Mailer',  
    @mailserver_name = 'smtp.xxxx.com', -- SMTP
    @port = xxx, -- Port
    @enable_ssl = 1,
    @username = 'Use a valid e-mail address', -- Use a valid e-mail address from where mail will be send
    @password = 'Use the password for the e-mail account above' ;  
GO

Adding profile and account by TSQL

Now execute the sysmail_add_profileaccount procedure, to add the Database Mail account with the Database Mail profile. Please execute the statement below.

-- Add the Database Mail account to the Database Mail profile
 
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'oti',  -- Profile
    @account_name = 'account_oti',  -- Account
    @sequence_number =1 ;  
GO

Deleting profile and account

So, now database mail configuration is done successfully. If for any reason any above steps return an error, use the below TSQL to roll back the changes.

EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = 'xxx'
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp @profile_name = 'yyy'
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'zzzz'
EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'kkkk'

Check that the existing email profile

To check that the existing email profile is configured on the server.

-- Check that the e-mail profile is configured 

SELECT [sysmail_server].[account_id],
       [sysmail_account].[name] AS [Account Name],
       [servertype],
       [servername] AS [SMTP Server Address],
       [Port]
FROM [msdb].[dbo].[sysmail_server]
     INNER JOIN [msdb].[dbo].[sysmail_account] ON [sysmail_server].[account_id] = [sysmail_account].[account_id];

Database Mail queues

To check the status of Database Mail queues.

-- Displays the status of Database Mail queues.

EXECUTE msdb.dbo.sysmail_help_status_sp ;  
GO

To start Database Mail queues

-- To start Database Mail queues

USE msdb ;  
GO  
EXECUTE dbo.sysmail_start_sp ;  
GO

To stop Database Mail queues

-- To stop Database Mail queues

USE msdb ;  
GO   
EXECUTE dbo.sysmail_stop_sp ;  
GO

To find failed mails

-- failed mailserver_name

use msdb
go

SELECT * FROM sysmail_faileditems   
ORDER BY last_mod_date DESC