Database Mail 101 Troubleshooting

by | Nov 12, 2018 | SQL Server | 0 comments

Shawn Meyers (@1dizzygoose), Principal Architect 

This blog covers the basics of troubleshooting Database Mail, and is more tailored to issues related to setup rather than installations that have been working for a while and all of a sudden have stopped. In the coming weeks, there will be two more blog posts from House of Brick regarding more advanced troubleshooting for Database Mail, but this blog gets things started with an introduction to troubleshooting Database Mail.

If you have many SQL Server instances you may already have a script to setup Database Mail, and if you use a script, you probably don’t have too many setup issues. However, if you are new, or just have a few servers, you may be setting it up manually. At the end of this blog, there is a script we use to set Database Mail defaults to anonymous access.

Is it Enabled?

First to check to make sure that Database Mail is enabled. Look in ‘sys.configurations’ for the setting Database Mail XPs, and if it is set to 0, Database Mail is not enabled.

, Database Mail 101 Troubleshooting

The value column shows ‘0’, so Database Mail is off. To enable it, you can run a simple script to change the value to ‘1’, as shown below.

EXEC sp_configure 'show advanced options', 1 

EXEC sp_configure 'Database Mail XPs', 1 


An alternative for enabling Database Mail is under Management => Database Mail => Configure Database Mail. Select any of the configuration options, and you will see a message stating that Database Mail is not available and asking if you want to enable it (as shown in the figure below).

, Database Mail 101 Troubleshooting

Next, the most common error we run into is that Database Mail has stopped for some reason. To start Database Mail, run the following script.

EXEC msdb.dbo.sysmail_start_sp ;


If there are configuration errors, the script will show you an error, which points you in the direction of the issue.

Now check the log. Database Mail provides a decent logging system, but there are things it isn’t good at, which will be discussed in more detail in future posts describing its more advanced features. However, most basic items are logged properly as shown below.

, Database Mail 101 Troubleshooting

, Database Mail 101 Troubleshooting

Profile and Accounts
Next, check to ensure the profile and users are setup correctly. Since I have never seen a profile setup incorrectly, I’m not going to cover it in this blog.

On the other hand, I see issues with accounts all the time. Besides obvious typos in the email address or SMTP server fields, most issues revolve around authentication. By default, Database Mail assumes you are sending emails to a SMTP server, which accepts anonymous messages. Inside an organization’s network this used to be a very common practice, but today it is becoming less and less so. Some organizations no longer have email servers locally, and the SMTP relay is in the cloud somewhere, and as such requires authentication or whitelisting.

For information about authentication, you will need to talk to your email admin who should be able to explain how to setup SMTP. If you are using a mail server, and can connect with AD credentials, then Database Mail will connect with SQL Server service account when you select ‘Windows Authentication’. In the screen shot below, you can see what information is needed for a mail service like Gmail to connect. Notice using the port number and Gmail requires a secure connection. While the SSL label is not accurate, as Gmail requires TLS connections, the concept is similar and everyone understands SSL.

, Database Mail 101 Troubleshooting

Many IT devices can send SMTP emails when something goes wrong, but some can only be used with anonymous messages. Organizations need to secure their email servers, so they use a process called whitelisting. This is basically a network rule, or rule on the email server, that only allows anonymous SMTP connections from certain addresses or hostnames. Your email administrators will know if this is the case for your organization, so you will most likely need to open a ticket with them to add your SQL Servers to the whitelist. If all of your servers are on a dedicated network subnet, you may be able to get the whole subnet listed, but expect to have to add each server individually. If this is the case at your organization, you need to add a whitelisting step to your build checklist, or you may forget about it at the worst possible time.

To validate that things are working properly and are not being blocked, a simple Telnet test can confirm if your SQL Server can reach the SMTP server.



This command should connect to the mail server. If it times out or doesn’t respond, something is blocking your access to the email server, and nothing you do inside of SQL Server will fix the issue. You will need to work with your network and email administrators to resolve the issue.

If it does connect, then type the following command:



You should receive feedback from the mail server, which will tell you everything is working correctly and the problem isn’t in communications with the mail server.

