Database mail and connection pool depletion
Database Mail (DBMail) in SQL Server is a feature that allows you to send emails directly from SQL Server, but it has limitations when sending a large volume of emails. The issues you’re experiencing with DBMail not being able to send massive emails could be due to several factors.
DBMail relies on the .NET Framework’s SmtpClient class, and any issues with this underlying component could affect DBMail’s functionality.
The SmtpClient class implementation pools SMTP connections so that it can avoid the overhead of re-establishing a connection for every message to the same server. An application may re-use the same SmtpClient object to send many different emails to the same SMTP server and to many different SMTP servers. As a result, there is no way to determine when an application is finished using the SmtpClient object and it should be cleaned up.
It is important also to notice that t’s important to note that SmtpClient is deprecated in .NET Core and .NET 5.0 and later versions. While it is still available for compatibility reasons, it is recommended to use alternative libraries for new development
As a side effect on the SQL Server database mail, we may face the below error
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 7 (2024-01-08T16:07:10). Exception Message: 1) Exception Information =================== Exception Type: Microsoft.SqlServer.Management.SqlIMail.MailFramework.Exceptions.BaseMailFrameworkException Message: Cannot send mails to mail server. (The operation has timed out.) Data: System.Collections.ListDictionaryInternal TargetSite: Void Send(Microsoft.SqlServer.Management.SqlIMail.MailFramework.Framework.IMessage) HelpLink: NULL Source: DatabaseMailProtocols HResult: -2146232832 StackTrace Information =================== at Microsoft.SqlServer.Management.SqlIMail.MailFramework.Smtp.SmtpMailSender.Send(IMessage msg) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.ProfileMailSender.SendMailToAccount(Account a, IMessageSender ms, OutMailItem si) 2) Exception Information =================== Exception Type: System.Net.Mail.SmtpException StatusCode: GeneralFailure Message: The operation has timed out. Data: System.Collections.ListDictionaryInternal TargetSite: Void Send(System.Net.Mail.MailMessage) HelpLink: NULL Source: System HResult: -2146233088 StackTrace Information =================== at System.Net.Mail.SmtpClient.Send(MailMessage message) at Microsoft.SqlServer.Management.SqlIMail.MailFramework.Smtp.SmtpMailSender.Send(IMessage msg). )
As a workaround we can take the below actions
Try to follow all limitations on the mail server side for your mail account and do not exceed them. This is for avoiding any possible exceptions in the SmtpClient <-> Mail Server layer.
For example, if the exchange server is configured a maximum number of concurrent connections, make sure that your script or application does not send number of emails that exceeds that limitation.
If you find that sending mail with DatabaseMail starts to fail, please restart Databasemail.exe. DatabaseMail will resend all failed mails upon restart.
Finally, please note that SMTPClient doesn’t support many modern protocols. and as of now is on compatibility mode-only but doesn’t scale to modern requirements of the protocols
More information on the below link
SmtpClient Class (System.Net.Mail) | Microsoft Learn
Microsoft Tech Community – Latest Blogs –Read More
