主页 / dba / 问题 / 10598
Accepted
Kyle Brandt
Asked: 2012-01-13 10:57:16 +0800 CST

SQL Server 2008 R2 查询失败的电子邮件通知

  • 772

有没有我可以运行的 SQL 查询来查看在过去的 X 分钟内是否有任何失败的尝试发送数据库邮件？

sql-server sql-server-2008-r2

2 个回答

  1. Best Answer

    假设您在最后 5 分钟内想要：

    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;

    抱歉，需要毒化 SMTP 服务器以验证我的原始答案是否足够。:-)

    • 5
    1. xp_ReadErrorLog

    2. 下列的

      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
    • 2

相关问题