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.

Caution:
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).

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 Setting Server 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;

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

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