Share via

  • Article

RECONFIGURE (Transact-SQL)

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.

Topic link iconTransact-SQL Syntax Conventions

Syntax

RECONFIGURE [ WITH OVERRIDE ]

Arguments

  • RECONFIGURE
    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.

  • WITH OVERRIDE
    Disables the configuration value checking (for values that are not valid or for nonrecommended values) for the recoveryinterval advanced configuration option.

    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.

Remarks

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.

Permissions

RECONFIGURE permissions default to grantees of the ALTER SETTINGS permission. The sysadmin and serveradmin fixed server roles implicitly hold this permission.

Examples

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