SQL Server Database mail common issue

Problem: SQL Server Database Mail common issue

We always use SQL Server Database mail feature to create email notification for our mission critical jobs but what if they stop sending us status of jobs. Recently one of our very high priority job stops sending database email notification and we found out about it very late and it create lot of chaos. Today we discuss about how to troubleshoot database mail error.

Solution

Following are the steps which we followed to troubleshoot database email error.

Step 1 – Check database mail in SQL server.

Our first and foremost step is to check 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 we are at above window, select mail profile, enter Email address, body and subject. Once completed click on “Send Test E-Mail” button.

In our case test E-mail was a success. Now one thing is clear that this error is not due to SMTP port blocking.

Step 2 – Validate operator in SQL server

From above step one thing is clear that error is not because of SMTP port disabled. We will go further now and check that Operator in SQL Server Agent is on place or not by navigating to SQL Server Agent > Operators. Our Operator was setup and the properties (not shown below) were correct.

For us operator is there as expected. So this is also not the cause of the issue.

Step 3 – check SQL Server Agent mail profile

Both step 1 and 2 conclude us that they are nowhere responsible for the database mail issue. Now we go one step deeper and check whether “Alert System” in SQL Server agent is correctly configured or not. For this we have to navigate to SQL Server agent then right clicking on SQL Server Agent to select the Properties option.

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.

Now we got the root cause so, we enable the Mail Profile by clicking on check box “Enable Mail Profile” and also selected correct “Mail System” and “Mail Profile” or you can also do this through query.

Use the following command to enable the Database Mail Profile for SQL Server Agent.

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

Step 5 – Restart SQL Server Agent

Now we enabled SQL Mail Profile, it is best practice to restart SQL Server Agent. If you cannot no issues just test it by running a job that include notification