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

Question

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?

Answer 1

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.

Answer 2

The error is quite clear. Your mail has reached a certain limit and is being rejected by a mail server.

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

If there are no file limitations on the SMTP (Mail) server

..., open up SQL Server Management Studio (SSMS) and navigate down to Management | Database Mail.

When you have reached Database Mail right-click on it and select Confgiure Database Mail.

You will be presented with the Database Mail Configuration Wizard for your SQL Server instance.

If you have not yet cancelled the wizard (in a previous session) you will be presented with an introduction page. Click on Next > to go to the next window.

You are now in the Select Configuration Task dialog and are presented with the following possibilities:

• Setup Database Mail by performing the following tasks:
  1. Create a new e-mail profile and specify its SMTP accounts
  2. Specify profile security
  3. Cofngiur system parameters
• Manage Database Mail accounts and profiles
• Manage profile security
• View or change system parameters

Select the last option View or change system parameters and click on Next >

You are presented with the Configure System Parameters page. This page shows various mail settings.

• Account Retry Attempts
• Account Retry Delay (seconds)
• Maximum File Size (Bytes)
• Prohibited Attachment File Extensions
• Database Mail Executeable Minimum Lifetime (seconds)
• Logging Level

Increase the size for Maximum File Size (Bytes) in this page and click on Next > and then on Finish.

Alternate Solution (Quick Fix)

Execute the built-in stored procedure sysmail_configure_sp (Microsoft Docs)

sysmail_configure_sp [ [ @parameter_name = ] 'parameter_name' ]  
    [ , [ @parameter_value = ] 'parameter_value' ]  
    [ , [ @description = ] 'description' ] 

E.g.

sysmail_configure_sp @parameter_name = 'MaxFileSize', @parameter_value = 20000000

If you have a file size limitation on the SMTP (Mail) server

..., ask the mail administrator (postmaster) to increase the limit for file attachments to allow your SQL Server Account (or other affected account) to send attachments with a larger file size.