Msg 22050, Level 16, State 1, Line 0 File attachment or query results size exceeds allowable value of 1000000 bytes.

We may encounter the below error if we want to send little large file (of query results) using sendmail option


 EXEC msdb.dbo.sp_send_dbmail
 @recipients=N'calyankumar@gmail.com',
 @body='Query Results Are Attached',
 @subject ='Query Results',
 @profile_name ='Kalyan_Notifications',
 @query ='exec MonthlyReport',
 @attach_query_result_as_file = 1,
 @query_result_separator =',',
 @query_result_no_padding=1,
 @query_attachment_filename ='QueryResults.txt'



Msg 22050, Level 16, State 1, Line 0 File attachment or query results size exceeds allowable value of 1000000 bytes.

Below statement will increase the size of attachment file

EXECUTE msdb.dbo.sysmail_configure_sp   'MaxFileSize', '2097152' ;

(OR)

SSMS - Management - Database Mail - Configure Database Mail - Next
Select View or change system parameters
Change the Maximum File Size (Bytes) -- To Desired value in bytes
Next - Finish
To view the status of mail, whether it was sent to recipients or not,

select mailitem_id, sent_status from msdb..sysmail_allitems