remidian.com

Display or change global configuration settings for your mssql server through sp_configure.

Show (advanced) configuration options

To display the current configuration settings execute dbo.sp_configure without arguments.

C:\>osql -E
1> exec dbo.sp_configure
2> go
 name                                minimum     maximum     config_value run_value
 ----------------------------------- ----------- ----------- ------------ -----------
 allow updates                                 0           1            0           0
 Cross DB Ownership Chaining                   0           1            0           0
 default language                              0        9999            0           0
 max text repl size (B)                        0  2147483647        65536       65536
 nested triggers                               0           1            1           1
 remote access                                 0           1            1           1
 remote login timeout (s)                      0  2147483647           20          20
 remote proc trans                             0           1            0           0
 remote query timeout (s)                      0  2147483647          600         600
 show advanced options                         0           1            0           0
 user options                                  0       32767            0           0

To view all configuration options you need to alter the configuration setting ‘show advanced options’ to ’1′.

1> exec dbo.sp_configure "show advanced options", "1"
2> reconfigure
3> go
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement 
to install.
1> -- now we'll get all options
2> exec dbo.sp_configure
3> go
 name                                minimum     maximum     config_value run_value
 ----------------------------------- ----------- ----------- ------------ -----------
 affinity mask                       -2147483648  2147483647            0           0
 allow updates                                 0           1            0           0
 awe enabled                                   0           1            0           0
 c2 audit mode                                 0           1            1           0
 cost threshold for parallelism                0       32767            5           5
 Cross DB Ownership Chaining                   0           1            0           0
 cursor threshold                             -1  2147483647           -1          -1
 default full-text language                    0  2147483647         1033        1033
 default language                              0        9999            0           0
 fill factor (%)                               0         100            0           0
 index create memory (KB)                    704  2147483647            0           0
 lightweight pooling                           0           1            0           0
 locks                                      5000  2147483647            0           0
 max degree of parallelism                     0          32            0           0
 max server memory (MB)                        4  2147483647   2147483647  2147483647
 max text repl size (B)                        0  2147483647        65536       65536
 max worker threads                           32       32767          255         255
 media retention                               0         365            0           0
 min memory per query (KB)                   512  2147483647         1024        1024
 min server memory (MB)                        0  2147483647            0           0
 nested triggers                               0           1            1           1
 network packet size (B)                     512       65536         4096        4096
 open objects                                  0  2147483647            0           0
 priority boost                                0           1            0           0
 query governor cost limit                     0  2147483647            0           0
 query wait (s)                               -1  2147483647           -1          -1
 recovery interval (min)                       0       32767            0           0
 remote access                                 0           1            1           1
 remote login timeout (s)                      0  2147483647           20          20
 remote proc trans                             0           1            0           0
 remote query timeout (s)                      0  2147483647          600         600
 scan for startup procs                        0           1            0           0
 set working set size                          0           1            0           0
 show advanced options                         0           1            1           1
 two digit year cutoff                      1753        9999         2049        2049
 user connections                              0       32767            0           0
 user options                                  0       32767            0           0

Changing a configuration setting

Listing below changes the configuration setting for the ‘c2 audit mode’;

C:\>osql -E
1> exec dbo.sp_configure "c2 audit mode", "1"
2> reconfigure
3> go
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement 
to install.
1> exec dbo.sp_configure 'c2 audit mode'
2> go
 name                                minimum     maximum     config_value run_value
 ----------------------------------- ----------- ----------- ------------ -----------
 c2 audit mode                                 0           1            1           0

Notice the config_value changed from 0 to 1. The MSSQL Server needs to be rebooted to get this parameter in effect hence the ‘run_value’ is still ’0′.