question

utsavmistry-6065 avatar image
0 Votes"
utsavmistry-6065 asked Cathyji-msft commented

SQL Server bulk email issue.

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-generalsql-server-transact-sqloffice-exchange-server-mailflow
image.png (23.6 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @utsavmistry-6065,

Any update for this thread? Did the replies could help you?

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered OlafHelper-2800 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Thanks for looking into the issue and providing the necessary steps. As SMTP has limitations of 30 messages per minutes, what could be alternative solution in order to send more mails in a given interval of time. If in the first given minute, 30 messages got sent out of 100, so in the next minute also, another 30 messages should get sent. Here in this case, once the mails get stopped sending, then all the following mails are getting affected. We need to clear up the failed queue in order to make the flow.

0 Votes 0 ·

what could be alternative solution in order to send more mails

Done mislead SQL Server as SPAMMER, get a professional email system for this instead.



0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

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://docs.microsoft.com/en-us/microsoft-365/admin/setup/create-distribution-lists?view=o365-worldwide

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @utsavmistry-6065,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.