Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

2 Answers

up vote 5 down vote accepted

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

USE msdb;
GO

DECLARE @age INT = 5; -- age in minutes
DECLARE @cutoff DATETIME = DATEADD(MINUTE, -@age, CURRENT_TIMESTAMP);

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
add comment
  1. xp_ReadErrorLog
  2. Following

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

Your Answer

 
discard

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.