Updates the currently configured value (the config_value column in the sp_configure result set) of a configuration option changed with the sp_configure system stored procedure. Because some configuration options require a server stop and restart to update the currently running value, RECONFIGURE does not always update the currently running value (the run_value column in the sp_configure result set) for a changed configuration value.
RECONFIGURE [ WITH OVERRIDE ]
Specifies that if the configuration setting does not require a server stop and restart, the currently running value should be updated. RECONFIGURE also checks the new configuration values for either values that are not valid (for example, a sort order value that does not exist in syscharsets) or nonrecommended values. With those configuration options not requiring a server stop and restart, the currently running value and the currently configured values for the configuration option should be the same value after RECONFIGURE is specified.
Disables the configuration value checking (for values that are not valid or for nonrecommended values) for the recoveryinterval advanced configuration options.
Any configuration option can be reconfigured by using the WITH OVERRIDE option. In addition, RECONFIGURE WITH OVERRIDE forces the reconfiguration with the specified value. For example, the minservermemory configuration option could be configured with a value greater than the value specified in the maxservermemory configuration option. However, this is considered a fatal error. Therefore, specifying RECONFIGURE WITH OVERRIDE would not disable configuration value checking.
sp_configure does not accept new configuration option values out of the documented valid ranges for each configuration option.
RECONFIGURE is not allowed in an explicit or implicit transaction. When you reconfigure several options at the same time, if any of the reconfigure operations fail, none of the reconfigure operations will take effect.
RECONFIGURE permissions default to grantees of the ALTER SETTINGS permission. The sysadmin and serveradmin fixed server roles implicitly hold this permission.
The following example sets the upper limit for the
recovery interval configuration option to
90 minutes and uses
RECONFIGURE WITH OVERRIDE to install it. Recovery intervals greater than 60 minutes are not recommended and disallowed by default. However, because the
WITH OVERRIDE option is specified, SQL Server does not check whether the value specified (
90) is a valid value for the
recovery interval configuration option.
EXEC sp_configure 'recovery interval', 90 RECONFIGURE WITH OVERRIDE GO
Help and Information
14 April 2006