{"id":12,"date":"2011-06-20T13:29:54","date_gmt":"2011-06-20T13:29:54","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2011\/06\/20\/using-sp_configure-sql-server\/"},"modified":"2011-06-20T13:29:54","modified_gmt":"2011-06-20T13:29:54","slug":"using-sp_configure-sql-server","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2011\/06\/20\/using-sp_configure-sql-server\/","title":{"rendered":"using sp_configure sql server"},"content":{"rendered":"

using sp_configure in sql server explained in depth.<\/p>\n

The procedure sp_configure Displays or changes global configuration settings for the current server.<\/p>\n

Syntax: <\/strong><\/p>\n


\nsp_configure
\n<\/code><\/p>\n

Parameters of sp_configure:<\/p>\n

<\/span><\/p>\n

<\/span><\/p>\n\n\n\n\n\n
<\/span>Parameter_name<\/td>\nType<\/td>\nLength<\/td>\nPrec<\/td>\nScale<\/td>\nParam_order<\/td>\nCollation<\/td>\n<\/tr>\n
@configname<\/td>\nvarchar<\/td>\n35<\/td>\n35<\/td>\nNULL<\/td>\n1<\/td>\nSQL_Latin1_General_CP1_CI_AS<\/td>\n<\/tr>\n
@configvalue<\/td>\nint<\/td>\n4<\/td>\n10<\/td>\n0<\/td>\n2<\/td>\nNULL<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

@configname is the configuration name. If this value is not
\nspecified then all the configuration options are listed.<\/p>\n

@configvalue is used to set the new value to the
\nconfiguration option provided in @configname. Its default value is null.<\/p>\n

The maximum and minimum values for each configuration name
\ncan be found by executing the sp_configure procedure directly.<\/p>\n

The output of executing sp_configure directly will be as
\nfollows(depending upon the current configuration the results will change):<\/p>\n

\"sp_configure<\/a><\/div>\n

The Screen Shot Shown Above Changes As Per The Current Sql Server Configuration.<\/p>\n

How to change the sql server configured value:<\/strong>(be cautious, changing the values with out knowledge would harm your servers instance)<\/p>\n

Syntax:<\/strong>
\nsp_configure ‘<<Configuration Name>>’,'<<Configuration Value>>’<\/p>\n

One fo the simple example would be:<\/p>\n

sp_configure 'show advanced options', 1
\nGO
\n<\/code><\/p>\n

This changes the configuration value of ‘show advanced options’ to 1. This means the sqrver instance is now configured to show the advanced options.<\/p>\n

In order to apply the changed confuguration to the servers instance the reconfigure command has to be run.<\/p>\n

Syntax:<\/strong>
\nreconfigure
\nGO
\n<\/code><\/p>\n

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.<\/p>\n

Now if the sp_configure procedure is run again, the advanced configuration options are shown as shown in the below image:<\/p>\n

\"\"<\/a><\/div>\n

How to check the configured value that is currently being used by the instance?<\/strong><\/p>\n

Running the procedure sp_configure with out parameters gives a resultset which contains the column run_value.<\/p>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,3],"tags":[33,34,35,36,37,38,39,40],"class_list":["post-12","post","type-post","status-publish","format-standard","hentry","category-configuring-sql-server","category-sqlserverpedia-syndication","tag-how-to-use-sp_configure","tag-reconfigure","tag-sp_configure-configname-configvalue","tag-sp_configure-and-reconfigure","tag-sp_configure-in-detail","tag-sp_configure-parameter_name-type-length-prec-scale-param_order-collation","tag-sp_configure-reconfigure","tag-sp_configure-show-advanced-options-1"],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/12","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=12"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/12\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=12"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=12"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=12"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}