I have a job set up on SQL Server 2012 to send weekly emails to specific recipients, and I am diagnosing an issue where users have been receiving duplicate emails.
I have traced the issue back to a BCC recipient that is no longer with the company and thus the email was failing to send to their account (but the e-mail still sends to the TO and CC recipients, even though the log shows that the email failed).
The Database Mail is configured to retry once on a failure after 60 seconds, which causes the email to be sent twice to the recipients with valid email addresses.
I have corrected the issue with the invalid recipient, but I want to eliminate this issue for similar issues in the future.
Is there a way to have the server attempt to retry on failure, but only to recipients who did not receive the email on the first attempt? I know that I can reduce the retry attempts to 0, but I fear that that the system will miss other failures where a retry would actually be appropriate.