SQL Server Database Mail Duplicate message special case

When a database server (Microsoft SQL Server (SP2) (KB3171021)-12.0.5000.0 (X64) discovered that a job called Database Mail sent a message, sometimes the same message was sent two times, After detailed inspection, to exclude the business logic in the job, there is a problem in the case of duplicate sending mail, check the Database Mail log, found in the 0:00~0.03 newspaper "The Mail could not being sent to the recipients Bec Ause of the mail server failure. (Sending Mail using account 1 (2016-10-27t00:03:21). Exception message:cannot send mails to mail server "This kind of error, the specific information is as follows

Date 2016/10/27 0:03:21

Log Database Mail (Database Mail log)

Log ID 29022

Process ID 11300

Mail Item ID 747326

Last Modified 2016/10/27 0:03:21

Last Modified by SA

Message

The mail could not being sent to the recipients because of the mail server failure. (Sending Mail using account 1 (2016-10-27t00:03:21). Exception message:cannot send mails to mail server. (Mailbox unavailable. The server response was:5.7.1 unable to relay for [email protected]).

)

From the above log can be seen is SMTP feedback [email protected] This mailbox cannot be delivered. The Mail Item ID can be found in the relevant, and then with the developer to find out the stored procedure to send the message, check the discovery or because one of the mailbox address is mistaken, causing these errors in the Database Mail log, of course, if you encounter such errors, You must review the exception message:cannot send mails to mail server after the detailed error message in parentheses, and then check and analyze the confirmation (Mailbox unavailable. The server response was:5.7.1 unable to relay for [email protected]). Different log information, often feedback different problems. This is also the case of the message, there has been a duplicate send situation.

Manually test this scenario (sent to multiple recipients where one of the message names was intentionally wrong) and found that the mailbox did receive two of these messages.

EXEC msdb.dbo.sp_send_dbmail @subject =' message recurrence problem test--1 ', @recipients =' [Email protected];[ Email protected] ', @body =' Test the email send twice problem ';

And then I wanted to locate the SQL Server version or the SMTP Setup problem, and the test process made me more confused.

1: A database that has SQL Server 2008 configured with the same SMTP server is tested and the message is not sent repeatedly. You can then troubleshoot some of the setup issues that are caused by SMTP.

2: Test the same version of SQL Server 2014, the same SMTP configuration of the database, found that this situation, there is no message duplicate sending situation. Now, it's confusing. That is the case only in that environment, and no other environment can reproduce.

3: Check the Database Mail configuration information and system parameter settings, basically consistent, no difference. Nor should this cause the message to be sent repeatedly.

4: The system administrator has checked the logs of the SMTP server and found that the messages that were sent repeatedly except for the following message ID are the same, but check the records in the Msdb.dbo.sysmail_mailitems table with only one send record. As shown below

The Internet also searched for a bit, did not see a similar problem or bug information. It's a very strange question, but it's very easy to solve. But the odd thing about the problem is confusing.

SQL Server Database Mail Duplicate message special case