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