SQL Server : sp_configure

Put “sp_configure” into a query window and execute and you get all configurable options.
Some are modifiable. Some require restart.

  • config_value : The value that will be used at next restart.
  • run_value: Currently used value.

If you change a setting and the run_value <> config_value, it means you need a restart or reconfigure for it to take effect.

The basic usage is as follows.

  • sp_configure : Display all options and values.
  • sp_configure “option name” : Displays only the requested option details.
  • sp_configure “option name”, value : Sets the named option to the specified value.

The┬ánumber of visible options is limited by the “show advanced options” option. The output below shows the

sp_configure "show advanced options"

name                                    minimum     maximum config_value   run_value
----------------------------------- ----------- ----------- ------------ -----------
show advanced options                         0           1            0           0

Set the value to “1” and show the output again. We can see the config value is set, but the run value isn’t.

sp_configure "show advanced options", 1

sp_configure "show advanced options"

name                                    minimum     maximum config_value   run_value
----------------------------------- ----------- ----------- ------------ -----------
show advanced options                         0           1            1           0

Reconfigure to make the run_value and config_value match.

reconfigure
sp_configure "show advanced options"

name                                    minimum     maximum config_value   run_value
----------------------------------- ----------- ----------- ------------ -----------
show advanced options                         0           1            1           1

Now sp_configure displays lots more options.

Remember, you can only change options you can display!

The normal process us as follows.

  • Turn on advanced options.
  • Make your change.
  • Turn off advanced options.

For example.

sp_configure "show advanced options", 1
reconfigure

sp_configure "an advanced option", value
reconfigure

sp_configure "show advanced options", 0
reconfigure

For more information see: