sp_configure (Transact-SQL)

Artikel
05/16/2013

Displays or changes global configuration settings for the current server.

Topic link icon Transact-SQL Syntax Conventions

Syntax

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

Arguments

[ @configname= ] 'option_name'
Is the name of a configuration option. option_name is varchar(35), with a default of NULL. The SQL Server Database Engine recognizes any unique string that is part of the configuration name. If not specified, the complete list of options is returned.

For information about the available configuration options and their settings, see Server Configuration Options (SQL Server).
[ @configvalue= ] 'value'
Is the new configuration setting. value is int, with a default of NULL. The maximum value depends on the individual option.

To see the maximum value for each option, see the maximum column of the sys.configurations catalog view.

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 alphabetically in ascending order, as shown in the following table.

The values for config_value and run_value are not automatically equivalent. After updating a configuration setting by using sp_configure, the system administrator must update the running configuration value by using either RECONFIGURE or RECONFIGURE WITH OVERRIDE. For more information, see the Remarks section.

Column name	Data type	Description
name	nvarchar(35)	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 sys.configurations.value). For more information about these options, see Server Configuration Options (SQL Server) and sys.configurations (Transact-SQL).
run_value	int	Currently running value of the configuration option (value in sys.configurations.value_in_use). For more information, see sys.configurations (Transact-SQL).

Remarks

Use sp_configure to display or change server-level settings. To change database-level settings, use ALTER DATABASE. To change settings that affect only the current user session, use the SET statement.

Updating the Running Configuration Value

When you specify a new value for an option, the result set shows this value in the config_value column. This value initially differs from the value in the run_value column, which shows the currently running configuration value. To update the running configuration value in the run_value column, the system administrator must run either RECONFIGURE or RECONFIGURE WITH OVERRIDE.

Both RECONFIGURE and RECONFIGURE WITH OVERRIDE work with every configuration option. However, the basic RECONFIGURE statement rejects any option value that is outside a reasonable range or that may cause conflicts among options. For example, RECONFIGURE generates an error if the recovery interval value is larger than 60 minutes or if the affinity mask value overlaps with the affinity I/O mask value. RECONFIGURE WITH OVERRIDE, in contrast, accepts any option value with the correct data type and forces reconfiguration with the specified value.

Warning

An inappropriate option value can adversely affect the configuration of the server instance. Use RECONFIGURE WITH OVERRIDE cautiously.

The RECONFIGURE statement updates some options dynamically; other options require a server stop and restart. For example, the min server memory and max server memory server memory options are updated dynamically in the Database Engine; therefore, you can change them without restarting the server. By contrast, reconfiguring the running value of the fill factor option requires restarting the Database Engine.

After running RECONFIGURE on a configuration option, you can see whether the option has been updated dynamically by executing sp_configure 'option_name'. The values in the run_value and config_value columns should match for a dynamically updated option. You can also check to see which options are dynamic by looking at the is_dynamic column of the sys.configurations catalog view.

Note

If a specified value is too high for an option, the run_value column reflects the fact that the Database Engine has defaulted to dynamic memory rather than use a setting that is not valid.

For more information, see RECONFIGURE (Transact-SQL).

Advanced Options

Some configuration options, such as affinity mask and recovery interval, are designated as advanced options. By default, these options are not available for viewing and changing. To make them available, set the Show Advanced Options configuration option to 1.

For more information about the configuration options and their settings, see Server Configuration Options (SQL Server).

Permissions

Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, you must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

Examples

A. Listing the advanced configuration options

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

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

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

Run RECONFIGURE and show all configuration options:

RECONFIGURE;
EXEC sp_configure;

B. Changing a configuration option

The following example sets the system recovery interval to 3 minutes.

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

Dela via