18

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?

7
  • Is the profile from that you are sending email is private ?
    – Kin Shah
    Jun 23, 2016 at 22:03
  • Any error? What about the logs? And the mail server? Does the mail server receive any connection from sql? The question is missing lots of relevant info...
    – Paolo
    Jun 23, 2016 at 22:26
  • @kin No, it's a public email. Both emails are office 365 and the accounts are sending and receiving fine from SQL 2014. Added a couple more details to end of 1st paragraph describing the situation.
    – Dave
    Jun 23, 2016 at 23:15
  • I meant your database mail profile.
    – Kin Shah
    Jun 23, 2016 at 23:28
  • Are you able to ping/telnet SMTP server from your SQL Server 2016 box? If yes, can you try running VB script from mssqltips.com/sqlservertip/1625/… link to rule out any SMTP issue. If you receive test mail then you can be sure that it is a problem with SQL server.
    – SQLPRODDBA
    Jun 24, 2016 at 3:24

5 Answers 5

17

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:

enter image description here

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.

5
  • I had the same issue. Resolved by applying .net 3.5. Jul 31, 2016 at 21:40
  • this fixed my issue too! it was driving me insane but installing NET3.5 fixed my issue!
    – NULL.Dude
    Nov 2, 2017 at 20:13
  • This fixed the same issue for me on 2016 SP1 CU8 Apr 13, 2018 at 13:16
  • where is the DB mail executable located? Jun 22, 2018 at 19:34
  • @Dave OH JESUS THIS IS AMAZING. Just wondering... do you use LogMeIn on these servers? We've encountered bizarre situations where LogMeIn uninstalls or deletes stuff, and they refuse to admit it. My database mail at one point worked fine on one box and definitely stopped working sometime around the time we moved to LogMeIn Dec 28, 2018 at 1:22
10

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

4

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.

2
2

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

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

3
  • 2
    Instead of restarting the SQL Server instance, which isn't always feasible in a productive environment, you could try the sysmail_start_sp (Microsoft | SQL Docs) stored procedure instead. The procedure ....Starts Database Mail by starting the Service Broker objects that the external program uses.. Over the years I have observed that SQL Server Mail silently stops for no particular reason. (Feel free to add to you answer).
    – John K. N.
    Jul 1, 2020 at 6:29
  • 1
    That might help - although it was running, it was just that each attempt to send ended in failure. Perhaps sysmail_stop_sp followed by sysmail_start_sp would do the trick. Jul 1, 2020 at 6:52
  • I can confirm that sysmail_stop_sp followed by sysmail_start_sp does seem to clear the problem. Also, found interesting message in SQL error log: "An error occurred in Service Broker internal activation while trying to scan the user queue 'msdb.dbo.ExternalMailQueue' for its status. Error: 1222, State: 56. Lock request time out period exceeded." Aug 10, 2020 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.