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.
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.
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