How to troubleshoot database mail?
If after creating a DB Mail Profile and an account you send a test email and it doesn’t work, you can try these steps to troubleshoot it:
- Look for error messages in the database mail log: “SELECT * FROM msdb.dbo.sysmail_event_log;”
A couple of common problems that you might encounter here are: authentication issue (due to incorrect mail configuration such as username/password, or incorrect ports, SSL settings and so on), or the mail server being unreachable (usually due to Firewall settings).
- Check that the database mail status is started: “EXEC msdb.dbo.sysmail_help_status_sp;”
If it’s not, you can use the sysmail_start_sp procedure to start it.
- Check mail queue state with “EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = ‘Mail’ ;”
You can use this procedure to see how many items are in the queue, the status of the queue, and when it was last activated.
- Try to find “DatabaseMail.exe” in the task manager or disk and execute it.
- Windows can pop up error messages like “this app requires .NET Framework 3.5“, strange, but it offers to install it. Click Ok.
- Restart the SQL Server service.
- Mail queue is empty. All mail sent.