sp_configure (T-SQL)

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 fixed server role. RECONFIGURE permissions default to the sysadmin 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'

Configuration option changed. 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