Troubleshoot SQL Server Agent Notifications and Database Mail

By:   |   Comments (9)   |   Related: > SQL Server Agent


Recently when we came into the office and started reviewing daily alerts and we found that one of our business critical production environments did not send an alert for one of the critical SQL Server Agent Jobs. This SQL Server Agent Job feeds data to our FINANCE application dashboard for the current trends. What should we review in SQL Server to determine the root cause of the issue?


To resolve the issue we used the following steps:

Step 1 - Validate SQL Server Database Mail

First test to see if the system is able to send email using the Database Mail profile in SQL Server Management Studio by navigating to Management > Database Mail, then right clicking on Database Mail and selecting the "Send Test E-Mail..." option.


Once the interface loads, select the correct Database Mail Profile, enter the To email address, Subject and Body. Once complete press the "Send Test E-Mail" button.


In our case, the SQL Server Database Mail test was successful hence it shows the ExternalMailQueue is enabled and there is no issue with the SMTP port being blocked by a firewall rule.

To check the external mail queue is enabled, you can use the following code:

SELECT * FROM msdb.sys.service_queues
WHERE name = N'ExternalMailQueue'


Step 2 - Validate SQL Server Operator

As the above test was successful, we next looked to see if someone had removed the Operator from SQL Server Agent in SQL Server Management Studio by navigating to SQL Server Agent > Operators. Our Operator was setup and the properties (not shown below) were correct.


In our case, the operator still exists, the properties were correct, etc., so this was not the root cause of our issue.

Step 3 - Validate the SQL Server Agent Mail Profile

After the above two steps were not the issue, we concluded the issue was with SQL Server Agent. We thought about restarting SQL Server Agent, but since Jobs were completing successfully we started looking into the SQL Server Agent Properties. The Properties can be found in SQL Server Management Studio by navigating to SQL Server Agent then right clicking on SQL Server Agent to select the Properties option.


As notification is tied to the Alert system we looked at the “Alert System” tab. Looking at the properties we were able to figure out the issue. It was very clear that even though we have a Database Mail Profile setup it was not enabled for SQL Server Agent as shown below.


Step 4 - Enable Mail Profile in SQL Server Agent

To correct the situation, check the “Enable Mail Profile” check box and select the Mail System and Mail Profile in the interface as shown below or use the following command to enable the Database Mail Profile for SQL Server Agent.

USE [msdb]
EXEC msdb.dbo.sp_set_sqlagent_properties
                 @databasemail_profile=N'DBAxxxxx', -- put your database mail profile here


Step 5 - Restart SQL Server Agent

Once the Database Mail Profile is enabled it is a best practice to restart the SQL Server Agent service and then test the notification by running a job that includes notification. However in our case we didn’t restart the service, but tested the notification by running a job.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Varinder Sohal Varinder Singh is a SQL Server production DBA in the Boston area with 20+ years of database, SSIS, SSAS and SSRS administration experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, January 10, 2024 - 2:01:20 PM - Tarcisio Back To Top (91848)
Thank you my friend, it helped me a lot.

Wednesday, June 14, 2023 - 11:21:41 AM - Guitarzan8 Back To Top (91287)
This worked great. Solved my issue too! Thank you.

Friday, April 14, 2023 - 9:15:16 AM - Tamara Back To Top (91108)
This worked great. Solved my issue.

Friday, March 10, 2023 - 1:19:45 PM - Gord Back To Top (90996)
One more for the list.... if you have multiple email addresses in the Operator - they need to be separated with a semi colon. NOT a comma!

Tuesday, February 28, 2023 - 1:35:34 PM - M Back To Top (90961)
Thank you! very helpful.

Monday, October 10, 2022 - 6:16:48 AM - Sean Walsh Back To Top (90575)
Thanks a million, your article solved the problem for me!

Monday, May 31, 2021 - 1:44:02 PM - Brinda Back To Top (88768)
Hello Sir I read your advice. I have an issue.when I create users on my system on Microsoft dot net. My users do not get notification of their username and password to access a system on Microsoft dot .

Can you please advise me how to fix this on sql database please. Thanks

Thursday, July 12, 2018 - 1:34:42 PM - Andy K. Back To Top (76627)

 YOU are my hero! Thank you!!! 

Friday, February 12, 2016 - 12:31:15 AM - Ravindra Kumar Back To Top (40655)

 Awesome ..:) WOW

Actually we need html formatted mail.

Could you please suggest how we send it?



Ravindra Kumar


get free sql tips
agree to terms