SQL Server bulk email issue.

asked 2021-05-03T08:29:26.623+00:00
utsav mistry 1 Reputation point

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
93285-image.png

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,453 questions
Exchange Server Management
Exchange Server Management
Exchange Server: A family of Microsoft client/server messaging and collaboration software.Management: The act or process of organizing, handling, directing or controlling something.
6,064 questions
{count} votes

3 answers

Sort by: Most helpful
  1. answered 2021-05-03T08:43:09.29+00:00
    Olaf Helper 25,341 Reputation points

    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.


  2. answered 2021-05-03T17:08:39.54+00:00
    Tom Phillips 17,521 Reputation points

    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

    No comments

  3. answered 2021-05-04T09:17:04.507+00:00
    CathyJi-MSFT 20,661 Reputation points Microsoft Employee

    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.

    No comments