Posted in Uncategorized

Sysmail_configure_sp – SQL Server Database Mail Stored Procedure – Part 1

Hi friends, from today we will discuss about database mail stored procedures. In next few days you will learn how to configure database mail, how to add mail accounts, how to add profile, and how to send mail.

Today, we will discuss about configuration setting for database mail using stored procedure sysmail_configure_sp.

Sysmail_configuration_sp stored procedure is used to change the configuration setting for database mail which applies to the entire SQL Server instance. This stored procedure is stored in msdb database and owned by schema dbo. If current database is not msdb then this stored procedure must be executed with three-part name. Permission to execute this stored procedure is default to sysadmin server role.

Syntax of sysmail_configure_sp is as follows:

sysmail_configure_sp [ [ @parameter_name = ] ‘parameter_name’ ]

[ , [ @parameter_value = ] ‘parameter_value’ ]

[ , [ @description = ] ‘description’ ]


@parameter_name  is the name of the parameter to change.

@parameter_value is the new value of the parameter.

@description is the description of the parameter.

First we need to enable database mail service using following code –

use master


sp_configure ‘show advanced options’, 1


reconfigure with override


sp_configure ‘Database Mail XPs’, 1




There are many options to configure and change database mail which are:

AccountRetryAttempts – The number of times the external mail process attempts to send the e-mail message using each account in specified profile. Default value is 1.

AccountRetryDelay – The number of times, in seconds, for the external mail process to wait between attempts to send a message. Default value is 5000.

DatabaseMailExeMinimumLifeTime – The minimum amount of time, in seconds, that the external mail process remains active. Default value is 600.

DefaultAttachmentEncoding – The default encoding for e-mail attachment. Default value is MIME.

MaxFileSize – The maximum size of an attachment in bytes. Default value is 1000000.

ProhibitedExtensions – A list of extensions which cannot be sent as an e-mail attachment to an e-mail message. Default value is exe,dll,vbs,js.

LoggingLevel – Specify which messages are recorded in Database mail log with possible value 1, 2, or 3. Default value is 2.

  • 1 – This is normal mode. Logs only errors.
  • 2 – This is extended mode. Logs errors, warnings, and informational messages.
  • 3 – This is verbose mode. Logs errors, warnings, informational messages, success messages, and additional internal messages. Use this mode for troubleshooting.

Now let’s change the configuration value of Prohibited extension and add bat value in the list.

EXEC msdb.dbo.sysmail_configure_sp ‘ProhibitedExtensions’, ‘exe,dll,vbs,js,bat’;

After executing this procedure one more value gets added in the Prohibited Extension.

Similarly we can change the attachment size to 40000 –

EXEC msdb.dbo.sysmail_configure_sp ‘MaxFileSize’, ‘40000’;

Now to see the changed values execute below stored procedure –

EXEC msdb.dbo.sysmail_help_configure_sp


We will continue with other database mail procedure in next blogs.

Hope you will like this post.



I am Kapil Singh Kumawat working on SQL Server since last 5 years. I am from Jaipur, Rajasthan, India and currently working in Cognizant Technology Solutions as SQL Server Developer. I have a good experience in writing queries, performance tuning, SSIS, SSAS, Power BI, Data migration and database designing. Apart from database I have interest in travelling,watching football and listening music. My blogs are also published on

One thought on “Sysmail_configure_sp – SQL Server Database Mail Stored Procedure – Part 1

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s