sp_send_dbmail继续发送电子邮件 [英] sp_send_dbmail keep sending email

问题描述

我创建了一个SP，用于重建/重组索引.

I created a SP which rebuild/reorganize my indexes.

如果发生错误，则sp_send_dbmail会通知我该错误.通话是这样的

If an error occurs, the sp_send_dbmail notifies me the error. The call is something like this

EXEC MSDB.DBO.sp_send_dbmail 
    @profile_name=@profile_name, 
    @recipients = '[e-mail]', 
    @body=@Message, 
    @body_format='TEXT', 
    @Subject=@Subject; 

上周工作正常，我通过电子邮件收到了错误消息.但是昨晚，当发生错误时，发送了一封电子邮件，现在正在连续发送同一封电子邮件.在作业活动监视器上，作业已完成. 我执行了sp_who2来查找是否有运行我的SP的程序，但是什么也没发生. 重新启动服务器，继续发送相同的电子邮件.

The last week, worked fine, I received the errors by email. But last night, when an error occured, sent a email, and now is sending the same email continuously. On the job activity monitor the job is finished. I executed sp_who2 to find if there is something running my SP, but nothing comes up. Restarted the server, continues sending the same email.

有人可以帮助我吗?

EDIT1 : 我已经执行

select  count(*), sent_status
from msdb.dbo.sysmail_mailitems
where send_request_date > '20150116'
GROUP BY sent_status

，发现有1密耳的电子邮件未发送，重试200000，并发送了300000.因此执行

And found that have 1 millon of emails unsent 200000 retrying, and 300000 sent. So execute

EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = Null,
 @sent_status = 'unsent';

希望解决这个问题.但是电子邮件已经插入，可以发送了...再次执行firts查询，现在有1封电子邮件未发送.

Hoping to solve this. But the emails keep been inserted ready to send... Executed again the firts query and now have 1 email unsent.

执行时

SELECT TOP 100 * FROM msdb.dbo.sysmail_log ORDER BY log_date DESC 

发送消息:

Error: 14667, Severity: 16, State: 1. (Params:). The error is printed in
 terse mode because there was error during formatting. Tracing, ETW,
 notifications etc are skipped.  

还有

mailitem_id on conversation 39EBD9CC-3C9D-E411-8ACD-782BCB32DC87 was not 
found in the sysmail_send_retries table. This mail item will not be sent.

推荐答案

好了，看来已经解决了，用sysmail_delete_mailitems_sp删除电子邮件后，一些电子邮件到达了收件箱，但是现在停止了

Well, it's seems to be resolved, after delete the emails with sysmail_delete_mailitems_sp, a few emails arrived to the inbox, but now stopped

这篇关于sp_send_dbmail继续发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！