4

After configuring database mail on one server and sending a test email, I am finding nothing in the queue or the log. The mail id, however, keeps incrementing with each retry. I have checked the following:

  • I am sysadmin on the box, so I do have permissions to send mail
  • EXEC msdb.dbo.sysmail_help_status_sp; reports STARTED
  • There are no records in the queue: SELECT sent_account_id, sent_date FROM msdb.dbo.sysmail_sentitems;
  • There are no records in the event log: SELECT * FROM msdb.dbo.sysmail_event_log;
  • Service broker is enabled. This returns "1": SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';

I have configured this successfully on 4 other instances, it's just one that is giving me grief. Any other pointers would be appreciated.

Mark Wojciechowicz
  • 4,287
  • 1
  • 17
  • 25
  • 1
    Anything in `msdb.dbo.sysmail_allitems`? What's the fail of `sent_status` if so? – Thom A Mar 23 '18 at 15:29
  • Ah, great! There are records in there - all failed, except for the last which is `unsent`. The last one I decided to delete existing profiles and start over, so perhaps that's why it is a different status. How do I get it to go from here? – Mark Wojciechowicz Mar 23 '18 at 15:34
  • https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/configure-database-mail might help – Tanner Mar 23 '18 at 15:37
  • @Tanner thanks, I spent some time there and on the troubleshooting pages already – Mark Wojciechowicz Mar 23 '18 at 15:43

1 Answers1

1

Looks like this issue is with a bug in the version of sql that I am running on this instance as explained here: https://support.microsoft.com/en-us/help/3186435/fix-sql-server-2016-database-mail-does-not-work-on-a-computer-that-doe

Installing CU2 will fix it:

Note This issue also affects instances that are running SQL Server 2016 SP1 CU1. This issue occurs because of a bug in SQL Server 2016 SP1 CU1 setup. The setup for SQL Server 2016 SP1 CU1 deletes the config file DatabaseMail.exe.config without replacing it with a new one. This causes the Database Mail to break in the absence of .Net framework 3.5 SP1. The fix in SQL Server 2016 SP1 CU2 is meant to address this issue and prevent the DatabaseMail.exe.config file from getting deleted from the machine after installing a CU. So if you install SQL Server 2016 SP1 + CU2, the Database Mail will not be impacted by the setup. However, if the Database Mail is broken by the installation of SQL Server 2016 SP1 CU1, you can use any one of the workarounds mentioned below.

Mark Wojciechowicz
  • 4,287
  • 1
  • 17
  • 25