Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there a SQL query I can run to see if there has been any failed attempts to send database mail in the last X Minutes?

share|improve this question
up vote 5 down vote accepted

Let's say you wanted in the last 5 minutes:

USE msdb;

DECLARE @age INT = 5; -- age in minutes

SELECT recipients, subject, sent_status, send_request_date
  FROM dbo.sysmail_faileditems 
  WHERE send_request_date >= @cutoff
  ORDER BY send_request_date;

 In fact, there are some items that appear in the log 
 but not in failed items for some reason, and failed 
 items doesn't describe the problem anyway. So as @Oleg 
 added I would also run this query:

SELECT mi.recipients, mi.subject, l.description, l.log_date
  FROM dbo.sysmail_log AS l
  LEFT OUTER JOIN dbo.sysmail_mailitems AS mi
  ON l.mailitem_id = mi.mailitem_id
  WHERE l.log_date >= @cutoff
  AND l.event_type > 1
  ORDER BY l.log_date;

Sorry, needed to poison an SMTP server to verify that my original answer was insufficient. :-)

share|improve this answer
Nifty, never seen CURRENT_TIMESTAMP before, always used GETDATE or GETUTCDATE() – Kyle Brandt Jan 12 '12 at 19:11
I prefer CURRENT_TIMESTAMP only because it's ANSI. Sadly, no UTC equivalent. – Aaron Bertrand Jan 12 '12 at 19:17
  1. xp_ReadErrorLog
  2. Following

    sp_CONFIGURE 'show advanced', 1
    sp_CONFIGURE 'Database Mail XPs', 1
    select * from msdb.dbo.sysmail_faileditems 
    select * from msdb.dbo.sysmail_log
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.