Resending failed emails through sp_send_email

Just a quick little post about an issue that pops up occasionally while I slowly work my way back into SQL after my test.

There are a lot of SQL databases servers that have been configured to use Database Mail to send email alerts for failed jobs, query results, alerts, or other tasks that require some kind of notification. Database Mail, introduced with SQL 2005, is the much improved way to send emails, far better than the older SQL Mail option. If you’re not familiar with Database Mail, go read up on it first on Books Online.

However I’ve found that some DBAs that set up Database Mail aren’t familiar with how to troubleshoot it when users complain they haven’t received expected emails from SQL. Actually it’s pretty simple. Microsoft has included some system views in the msdb database to help you out, and a log to view errors.

If you don’t want to use T-SQL you can view the database mail logs from SSMS. Just right click on Database Mail in the Object Explorer and choose View Database Mail Log (of course this is after you’ve configured Database Mail). Here you can see the status of db mail and any emails that didn’t get sent, including the reason for the failure.

image

You can get the same information by querying msdb.dbo.sysmail_event_log, including filters for event_type and a specific date if you want;

SELECT * FROM msdb.dbo.sysmail_event_log
WHERE event_type = 'error'
AND log_date >= dateadd(d, -1, getdate())

image

You can use other views to see the content of the emails. SYSMAIL_FAILEDITEMS shows emails that didn’t get sent, SYSMAIL_SENTITEMS shows emails that were sent successfully, and SYSMAIL_ALLITEMS  combines the two. You can filter by the email_id you got from the log, or see all by a date.

SELECT *
FROM msdb.dbo.sysmail_faileditems
WHERE mailitem_id = 56299;

OK. So you’ve found out that there was a problem sending emails through SQL. You’ve looked at the log to find the problem and fixed it. Now you want to resend the failed emails. Couldn’t be easier. Just query one of the msdb views for the emails you want to resend, store relevant fields in variables, then call the sp_send_dbmail procedure passing in the variables. You can use it for a single item, or wrap it in a loop to resend multiple emails. I use a script like the one below.

DECLARE @to        varchar(max)
DECLARE @copy    varchar(max)
DECLARE @title    nvarchar(255)
DECLARE @msg    nvarchar(max)
SELECT @to = recipients, @copy = copy_recipients, @title = [subject], @msg = body
FROM msdb.dbo.sysmail_faileditems
WHERE mailitem_id =  56299
EXEC msdb.dbo.sp_send_dbmail
@recipients = @to,
@copy_recipients = @copy,
@body = @msg,
@subject = @title,
@body_format = 'HTML';

And that’s it. Simple, no?