SQL Server 2008 R2 Query For Failed Email Notifications

Question

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?

Answer 1

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. :-)

Answer 2

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