SQL Server 2016 DB Mail Not Sending

Question

I'm on SQL Server 2016 and having a blast... my DB Mail isn't sending and I'm running out of places to look. I double checked the SQL account permissions to the DBmail executable - it has read and execute. I entered a rule for the firewall outbound port 587. I tried another mail account and profile with the same unsent issues. The only entries in the logs (db mail logs) are starting and ending of the service. There are no errors anywhere that I can find. The emails appear to simply enter the send queue and never leave it. The accounts can send and receive email on their own and from a SQL Server 2014 instance on another machine.

I've got a queue of items with sent status "unsent" and checked all the normal places with expected results in all of them, aside from a long queue of unsent mail:

SELECT * FROM msdb..sysmail_event_log order by log_id DESC

SELECT * FROM dbo.sysmail_mailitems

SELECT * FROM dbo.sysmail_sentitems

USE msdb
SELECT sent_status, *
FROM sysmail_allitems

 SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

 EXECUTE msdb.dbo.sysmail_help_status_sp

I have tried turning it off and on again... so did I miss a DMV etc. that could shed light on this situation? Is this a known issue with SQL Server 2016 that I haven't in my searches? Any other possible steps to get this mail sent?

Answer 1

On a whim, when double checking permissions, I double clicked on the actual DB mail executable. The result on the SQL Server 2014 machine was an empty command window. On SQL Server 2016 clicking the DB Mail executable presented this message:

I cannot find this requirement anywhere in the SQL Server 2016 documentation, yet it is clearly a requirement. DB Mail is functioning perfectly with no other changes than installing .NET 3.5.

Answer 2

According to Microsoft Support, there's a bug in SQL server 2016 Setup that causes the database mail not to work without .net 3.5

There's a work around by creating a DatabaseMail.exe.config file in the same folder where the DatabaseMail.exe exists (Binn folder) write the following to the file and save with utf-8 encoding

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup useLegacyV2RuntimeActivationPolicy="true"> 
<supportedRuntime version="v4.0"/>     
<supportedRuntime version="v2.0.50727"/>
</startup>
</configuration>

Source: FIX: SQL Server 2016 Database Mail does not work on a computer that does not have the .NET Framework 3.5 installed

Answer 3

Take alook at this instead. This way you only need to copy a file instead of installing .net 3.5. Need to install CU1 first. Check here for more details.

Answer 4

Besides already mentioned causes, it is also important to activate e-mail profile at SQL Server Agent level as indicated here:

• Right Click SQL Server Agent > select Properties

• Select Alert System in the left pane

• Checkmark > Enable mail profile

• Verify Mail system: Database Mail
• Verify Mail Profile: SQLAlerts
• Checkmark > Include body of e-mail in the notification message
• Click OK.
• Restart Agent.

Answer 5

Try Restarting SQL Server

Yesterday, after running for months with no problems, SQL mail suddenly stopped sending mail for no apparent reason, or rather each attempt ended in failure, as did retries. I assumed initially it was a problem with the destination ( it was specific to one particular mail server ), and asked if the password had been changed - answer no.

Eventually, after using powershell to prove to myself the connection details and credentials were ok, I restarted SQL server, and everything started working again.

As for what caused it : there had just been a moderately large number of email sent to the destination in a single batch - around 400 emails, so that apparently caused the issue. About half of these were sent before it started failing.

If restarting SQL is problematic, you could try sysmail_stop_sp / sysmail_start_sp ( per suggestion in comments ).