August 20, 2021 at 5:58 pm
I'm using a stored procedure executed through a SQL Agent job to send out multiple emails. I've been using msdb.dbo.sp_send_dbmail for several years without issues to send these emails.
Over the last two days, I've discovered that all the emails were not being sent via feedback from users. I checked msdb.dbo.sysmail_allitems and did not see the emails that I expected to see. I checked both the sql agent job history and the dbmail error log via the SSMS GUI. Neither show any error over the last two days.
I manually executed the stored procedure in SSMS. The emails were generated and I could see them in msdb.dbo.sp_send_dbmail. I then manually started the sql agent job in SSMS and again the emails were generated and I could see them in msdb.dbo.sp_send_dbmail.
I'm currently in a wait and observer pattern to see what it does at the next scheduled run time.
I could use some suggestions on troubleshooting steps I could do to figure out why the emails were not sent and yet no errors were recorded.
August 21, 2021 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
August 22, 2021 at 4:16 pm
I have no suggestions based on what you posted. Everything seems correct but your check in msdb.dbo.sysmail_allitems seems to say they didn't actually go but there are no errors anywhere that says there was a problem that prevented the emails from going.
If you find the answer to this one, I'd sure like to know what it is. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2021 at 8:26 am
I'm no expert on this but have you checked your SMTP server that emails were received, whenever I have troubles like this it is invariably an SMTP issue. Are the emails internal or external to your domain, cannot remember if it was SMTP or not but I do remember some setting somewhere that allows relaying to external domains.
p.s. Also check if any new/updates to software that could interfere with email such as content filtering, size etc.
Far away is close at hand in the images of elsewhere.
Anon.
August 23, 2021 at 8:30 am
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 23, 2021 at 8:33 am
- create a test job and send some mails to yourself.
- Send some emails with your own email address in BCC to find out if they actually arrive.
- As it seems now, you should contact an email systems engineer and have him "trace" for incoming traffic of your "named" emails
+1
Forgot about that essential test especially for external addresses. :-/
Far away is close at hand in the images of elsewhere.
Anon.
August 23, 2021 at 3:35 pm
David,
The emails are all internal.
Can you be more specific about what kind of SMTP error could cause DBMail to not log any errors?
Johan,
In my original description I outlined how I manually ran the stored procedure and manually ran the sql agent job. In both cases, the emails were successfully sent. I have dozens of email notification procedures that are all operating successfully. Could you be more specific about what kind of email test I should run to explain why DBMail did not log any errors?
UPDATE
Today, I checked msdb.dbo.sysmail_allitems and all of the emails for this particular job were logged as sent on Saturday as they should have. I confirmed with the complaining users that they were received. I really wish the issue had persisted so that I would have something to continue troubleshooting and possibly find an explanation. I hate it when an issue mysteriously resolves itself because they are the hardest to find a root cause for.
August 23, 2021 at 3:54 pm
I am by no means an expert in this but AFAIK the only error DBMail would have is with the connection to the destination SMTP server or a system error with the message. Once SMTP successfully receives the message it is in full control and will not return anything to DBMail if there is a problem sending the email, only the mailbox linked to DBMail will receive any response (bounce backs, invalid address etc).
The only way I have traced missing emails is for our system guys to check logs (don't ask me which) to check that the message was received and what the server did with it, any response it got from the destination or the reason for non delivery. In all my years interfacing with SMTP I have had numerous mail disappearing into a 'black hole', it is very frustrating.
You should also check if there is any software running that checks mail before SMTP that may check content, attachment size etc this could also be a reason for or looks like non delivery.
Far away is close at hand in the images of elsewhere.
Anon.
August 23, 2021 at 4:06 pm
David,
I understand the issues with SMTP servers and I would heavily pursue your line of thinking IF the emails had shown up in msdb.dbo.sysmail_allitems with any kind of status. Or if the stored procedure or DBMail error logs had any kind of error in them at all.
In this case though, something seems to have have gone wrong on the SQL Server side because it didn't log any errors, but at the same time did not queue up the emails to be sent. SQL Server has no record that any attempt was made to send them. At least not in any place that I know to check.
I think I should also clarify that some of the expected emails did get queued up and sent, just not all of them. It's not like the entire thing failed to work. But only about 5 out of 40 emails were queued up. And now it's queueing up all 40.
My primary suspicion is an undocumented issue in msdb.dbo.sp_send_dbmail that can cause it to fail without capturing an error that would escalate to the sql agent job. I'm on SQL Server 2017. The only documented issue that I've found even close to this is KB3186435 but that was for SQL 2016 and was patched.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply