When you use Database Mail to send emails with attachments, you’ll need to ensure that the attachment file size is within the allowable attachment file size.

If you need to send attachments that are larger than the attachment limit, you’ll need to increase that limit.

Fortunately, increasing the allowed attachment file size can be done with a single line of T-SQL code.

The Error

Here’s the error you’ll receive if you try to send an attachment that’s larger than the allowable attachment file size:

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

Check the Limit

If you haven’t yet received an error, you might not know what the limit is. You can use the sysmail_help_configure_sp stored procedure in the msdb database to check the attachment file size limit.

EXEC msdb.dbo.sysmail_help_configure_sp 'MaxFileSize';

Result:

+-------------+--------------+---------------------------+
| paramname   | paramvalue   | description               |
|-------------+--------------+---------------------------|
| MaxFileSize | 1000000      | Default maximum file size |
+-------------+--------------+---------------------------+

In this example I passed MaxFileSize as an argument to narrow the results to just the configuration option that I’m interested in. You can also use sysmail_help_configure_sp procedure without any arguments to return all configuration options.

Change the Limit

You can change the attachment file size limit with the sysmail_configure_sp stored procedure.

EXEC msdb.dbo.sysmail_configure_sp 'MaxFileSize','3000000';

Now when we execute sysmail_help_configure_sp again, we see the new value.

EXEC msdb.dbo.sysmail_help_configure_sp 'MaxFileSize';

Result:

+-------------+--------------+---------------------------+
| paramname   | paramvalue   | description               |
|-------------+--------------+---------------------------|
| MaxFileSize | 3000000      | Default maximum file size |
+-------------+--------------+---------------------------+

Note that the description has remained the same. In this case it’s a bit misleading because 1000000 is the default maximum file size – not 4000000.

You can change the description with the same procedure. In fact, you can specify the parameter name=value pairs as well, if you prefer (this is also true for the sysmail_configure_sp procedure).

Let’s do it again, but this time I’ll update the description, and I’ll use the name=value pairs.

EXECUTE msdb.dbo.sysmail_configure_sp  
    @parameter_name = 'MaxFileSize', 
    @parameter_value = '4000000',
    @description = 'Current maximum file size';

Now when we execute sysmail_help_configure_sp again, we see the new value.

EXEC msdb.dbo.sysmail_help_configure_sp 
    @parameter_name = 'MaxFileSize';

Result:

+-------------+--------------+---------------------------+
| paramname   | paramvalue   | description               |
|-------------+--------------+---------------------------|
| MaxFileSize | 4000000      | Current maximum file size |
+-------------+--------------+---------------------------+