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. In some very rare situations, inside the SmtpClient, a send mail request acquires a connection from the pool, but does not return the connection back to the pool after the send request finishes, resulting in connection leaking from the pool, and ultimately, pool depletion occurs. After pool depletion occurs, no more mail will be able to sent out by Database Mail.

It is important also to notice 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

1. 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.
2. 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