SQL Server 2016 – Database mail queued but not sending

Have you installed SQL Server 2016, enabled Database Mail and not been able to send a test email?  Does the email queue show a list of queued mail items but no entries in the Database Mail log?  This may help.

I installed SQL Server 2016 with CU1, used a domain user as the owner of the SQL Server accounts and gave that domain user local administrator privileges. I turned on database mail and created a profile and email account to send mail. When I attempted to send a test email, no test email ever arrived. Hmm, time to start troubleshooting.

First, I checked the Database Mail log. This was accomplished by going to the Object Explorer window, expanding ‘Management’ and right clicking on ‘Database Mail’ and choosing ‘View Database Mail Log’:

database_mail_log

How to view SQL Server Database Mail log

No entries. Hmmm.

Next was to see what MSDB thought about the process. Querying MSDB using the built-in mail stored procedures and system tables yielded little results:

-- This one told me that Database Mail was started
EXEC msdb.dbo.sysmail_help_status_sp;
 
-- Here I learned that there were 5 items queued and the last times I tried sending mail
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
 
-- This confirmed none of the email was sent
SELECT * FROM msdb.dbo.sysmail_sentitems;
SELECT * FROM msdb.dbo.sysmail_unsentitems;
 
-- Is Service Broker enabled? It has to be to send mail
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
 
-- I tried stopping and restarting the Database Mail exe
EXEC msdb.dbo.sysmail_stop_sp;
EXEC msdb.dbo.sysmail_start_sp;

The domain account responsible for running the SQL Server service and agent was a local administrator. I made it an instance administrator and restarted the instance. Still no success. And since I (and the service accounts) were both in the ‘sysadmin’ server role, permissions and ownership in the MSDB database role ‘DatabaseMailUserRole’ were not the issue.

My last attempt was to check the Database Mail executable directly. A Windows search for ‘databasemail’ showed it to be in “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn”. Double clicking (or right click and ‘Run As Administator’) the executable showed me that .Net 3.5 was not installed:

netframework3_5

.Net Framework 3.5 is not installed

Aha!

This was a Windows Server 2012 R2 box, so I had to add .Net 3.5 framework.  Which meant I had to dig up the Windows install media (there may be ways to do it without but I didn’t dig that deep in Bingle).

Opening Control Panel and choosing ‘Turn Windows features on or off’ brought up the wizard. control_panel_windows_features

Choose ‘Role-based or feature-based installation’ > ‘Next’.  Select the server and click ‘Next’.  Leave all boxes blank on the ‘Select server roles’ screen and choose ‘Next’. On the ‘Select features’ screen, check the box for ‘.Net Framework 3.5 features’ and click ‘Next’.

The ‘Confirm installation selections’ screen will have a warning at the top beginning with ‘Do you need to specify an alternate source path?…’.  Below the big white box in the middle of the windows, click the link ‘Specify an alternate source path’; this brings up a new window.  Put the path to the Windows media.  For me, I’d mounted the disc to E:\, my source path was ‘E:\sources\sxs’.

alternate_source_path.png

Supply alternate source path

Click ‘Install’ (the above came from https://technet.microsoft.com/en-us/library/dn482071.aspx, which claims not to be maintained).

Once .Net 3.5 was installed, I went back and double clicked the ‘databasemail.exe’ from above.  All my mail that was queued was delivered.  And all test emails were now sent successfully.

These help with troubleshooting:
https://technet.microsoft.com/en-us/library/ms187540(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms190630(v=sql.105).aspx

This one has a comment at the end that helped point me in the right direction and says that CU2 may resolve the above issue:
http://dbatasks.blogspot.com/2012/11/troubleshooting-database-mail-part-ii.html

Bookmark the permalink.

23 Responses to SQL Server 2016 – Database mail queued but not sending

  1. Don says:

    Great work! Thanks.

  2. Tim says:

    Nice! Thanks.

  3. Christophe Marié says:

    Well done! Thanks a lot

  4. suvi says:

    Thanks a ton.After struggling for two days finally this worked. Great advice.

  5. bm says:

    THANK YOU! This was driving me crazy. Finding this literally made my day.

  6. Bjorn says:

    Another thank you!

  7. Julie says:

    Brilliant! It was driving me potty.

  8. Anthony says:

    It helps. Thanks a lot.

  9. Armin says:

    Thank you very much! This was driving me crazy…

  10. Patrick says:

    Thanks a lot! This really helped!

  11. David says:

    Thank you very much!!! 🙂

  12. Forrest says:

    thanks for posting. others have posted same with SP2 CU1 at https://dba.stackexchange.com/questions/210031/missing-databasemail-exe-config-after-applying-sql-server-2016-sp2-cu1?rq=1

    also

    Going from 2016 SP2
    13.0.5026.0

    to SP2 plus meltdown/spectre also seems to reintroduce this bug:

    CVE-2018-8273 (GDR) KB #4293802
    13.0.5081.1

  13. subhash says:

    I had faced the same error and challenge, In my case SQL version was 2016 SP 2 standard edition and OS was SQL 2016 standard edition with latest patch update ….Issue was resolved after update the Latest CU patch (SQLServer2016-KB4458871-x64) update ..

  14. Max says:

    Thanks, excellent guide, this solve my problem.

  15. ipman says:

    Thank you.
    This is the only place in whole Internet where the proper solution (even if is strange) is given.
    It works for me! I wanted to give up, and then I found your post. Thanks!

  16. Jeff says:

    Thank you!

  17. Surendra says:

    Thank you..
    Excellent…it worked

  18. June says:

    Thank you !
    Great article, I have the same issue, after adding .Net 3.5 framework on windows 2016 server, it works !!

  19. James Cleeve says:

    Life saver!!! would never have thought to check that…. Doesn’t even require a reboot!

  20. Javier Galoc says:

    Dude, you are the best! Thanks in advance for the tip.

  21. Pingback: Troubleshooting SQL Server Send Email – EugeneChiang.com

Leave a Reply

Your email address will not be published. Required fields are marked *