Sql-server – SQL Job is getting failed randomly while trying to send the alert email. need help

alertsdatabase-mailsql-server-agent

We have configured a alert which triggers the email using a job. But the job fails randomly during the day and we don't receive the alert related mails. If I clear the ExternalmailQueue it starts working again.

Below are the logs I got before the job gets failed.

Windows Event Logs

  1. Database Engine Instance=NODMSSQL1;Mail PID=2240;Error Message:Exception of type 'System.OutOfMemoryException' was thrown.
  2. Database Engine Instance=NODMSSQL1;Mail PID=2240;Error Message:The connection is not open.

Database Mail Log

08/30/2017 21:30:31,,Information,15826,DatabaseMail process is shutting down,2240,,,8/30/2017 9:30:31 PM,..\sqladmin
08/30/2017 21:30:31,,Information,15825,Database Mail shutting down. Mail queue has been stopped.,2240,,,8/30/2017 9:30:31 PM,..\sqladmin
08/30/2017 21:30:31,,Success,15824,Activation successful.,,,,8/30/2017 9:30:31 PM,sa
08/30/2017 21:30:20,,Success,15823,Mail successfully sent.
08/30/2017 21:30:19,,Success,15821,Mail successfully sent.
.
.
.

08/30/2017 21:30:10,,Error,15803,

The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 1 (2017-08-30T21:30:10). Exception Message: Cannot 
send mails to mail server. (Exceeded storage allocation. The server response 
was: 5.3.4 Message size exceeds fixed maximum message size).
),2240,19923,,8/30/2017 9:30:10 PM,sa

Sql Agent Log

08/30/2017 21:30:38,,Error,[440] SQLiMail send mail failure (reason: )
08/30/2017 21:30:38,,Error,[298] SQLServer Error: 14641 Mail not queued. Database Mail is stopped. Use sysmail_start_sp to start Database Mail. [SQLSTATE 42000]

Question

What do we have to reconfigure to reduce the errors when sending mails?

Best Answer

Pretty clearly, the mail server is sending a response to SQL Server:

Exceeded storage allocation. The server response was: 5.3.4 Message size exceeds fixed maximum message size

The size of the email being sent by SQL Server is too large.