sp_send_dbmail keep sending email

2019-08-09 09:20发布

I created a SP which rebuild/reorganize my indexes.

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 = 'email@domain.com', 
    @body=@Message, 
    @body_format='TEXT', 
    @Subject=@Subject; 

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.

Can anyone help me?

EDIT1: I've executed

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

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';

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

When execute

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

Throw the message:

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.  

And also

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.

1条回答
别忘想泡老子
2楼-- · 2019-08-09 10:23

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

查看更多
登录 后发表回答