SQL Server bulk email issue.

Question

There is a requirement to send the notification emails on every 1st and 2nd working day of every month to all the concerned users. Since we have SQL Server as our database, we are using SQL database mail to send the mails, which is connected with smtp server. The average number of mails for this functionality would be more than 500. All this mails triggers in one attempt. Since this emails are in bulk, a problem arises such that the mails starts getting failed or either go in unsent or retrying pool. Post that, if the time interval between the SMTP server connection will be over and all the unsent or retrying mails gets failed. Sometimes, we need to restart the SQL service and SQL Agent and it's related service. We have database mail account for the profile we are using to send the mails. In that database mail account, since we have smtp sever, we are defining server name as : smtp.office365.com. We also have Outlook in server so whenever this mail arises, as a quick fix try, we change the server name from smtp.office365.com to outlook.office365.com. This resolves the issue for about couple of weeks and again then the issue starts arising. So again, we change it form outlook to smtp and everything works properly. We explored over the Microsoft' site about the database mail issue and found some changes related to database mail configuration through the stored procedure sysmail_configure_sp and set its's relative parameters as

Although we have done all the probable changes and implemented all the necessary steps, after sending few amount of mails, this issue persists whenever bulk mails are needed to be send. upon checking the logs, we found the error as

"The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 17 (2021-05-03T13:54:12). Exception Message: Cannot send mails to mail server. (The operation has timed out.)."

"The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 17 (2021-05-03T13:54:12). Exception Message: Cannot send mails to mail server. (Failure sending mail.).
)"

Looking for a permanent fix for this.

Answer 1

The average number of mails for this functionality would be more than 500

Has nothing to do with SQL Server, Office365 has limitations, see Limitations of SMTP AUTH client submission : Microsoft 365 or Office 365 imposes a limit of 30 messages sent per minute, and a limit of 10,000 recipients per day.

Answer 2

As Olaf said, you are sending too many emails from SQL Server per min.

In your case, the best practice is to create a "distribution group" inside Office365. Then your email from DBMail is only 1 email to 1 email address, the distribution group.

https://learn.microsoft.com/en-us/microsoft-365/admin/setup/create-distribution-lists?view=o365-worldwide

Answer 3

Hi @utsav mistry ,

Suggest you try to create a mail group as Tom mentioned. Then change the email address to group email address when configuring SQL DB mail.

Check if this old thread DBMail fails to send email to Group Account could help you? It sharing some reminders for this configuration.