After a long period of time i am again starting blogging and try to keep it continue.I like to start a series of blog post about system store procedures used in SQL Server.To kick this series out i am picking up sp_configure system procedure.
Use of sp_configure
In simple words sp_configure is a process to change or display global settings of current SQL Server.You can see the global settings and change them with the help of this procedure.
There are many settings of SQL Server which we can see and change. I will provide a details of settings and their uses in detail in later posts.First we see the columns returned by this procedure and their meanings.
When you run sp_configure you will get following columns:
Name Name of the configuration option.
Minimum Minimum allowed value for the particular
Maximum Maximum allowed value for the particular
config_value Value which we set by using sp_configure.
run_value Value which on which the particular configuration
is currently running(we will see it in detail also).
How to run sp_configure
Ok so there are many settings on SQL Server which you can change from this procedure so here is the syntax for change the setting.
EXECUTE sp_configure 'option name','value';
After running that you will get a message like
“Configuration option ‘option name’ changed from ‘old value’ to ‘new value’. Run the RECONFIGURE statement to install.”
So as message states we have to run reconfigure statement like this
So in this way you can change your global settings(server level settings) from sp_configure.
Now lets look in to the details of config_value and run_value. Config_value shows the value which is set by or configured by sp_configure and run_value shows the value on which the configuration is currently running. For making it further clear we look into sys.configurations system view.
System View sys.configurations:
sys.configuration is a system view which hold same info as sp_run_configure. SYS.Configurations view have following columns:
configuration_id Unique ID for the configuration value.
name Name of the configuration option.
value Configured value for this option.
minimum Minimum allowed value for the configuration
maximum Maximum allowed value for the configuration
value_in_use Running value currently in effect for this
description Description of the configuration option.
is_dynamic Decides whether there is need to restart
services or not.
is_advanced 1 = The variable is displayed only when the
show advanced option is set.
So here is_dynamic column decides whether there is need to restart SQL Server services or not. As if value of is_dynamic is 1 then you just have to run reconfigure statement and you are done there is no need to restart your SQL Server services for this but if your is_dynamic column value is 0 this means "NOT DYNAMIC" and you have to restart your services and after that your settings will take effect.
When you change any configuration setting so before running "reconfigure" or before starting SQL Server Services you can see the values of "config_value" and "run_value" is different so you can differentiate these two columns as "run_value" is showing the currently running configuration value and "config_value" is showing the value you configured by sp_configure and after restarting SQL Server Services or running "reconfigure" both values will be again same.
In later posts we will see all the configurations value one by one there uses and effects and many other uses of sp_configure.