SQL Agent Enabled

If Database Mail is working, but your SQL Agent jobs are not alerting you via email, the first thing you need to do is verify that the SQL Agent has a mail profile enabled. If you removed a profile recently, and if it was the one SQL Agent was using, SQL Agent jobs will stop sending emails.

To verify, the step is as simple as ensuring the ‘Enable mail profile’ check box is checked, and confirming a valid mail profile is selected.

, Database Mail 101 Troubleshooting

Testing Database Mail

Make sure you test Database Mail from both of the ‘Sent Test Email’ options under the Database Mail configuration as well as send one via SQL Server Agent from a job result. The Database Mail script at the end of this post creates a test job for just this purpose.

Migrating Database Mail

Another common issue is migrating a working Database Mail configuration from an old server to a new server. The good news is that someone has already written a blog post about this, and you can read it here.

Database Mail Setup Script

USE msdb

DECLARE @profile_name sysname,
        @account_name sysname,
        @SMTP_servername sysname,
        @email_address NVARCHAR(128),
        @display_name NVARCHAR(128),
        @String NVARCHAR(2000);

SET @profile_name = 'Default'; -- select profile name
SET @account_name = 'SQLAdmin'; -- select account and operator name
SET @display_name = @@ServerName; --set servname to be used for display
SET @email_address = ''; -- INSERT EMAIL ACCT HERE

--enable DBmail
EXEC sp_configure 'show advanced options', 1

EXEC sp_configure 'Database Mail XPs', 1

EXEC sp_configure 'Agent XPs',1

--service broker is required for dbmail to work
IF (SELECT Is_Broker_Enabled FROM master.sys.databases WHERE name = 'msdb') = 0
      ALTER DATABASE msdb set restricted_user with rollback immediate
      ALTER DATABASE msdb set multi_user with rollback immediate
--create the account and profile
IF (SELECT 1 FROM [msdb].[dbo].[sysmail_account] WHERE name = @account_name) IS NULL
      EXECUTE msdb.dbo.sysmail_add_account_sp
              @account_name = @account_name,
              @description = 'Default Mail Account',
              @email_address = @email_address,
              @display_name = 'DBA Alerts',
              @replyto_address = @email_address,
              @mailserver_name = @SMTP_servername

      EXECUTE msdb.dbo.sysmail_add_profile_sp
              @profile_name = @profile_name,
              @description = 'Default Profile'

      EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
              @profile_name = @profile_name,
              @account_name = @account_name,
              @sequence_number = 1

      EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
              @profile_name = @profile_name,
              @principal_name = 'public',
              @is_default = 1

--create an operator with same name as the account
IF NOT EXISTS(SELECT name FROM msdb.dbo.sysoperators WHERE name = @account_name)
      EXEC msdb.dbo.sp_add_operator @name=@account_name,
--enable dbmail for SQL Server Agent alerts
IF (SELECT 1 WHERE @@version LIKE ('%SQL Server 201%')) = 1
             EXEC msdb.dbo.sp_set_sqlagent_properties @databasemail_profile=@profile_name
             EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
             EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', @profile_name
             EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
--set failsafe operator
EXEC master.dbo.sp_MSsetalertinfo @failsafeoperator=@account_name, @notificationmethod=1

--Create test job to test dbmail
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT [name] FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

IF EXISTS(SELECT [name] FROM msdb.[dbo].[sysjobs] WHERE [name] = 'TestJob')
EXEC msdb.dbo.sp_delete_job @job_name=N'TestJob'

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'TestJob',
             @description=N'No description available.',
             @category_name=N'Database Maintenance',
             @notify_email_operator_name=@account_name, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'One',
             @os_run_priority=0, @subsystem=N'TSQL',
             @command=N'SELECT GETDATE()',

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_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
GOTO EndSave
-- run test job
EXEC msdb..sp_start_job 'TestJob'

Please note: this blog contains code examples provided for your reference. All sample code is provided for illustrative purposes only. Use of information appearing in this blog is solely at your own risk. Please read our full disclaimer for details.


Submit a Comment

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