SQL Server bulk email issue.

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

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.
12,877 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.
7,387 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,562 questions
{count} votes

3 answers

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

    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. Tom Phillips 17,716 Reputation points
    2021-05-03T17:08:39.54+00:00

    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

    0 comments No comments

  3. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-05-04T09:17:04.507+00:00

    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.

    0 comments No comments