SQL Database Mail Queries in details.

View Database Mail Log : 

SELECT * FROM msdb.dbo.sysmail_log

If you need to see a list of Successful emails you need to run :

SELECT * FROM msdb.dbo.sysmail_mailitems

You will only get the list of Failed emails:

SELECT * FROM msdb.dbo.sysmail_faileditems

List of All emails Event Log:

SELECT * FROM msdb.dbo.sysmail_event_log

Use the below query to get details for All emails sent the same date:

SELECT * FROM msdb..sysmail_mailitems WHERE sent_date > DATEADD(DAY, -1,GETDATE())

Here is the complete query to get All the Failed emails from the past 24 hours:

SELECT items.subject ,

       items.recipients ,

       items.copy_recipients ,

       items.blind_copy_recipients ,

       items.last_mod_date ,

       l.description

FROM   msdb.dbo.sysmail_faileditems AS items

       LEFT OUTER JOIN msdb.dbo.sysmail_event_log AS l

                    ON items.mailitem_id = l.mailitem_id

WHERE  items.last_mod_date > DATEADD(DAY, -1,GETDATE())

Some important Tables for DBMail Account, Profile, Configuration, etc.

SELECT * FROM msdb.dbo.sysmail_profile

SELECT * FROM msdb.dbo.sysmail_profileaccount

SELECT * FROM msdb.dbo.sysmail_account

SELECT * FROM msdb.dbo.sysmail_server

Some important SP for DBMail Account, Profile, Configuration, etc.

EXEC msdb.dbo.sysmail_help_configure_sp;

EXEC msdb.dbo.sysmail_help_account_sp;

EXEC msdb.dbo.sysmail_help_profile_sp;

EXEC msdb.dbo.sysmail_help_profileaccount_sp;

EXEC msdb.dbo.sysmail_help_principalprofile_sp;

All of SQL DBMail details:

SELECT *

FROM msdb.dbo.sysmail_profile p

JOIN msdb.dbo.sysmail_profileaccount pa on p.profile_id = pa.profile_id

JOIN msdb.dbo.sysmail_account a on pa.account_id = a.account_id

JOIN msdb.dbo.sysmail_server s on a.account_id = s.account_id

If there is any other better option,

Please share it here or mail to me arkaa4@gmail.com

Thank You,

Arka Gupta.

http://arkaguptablog.blogspot.in/

https://in.linkedin.com/in/arkagupta

https://plus.google.com/+ArkaGupta-arkagupta/posts