Problem. Your database mail suddenly stopped sending out email notifications on completed jobs and tasks. You try to send out an email test message from your SSMS and nothing is sent out.
Solution. One thing that could cause this issue is when your database mail suddenly hangs or stops in the production host server. There are number of ways that you can start to troubleshoot and investigate this issue.
First make sure your database mail is still enabled via sp_configure.
Check if the user account of the application that needs to use the database mail is member of the DatabaseMailUserRole by running below statement,
EXEC msdb.sys.sp_helprolemember ‘DatabaseMailUserRole’;
Check also if the user account has access to at least one user database mail profile,
Check and verify that your database mail is started and running;
And of course you need to start it if it is not running then run statement,
If the external program for database mail is running, you may check the mail queue by running,
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = ‘mail’;
You would want to find that your mail queue state should be ‘RECEIVES_OCCURING’. But if this is not the case you can stop the mail queue by running statement,
and start it again by,
Also you would want to check if the suddenly stopping of your database mail happens for some user accounts only. You can then run below statement;
SELECT sent_account_id, sent_date FROM msdb.dbo.sysmail_sentitems;
and to view the error messages from the database mail log, run the statement
SELECT * FROM msdb.dbo.sysmail_event_log;
or you can view from SSMS Log File Viewer window and filter out logs from database mail. But note that errors such as invalid email addresses which prevent the mail from being delivered is not contained in the database mail log.
If you feel that you have the need to increase the successful mail delivery rate you can increase the number of times Database Mail attempts to send each message. Start the Database Mail Configuration Wizard, and select the View or change system parameters option. Alternatively, you can associate more accounts to the profile so upon failover from the primary account, Database Mail will use the failover account to send e-mails.
From my own experience, the cause of our database mail suddenly stopping during a job process was a very large email item that had to be sent out and the external program just hanged. I had to stop the external program for the database mail in the Task Manager in the host server and issue a KILL statement for the process id that is blocked by the database mail process. The case might be different with yours as the issue can also be with your SMTP server.