3

I've been seeing some SMTP errors in my database mail logs.

So I have a set up a test SQL Agent job that runs every ten minutes (it's only action is to SELECT NULL). It is set to send email notifications whenever the job completes. The addresses are internal email addresses. I have configured database mail to retry failed emails up to 4 times.

In the last day, almost half the emails encounter an error at least once but eventually get delivered. 15 of the emails failed completely. A little more than half get sent without any error at all.

Here's a list of each job execution since midnight, along with the number of errors encountered by each attempt to send mail: http://screencast.com/t/wMqolB7b (note: there are gaps in the mailitem_id sequence because we have other jobs that also send emails).

The errors are a mix of the following two messages:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2016-03-08T05:41:21). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 192.168.60.26:25). )

-or-

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2016-03-08T05:42:42). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )

  • I see no pattern for the errored or failed emails. The emails themselves are all identical... it is only the time of the notification that changes.
  • I have verified the SMTP account settings and they are correct. The same SMTP account settings are set on two other servers, along with a similar test job. None of the emails on these other servers are failing at all.
  • I see no evidence of problems on the Exchange server
  • I see no useful related info in the server's event logs

BIG QUESTION: Why are these emails failing?

Smaller question: What steps can I take to further troubleshoot this to ultimately answer the big question?

  • I would probably start by checking your network. It appears to be an intermittent network issue. Get your network engineers to do a capture and see if any packets are being dropped. – dwjv Mar 8 '16 at 12:55
  • The mail could not be sent to the recipients because of the mail server failure.. When I got this error, everytime is network problem brother. When SQL fails to send the mail, I don't remember now the error but it's a different one. – Racer SQL Mar 8 '16 at 13:04
  • Is load on the three servers the same? – Max Vernon Mar 8 '16 at 13:05
  • @Max, the load is much lighter on the two test servers, as the server exhibiting the problem is our prod DB Server. The server is not experiencing any resource issues (CPU, RAM, I/O) – Shoeless Mar 8 '16 at 14:34
  • 2
    @dwjv (and Rafael) - oh yes, we are seeing network issues. The DB server cannot see (ping) the mail server, and the mail server cannot see the DB server. Both servers can see other servers just fine. No firewalls enabled. I will continue to troubleshoot. Sorry I didn't investigate this fundamental stuff further before posting. – Shoeless Mar 8 '16 at 15:44
0

This appears to be the result of a "bug" in Windows Server firewall. We disabled the firewall service, and after rebooting the production server (yuck) all appears to be working as expected. We have an open MS case for this, but I don't have great confidence that anything will come of it.... If we do come up with something conclusive I will post back here.

  • 1
    Interesting. If this does turn out to be the answer, lets make sure to include the Windows version, patch level, specific patch version for the Win Firewall and SQL version and patch level. It may prove to be important to those experiencing similar issues. – Shooter McGavin Nov 1 '16 at 15:31
  • Fascinating bug. Why would the firewall specifically hide SQL Server from Exchange (and or vice versa). Was it just the firewall service on the SQL Server, or did it involve the firewall on the Exchange server as well? – Laughing Vergil Jan 6 '17 at 18:51
  • How did you know it was windows firewall - if the issue was intermittent? Was it via packet capture? Experiencing a similar problem. – Dina Sep 29 '17 at 13:50
  • This was going back a ways, so I do not recall with 100% accuracy - but I believe some research on the error message led to some people experiencing the same error and discovering it was firewall related (in a more consistent fashion). It was MS that recommended disabling and rebooting, which ultimately seemed to fix the issue. – Shoeless Sep 29 '17 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.