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