Failed DBMail actually sending

Question

Failed DBMail actually sending

gerrit-811325

Old Hand

Points: 300
More actions
February 17, 2015 at 12:27 am

#294695

Hi All, this is my first post here, so please excuse if I missed a topic about this issue (I did search but couldn't find any).
I have a very weird issue, I use DBMail to send mail via various sp's every morning. Sometimes some of them fails and then I have to resend them manually.
So I wrote a script to show me what failed in order to resend, but then I discovered that even though it shows up in my msdb as "failed" it actually did send.
Why will this happen ?
I use SQL 2005 on Windows 2003 R2 SP2
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323506 More actions · Answer 1

i believe the defaults for DbMail, since it's asyncronous, is to try to resend a message one extra time if it fails, but it's configurable; i once inherited a system that tried re-sending some huge number of times because someone configured it wrong, fiddling with stuff they didn't understand.

so you probably want to read through and see why the mail failed the first time, but not the second.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gerrit-811325 Old Hand Points: 300 More actions · Answer 2

What is very weird is, it failed both times according to the log (or msdb.dbo.sysmail_faileditems for that matter) but in reality it sent it both times.

That is what worries me, is why does it show failed under sent status, but it never actually failed.

Lowell SSC Guru Points: 323506 More actions · Answer 3

on my servers, i have a few emails that failed and still sent; i think it has a lot to do with your mail server.

try this query out for size:

SELECT

sent_status As Reason,

err.[description],

mail.*

FROM [msdb].[dbo].[sysmail_allitems] mail

inner join [msdb].[dbo].[sysmail_event_log] err

ON err.mailitem_id = mail.mailitem_id

order by mailitem_id desc

in my case, i see two specific errors that show up, but the mail still got sent later:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2015-02-13T11:03:54). Exception Message: Cannot send mails to mail server. (Service not available, closing transmission channel. The server response was: Temporary failure, please try again later.).
)
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2015-02-12T12:56:58). Exception Message: Cannot send mails to mail server. (Failure sending mail.).
)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gerrit-811325 Old Hand Points: 300 More actions · Answer 4

I get these ones...

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2015-02-12T07:56:30). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2015-02-12T07:43:09). Exception Message: Cannot send mails to mail server. (Unable to send to a recipient.). )
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2015-02-12T07:49:50). Exception Message: Cannot send mails to mail server. (The operation has timed out.). )

Just one last question: When you say it fails and then sends later, does it still show in the log that it failed both times ?

danielpaduck Valued Member Points: 59 More actions · Answer 5

I am having the same problem. I see that the status is 'failed' but the employee I wanted to send the email too received it three times because the retry attempts is three.

This really concerns me because it means that I cannot rely on the 'SysMail_AllItems.Sent_Status' at all to determine who is or is not receiving emails.

Is there any status that will tell me the 'truth' behind if the emails have been sent?

Thanks!