If you’re using Database Mail in SQL Server, you can change the configuration settings by executing the sysmail_configure_sp stored procedure in the msdb database.

Syntax

The syntax goes like this:

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

So you provide the setting name, followed by the desired value. You can also update the description for that setting.

Note that the sysmail_configure_sp stored procedure is in the msdb database and it is owned by the dbo schema. Therefore, the procedure must be invoked with a three-part name if msdb is not the current database.

Example

Here’s an example of changing the maximum file size for attachments:

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

In this case, I changed the parameter value to 2000000, but I didn’t change the description. Therefore the description remains the same as it was.

Another way of doing it is to explicitly use the argument names.

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

In this case I return the file size to its default value, and I provide the default description text (which wasn’t actually changed in the previous example anyway).

All Configuration Settings

You can get a list of the current configuration settings by executing the sysmail_help_configure_sp stored procedure without passing any arguments.

EXEC msdb.dbo.sysmail_help_configure_sp;

Result:

+--------------------------------+----------------+------------------------------------------------------------------------------
| paramname                      | paramvalue     | description                                                                  
|--------------------------------+----------------+------------------------------------------------------------------------------
| AccountRetryAttempts           | 1              | Number of retry attempts for a mail server                                   
| AccountRetryDelay              | 60             | Delay between each retry attempt to mail server                              
| DatabaseMailExeMinimumLifeTime | 600            | Minimum process lifetime in seconds                                          
| DefaultAttachmentEncoding      | MIME           | Default attachment encoding                                                  
| LoggingLevel                   | 2              | Database Mail logging level: normal - 1, extended - 2 (default), verbose - 3 
| MaxFileSize                    | 1000000        | Default maximum file size                                                    
| ProhibitedExtensions           | exe,dll,vbs,js | Extensions not allowed in outgoing mails                                     
+--------------------------------+----------------+------------------------------------------------------------------------------

Return a Specific Configuration Setting

If you only want to return one configuration setting, you can pass that to the stored procedure using the @parameter_name argument.

EXEC msdb.dbo.sysmail_help_configure_sp
    @parameter_name = MaxFileSize;

Result:

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