Information on sys.configurations

sys.configurations – It has server side configuration value
name – Name of configuration name
value – current value of the parameter
minimum – minimum value of configuration can be set to
maximum – maximum value of configuration can be set to
value_in_use – running with the current value
description – Description of the configuration option
is_dynamic – When (1) the parameter can be changed when the RECONFIGURE statement is executed
is_advanced – When (1) the parameter is displayed when the show advanced option is set

Example of advanced option: Shows one of the configuration option, it current value and the option is advanced which means exec sp_configure 'show advanced option', '1'; to set and change the value

SELECT name, value, is_dynamic, is_advanced FROM sys.configurations where name = 'min server memory (MB)'
name value is_dynamic is_advanced
———————– —————————- ———– ————
min server memory (MB) 500 1 1

exec sp_configure 'show advanced option', '1';
reconfigure
exec sp_configure 'min server memory (MB)'
exec sp_configure 'show advanced options', 0
reconfigure
Output:
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
name minimum maximum config_value run_value
———————————– ———– ———– ———— ———–
min server memory (MB) 0 2147483647 500 500

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

If one tries to see the value of advanced configuration parameter without setting the ‘show advanced options’ one will receive the following error:
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option ‘min server memory (MB)’ does not exist, or it may be an advanced option.

To see all the configuration parameters that can be dynamically modified and changed using advanced options one can run the following SQL.
SELECT * FROM sys.configurations WHERE is_dynamic = 1 and is_advanced = 1

http://msdn.microsoft.com/en-us/library/ms188345.aspx
http://msdn.microsoft.com/en-us/library/ms188345%28SQL.90%29.aspx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s