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: