Have you ever been in the awkard situation that nothing is reported in the logs, but still nothing seems to work?
Yeh, I’m sure you have! Especially if you are a SQL Server DBA and are using Database Mail frequently.
Today I was following the usual steps in setting up a basic monitoring procedure on a new SQL Server installation. This is:
– Install the dbWarden monitoring solution
– Install and configure the legendaru Maintenance Solution (by Ola Hallengren)
– Configure Database Mail so that alerts and reports find their way to my mailbox
Everything went smooth but there were no new messages in my Inbox. In the next few hours I exhausted all my knowledge and ingenuity trying to resolve this. To cut a long story short, I checked the firewall, I tried telnet and everything looked fine. But when I tried “sending a test email” through Database Mail, nothing was logged and no message was received. I then examined the system views in msdb database related to Database Mail. All my attempts were logged there (thank God) with a status of unsent. Now I knew that my messages were queued but not served.
Googling revealed a very useful post for troubleshooting Database Mail issues. At some point it suggested that I ran the Database Mail executable expicitely and see if the queued messages were delivered, meaning that there was some kind of permissions or configuration issue. I tried to run the DatabaseMail.exe found in SQL Server’s BINN folder (somewhere in “C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn” ) only to get the following error message:
This was the actual problem, and the solution was to simply install the missing .NET through “Add features and roles”. Your SysAdmin is the right person to do it properly, so you should better engage him.
As soon as .NET was installed, I executed the DatabaseMail.exe manually and checked the sysmail_allitems view once again. All items had a status of sent now. The problem was solved. So, the next time you face a similar issue with the Database Mail in SQL Server, try to run the executable manually before digging into deeper water.