Queries for troubleshooting the Database Mail (dbmail) function of Microsoft SQL Server


So just a quick one today. I was recently working on a SQL Server, running through some Database Mail setup and testing (see Microsoft Docs) with one of our applications.  I needed a way to see what e-mails were being sent out as well as what wasn’t.  The below queries will give you the info I was after, the first one shows any items that have run through DB Mail and their details for the last day (you can customise the WHERE statement to your needs.  You will want to run them against MSDB, do this by selecting it or issuing a USE MSDB statement.

SELECT p.name, i.send_request_date, i.sent_date, i.recipients, i.subject, i.body
FROM sysmail_mailitems AS i inner join sysmail_profile as p on p.profile_id = i.profile_id
WHERE sent_date > DATEADD(DAY, -1,GETDATE())

Bare in mind that I’m using Aliases to shorten the query a little (see this article).  Now this next one simply shows failed items as well as error responses if any from a mail server.

SELECT i.subject, i.recipients, i.copy_recipients, i.blind_copy_recipients, i.last_mod_date, l.description
FROM sysmail_failedi AS i LEFT OUTER JOIN sysmail_event_log AS l ON i.mailitem_id = l.mailitem_id
WHERE (i.last_mod_date > DATEADD(DAY, - 1, GETDATE()))

Hope that helps.


Leave a Reply

More Posts

Hello World

So here is my new blog, one which I actually intend on working on. Feel free to browse around, comment and critique. You can also browse the pages and contact me via the pages link or follow me on twitter. Hope you enjoy my blog and the articles that I write.

Fixing HTTP Error 500.21 – Internal Server Error Handler “WebServiceHandlerFactory-Integrated” has a bad module “ManagedPipelineHandler” in its module list on IIS.

I was recently developing some .net web applications at work and finally took the plunge of setting up a server to host them after I was happy that we had reached the point where they were usable applications.  So after setting up Server 2008 R2, I went ahead and enabled IIS with asp.net configuration enabled […]

Moving your SUSDB when it is running under MSDE

During my morning rounds of looking after clients’ servers, running SBS 2008. I noticed one was running out of space.  It had recently been updated to WSUS 3.0 SP2, after doing a quick hard drive space check I found that SUSDB was over five gigabytes in size. Generally this database should be around one gig.  […]