using sp_configure in sql server explained in depth.
The procedure sp_configure Displays or changes global configuration settings for the current server.
Syntax:
sp_configure
Parameters of sp_configure:
| Parameter_name | Type | Length | Prec | Scale | Param_order | Collation |
| @configname | varchar | 35 | 35 | NULL | 1 | SQL_Latin1_General_CP1_CI_AS |
| @configvalue | int | 4 | 10 | 0 | 2 | NULL |
@configname is the configuration name. If this value is not
specified then all the configuration options are listed.
@configvalue is used to set the new value to the
configuration option provided in @configname. Its default value is null.
The maximum and minimum values for each configuration name
can be found by executing the sp_configure procedure directly.
The output of executing sp_configure directly will be as
follows(depending upon the current configuration the results will change):
The Screen Shot Shown Above Changes As Per The Current Sql Server Configuration.
How to change the sql server configured value:(be cautious, changing the values with out knowledge would harm your servers instance)
Syntax:
sp_configure ‘<<Configuration Name>>’,'<<Configuration Value>>’
One fo the simple example would be:
sp_configure 'show advanced options', 1
GO
This changes the configuration value of ‘show advanced options’ to 1. This means the sqrver instance is now configured to show the advanced options.
In order to apply the changed confuguration to the servers instance the reconfigure command has to be run.
Syntax:
reconfigure
GO
Now the updated value is configured to the instance. There are some configurations which do not apply immedietely but will need a Server service restart.
Now if the sp_configure procedure is run again, the advanced configuration options are shown as shown in the below image:
How to check the configured value that is currently being used by the instance?
Running the procedure sp_configure with out parameters gives a resultset which contains the column run_value.
This value is the value that is currently being used.Some configurations which do not apply immedietely will need a Server service restart. They can be found by analysing the run_value Column for sp_configure.
So the difference between Config_Value and run_value is that config_value is the value that the configration name is configured, run_value is the value that the instance is currently using.
Unless the reconfigure command is run the config_value and the run_value does not match, There are some configurations for which even after running the reconfigure command the config_value does not match with run_value, for these configurations sql server service has to be restarted after running the reconfigure command.