sp_configure

Transact-SQL Reference

Transact-SQL Reference

sp_configure

Displays or changes global configuration settings for the current server.

Syntax

sp_configure [ [ @configname = ] 'name' ]
    [ , [ @configvalue = ] 'value' ]

Arguments

[@configname =] 'name'

Is the name of a configuration option. name is varchar(35), with a default of NULL. Microsoft® SQL Server™ understands any unique string that is part of the configuration name. If not specified, the entire list of options is returned.

[@configvalue =] value

Is the new configuration setting. value is int, with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Result Sets

When executed with no parameters, sp_configure returns a result set with five columns and orders the options in alphabetically ascending order. The config_value and the run_value do not necessarily have to be equivalent. For example, the system administrator may have changed an option with sp_configure, but has not executed the RECONFIGURE statement (for dynamic options) or restarted SQL Server (for nondynamic options).

Column name Data type Description
name nvarchar(70) Name of the configuration option.
minimum int Minimum value of the configuration option.
maximum int Maximum value of the configuration option.
config_value int Value to which the configuration option was set using sp_configure (value in sysconfigures.value).
run_value int Value for the configuration option (value in syscurconfigs.value).

Remarks

Some options supported by sp_configure are designated as Advanced. By default, these options are not available for viewing and changing; setting the Show Advanced Options configuration option to 1 makes these options available. For more information about the available configuration options and their settings, see Setting Configuration Options.

When using sp_configure to change a setting, use the RECONFIGURE WITH OVERRIDE statement for the change to take immediate effect. Otherwise, the change takes effect after SQL Server is restarted.

Note  Minimum and maximum memory configurations are dynamic in SQL Server. You can change them without restarting the server.

Use sp_configure to display or change server-level settings. Use sp_dboption to change database level settings, and the SET statement to change settings that affect only the current user session.

Note  If the specified config_value is too high for an option, the run_value setting reflects the fact that SQL Server defaulted to dynamic memory, rather than use an invalid setting.

Permissions

Execute permissions on sp_configure with no parameters, or with only the first parameter, default to all users. Execute permissions for sp_configure with both parameters, used to change a configuration option, default to the sysadmin and serveradmin fixed server roles. RECONFIGURE permissions default to the sysadmin fixed server role and serveradmin fixed server role, and are not transferable.

Examples
A. List the advanced configuration options

This example shows how to set and list all configuration options. Advanced configuration options are displayed by first setting the show advanced option to 1. After this has been changed, executing sp_configure with no parameters displays all configuration options.

USE master
EXEC sp_configure 'show advanced option', '1'

--Here is the message:
Configuration option 'show advanced options' changed from 0 to 1. 
Run the RECONFIGURE command to install.

RECONFIGURE
EXEC sp_configure
B. Change a configuration option

This example sets the system recovery interval to 3 minutes.

USE master
EXEC sp_configure 'recovery interval', '3'
RECONFIGURE WITH OVERRIDE

See Also

RECONFIGURE

SET

sp_dboption

System Stored Procedures