Sent Status and mail log of the database mail.

Problem: How to know the status, content and log of the database mail?

Solution: 

In the last post as we were able to send emails for a SQL Server Agent job history, and for alerting when a job fails. As there is lot of activity going on the Database mail, how do we know whether any email went through or not, or the content of the mail, recipients and other information.

Each mail item sent from database mail gets an entry inside system tables of database mail tables. For the ease of the usage Microsoft has some system views created on this tables to retrieve the data sets.  

All these database mail views and tables can help us to track the information and activity of Mail server, account, profile and other details.

Example:

Example 1: In this example I would like to see all the information of unsent items.

USE msdb

GO

SELECT sml.description as mail_description,

smm.recipients,

smm.subject,

smat.attachment,

smat.filename,

smatt.transfer_id

 FROM

[dbo].[sysmail_log] sml

INNER JOIN

[dbo].[sysmail_mailitems] smm

ON smm.mailitem_id=sml.mailitem_id

LEFT JOIN

[dbo].[sysmail_attachments] smat

ON smm.mailitem_id=smat.mailitem_id

LEFT JOIN [dbo].[sysmail_attachments_transfer] smatt

ON smat.attachment=smatt.attachment

and smm.sent_status=0

OUTPUT:

mail_description	recipients	subject	attachment	filename	transfer_id
The mail could not be sent to the recipients	sqldevmahesh@gmail.com	Database Mail Test	NULL	NULL	NULL
The mail could not be sent to the recipients	sqldevmahesh@gmail.com	Database Mail Test	NULL	NULL	NULL
The mail could not be sent to the recipients	sqldevmahesh@gmail.com	Database Mail Test	NULL	NULL	NULL
The mail could not be sent to the recipients	sqldevmahesh@gmail.com	Database Mail Test	NULL	NULL	NULL

Example 2:

In this example I am going to show you to determine the server type of an email account and whether it’s an incoming server or outgoing server.

USE msdb

GO

SELECT

                sma.name as account_name,

                smp.name as profile_name,

                sms.servertype,

                sms.servername,

                sms.port,

                CASE

                                WHEN smsty.is_outgoing = 1

                                                THEN 'OUT-GOING SERVER'

                                WHEN is_incoming = 1

                                                THEN 'IN-COMING SERVER'

                                END [i/o]

FROM [dbo].[sysmail_account] sma

INNER JOIN [dbo].[sysmail_profileaccount] smpa

ON sma.account_id=smpa.account_id

INNER JOIN [dbo].[sysmail_profile] smp

ON smpa.profile_id=smp.profile_id

INNER JOIN

 [dbo].[sysmail_server] sms ON sma.account_id=smpa.account_id

INNER JOIN [dbo].[sysmail_servertype] smsty

                ON SMs.servertype = smsty.servertype

OUTPUT:

account_name	profile_name	servertype	servername	port	i/o
DBMail	DB Mail Public Profile	SMTP	0.0.0.0	25	OUT-GOING SERVER

 Please go through this article Configure Mail profile, on how to configure a mail account, profile.

Please go through this article send email from Database, on how to send a mail.