Two days ago I wrote a post Setting up SQL Server Database Mail with gmail, I showed you how to setup SQL Server Database Mail to work with a gmail account. In yesterday’s post Database Mail maintenance in SQL Server we looked at some maintenance, today we are going to look at how we can check what is being done by Database Mail. There are a couple of Catalog Views that we will take a look at:
sysmail_event_log
sysmail_faileditems
sysmail_sentitems
sysmail_unsentitems
sysmail_mailattachments
sysmail_event_log
The event_type column will have the type of message for each Windows or SQL Server message returned by the Database Mail system. The types of messages are errors, warnings, informational messages, success messages, and additional internal messages. Here is for example a query that brings back the last 100 rows that were generated
SELECT TOP 100 * FROM msdb.dbo.sysmail_event_log
ORDER BY last_mod_date DESC
sysmail_faileditems
This view will hold all the messages that did not go out, sent_status will be failed in all rows
To see all the failed messages, you can just execute this simple query
SELECT * FROM msdb.dbo.sysmail_faileditems
sysmail_sentitems
Database Mail will mark messages as sent when they are successfully submitted to an SMTP mail server. Database Mail will mark the message as sent when it is accepted by the SMTP mail server. For E-mail errors that occur on the SMTP mail server, such as an undeliverable recipient e-mail address, errors are not returned to Database Mail. Those e-mails are recorded as sent, even though they are not delivered. This view will help you determine that something is wrong with the SMTP server
To see all the items that were sent, you can use the following query
SELECT * FROM msdb.dbo.sysmail_sentitems
sysmail_unsentitems
You can use the sysmail_unsentitems view when you want to see how many messages are waiting to be sent and how long they have been in the mail queue.
Messages can have the unsent status for these reasons:
The message is new, and has been placed on the mail queue, Database Mail is working on other messages and has not yet reached this message.
The Database Mail external program is not running and no mail is being sent.
The sent_status column will be unsent if Database Mail has not attempted to send the mail. The sent_status column will be retrying if Database Mail failed to send the message but is trying again.
Here is a simple query that will return all unsent items
SELECT * FROM msdb.dbo.sysmail_unsentitems
sysmail_mailattachments
You can use the sysmail_mailattachments view to quickly see who got what attachment. The view has the file name as well as the size of the attachment
Here is a simple query to get you started
SELECT * FROM msdb.dbo.sysmail_mailattachments
Of course you can also combine these views by joining on the mailitem_id. here is just one such query
SELECT * FROM msdb.dbo.sysmail_mailattachments a
JOIN msdb.dbo.sysmail_unsentitems u ON a.mailitem_id = u.mailitem_id
This was just a short post showing you these five catalog views that might help you with troubleshooting Database Mail. Explore the views and incorporate them in your T-SQL library.