Updates the currently configured (the config_value column in the sp_configure result set) value 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 value for either invalid values (for example, a sort order value that does not exist in syscharsets) or nonrecommended values (for example, setting allow updates to 1). 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 specifying RECONFIGURE.
Disables the configuration value checking (for invalid values or for nonrecommended values) for the allow updates, recovery interval, or time slice advanced configuration options. In addition, RECONFIGURE WITH OVERRIDE forces the reconfiguration with the specified value. For example, the min server memory configuration option could be configured with a value greater than the value specified in the max server memory configuration option. However, this is considered a fatal error. Therefore, specifying RECONFIGURE WITH OVERRIDE would not disable configuration value checking. Any configuration option can be reconfigured using the WITH OVERRIDE option.
sp_configure does not accept new configuration option values out of the documented valid ranges for each configuration option.
RECONFIGURE permissions default to members of the sysadmin and serveradmin fixed server roles, and are not transferable.
This example sets the upper limit for the network packet size configuration option and uses RECONFIGURE WITH OVERRIDE to install it. Because the WITH OVERRIDE option is specified, Microsoft® SQL Server™ does not check the value specified (8192) to see if it is a valid value for the network packet size configuration option.
EXEC sp_configure 'network packet size', 8192 RECONFIGURE WITH OVERRIDE GO