Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

This Blog is About SQL Server Technolgy
Browse by Tags · View All
BRH 12
whitepapers 10
webcast 10
Training 9
BI 8
SQL-Server 8

Archive · View All
August 2010 9
July 2010 7
May 2009 6
September 2010 4
June 2009 4
July 2009 3
October 2010 2
April 2010 2
March 2010 1

Ashish's Blog On SQL Server

System Procedure sp_configure

Mar 17 2010 11:04AM by Ashish Gilhotra   

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:

Column        Description
========      ======================================
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.

USE master;
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

USE master;

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:

Column                  Description
================        =====================================
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.


Ashish Gilhotra
31 · 6% · 1776
2 Readers Liked this
Jacob Sebastian Liked this on 2/19/2012 9:26:00 AM
Profile · Blog · Facebook · Twitter
Guru Samy Liked this on 2/25/2012 2:29:00 AM
Profile · Blog


Your Comment

Sign Up or Login to post a comment.

"System Procedure sp_configure" rated 5 out of 5 by 2 readers
System Procedure sp_configure , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]