Hi
Looking for a bit of guidance setting up alerts to notify if mail has failed to send. We had an issue yesterday where we discovered there was a lot of mail queuing and had to restart our live server overnight which then sent out over 600 queued emails.
This is set up in a couple of jobs that run daily that notify users of unclosed transactions etc. in the system.
If I check the database mail logs there are a lot of the same failure over the last few months, getting more consistent over the last few weeks:
The mail could not be sent to the recipients because of a mail server failure.
After the server was rebooted it sent all the emails, our infrastructure guy was telling me there were over 600 queued emails. Once that finished I started looking into how we could manage this on the SQL Server side.
The message now in the logs is DatabaseMail process is started, followed by DatabaseMail process is shutting down. 3 times since the server was rebooted that day and once overnight today.
During my investigations and getting some knowledge about how all this works I've been looking at various SP's (Below) just to see what they all do and if I can use them in a script to notify when failures are happening.
-
msdb.dbo.sysmail_log
-
msdb.dbo.sysmail_start_sp
-
msdb..sysmail_stop_sp
-
msdb..sysmail_help_queue_sp
-
msdb..sysmail_help_status_sp
What's confusing me is the process. When a job runs and sends an email, what actually starts the mail process, and when I run msdb..sysmail_stop_sp does that process restart itself automatically when a job runs again o do I need to restart it manually.
In short what is the best notification script I can build to keep an eye on the emails?
I want to notify if there are errors or queues building up from previous day's runs, so it can be checked.
Thanks
Andrew