Início / dba / Perguntas / 10598
Accepted
Kyle Brandt
Asked: 2012-01-13 10:57:16 +0800 CST

Consulta do SQL Server 2008 R2 para notificações de e-mail com falha

  • 772

Existe uma consulta SQL que eu possa executar para ver se houve alguma tentativa falha de enviar e-mail de banco de dados nos últimos X minutos?

sql-server sql-server-2008-r2

2 respostas

  1. Best Answer

    Digamos que você queria nos últimos 5 minutos:

    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;

    Desculpe, precisei envenenar um servidor SMTP para verificar se minha resposta original era insuficiente. :-)

    • 5
    1. xp_ReadErrorLog

    2. Seguindo

      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

relate perguntas