sp_configure is a stored procedure on MS SQL Server for displaying or changing global configuration settings for the server.When you run the stored procedure with no parameters, it returns a result set with five columns and orders the options in alphabetically ascending order. The colums are:
- name: Name of the configuration option.
- minimum: Minimum value of the configuration option.
- maximum: Maximum value of the configuration option.
- config_value: Configured value for the system.
- run_value: Configured value for current session.
To change a configuration option, the syntax of the command is:
sp_configure [ [ @configname = ] ‘name‘ [ , [ @configvalue = ] ‘value‘ ] ]
The below able show the availbale configuration options:
| Configuration option | Minimum | Maximum | Default |
| affinity mask (A, RR) | 0 | 2147483647 | 0 |
| allow updates | 0 | 1 | 0 |
| awe enabled (A, RR) | 0 | 1 | 0 |
| c2 audit mode (A, RR) | 0 | 1 | 0 |
| cost threshold for parallelism (A) | 0 | 32767 | 5 |
| Cross DB Ownership Chaining | 0 | 1 | 0 |
| cursor threshold (A) | -1 | 2147483647 | -1 |
| default full-text language (A) | 0 | 2147483647 | 1033 |
| default language | 0 | 9999 | 0 |
| fill factor (A, RR) | 0 | 100 | 0 |
| index create memory (A, SC) | 704 | 2147483647 | 0 |
| lightweight pooling (A, RR) | 0 | 1 | 0 |
| locks (A, RR, SC) | 5000 | 2147483647 | 0 |
| max degree of parallelism (A) | 0 | 32 | 0 |
| max server memory (A, SC) | 4 | 2147483647 | 2147483647 |
| max text repl size | 0 | 2147483647 | 65536 |
| max worker threads (A, RR) | 32 | 32767 | 255 |
| media retention (A, RR) | 0 | 365 | 0 |
| min memory per query (A) | 512 | 2147483647 | 1024 |
| min server memory (A, SC) | 0 | 2147483647 | 0 |
| Using Nested Triggers | 0 | 1 | 1 |
| network packet size (A) | 512 | 65536 | 4096 |
| open objects (A, RR, SC) | 0 | 2147483647 | 0 |
| priority boost (A, RR) | 0 | 1 | 0 |
| query governor cost limit (A) | 0 | 2147483647 | 0 |
| query wait (A) | -1 | 2147483647 | -1 |
| recovery interval (A, SC) | 0 | 32767 | 0 |
| remote access (RR) | 0 | 1 | 1 |
| remote login timeout | 0 | 2147483647 | 20 |
| remote proc trans | 0 | 1 | 0 |
| remote query timeout | 0 | 2147483647 | 600 |
| scan for startup procs (A, RR) | 0 | 1 | 0 |
| set working set size (A, RR) | 0 | 1 | 0 |
| show advanced options | 0 | 1 | 0 |
| two digit year cutoff | 1753 | 9999 | 2049 |
| user connections (A, RR, SC) | 0 | 32767 | 0 |
| user options | 0 | 32767 | 0 |
- A: Advanced options, which require setting show advanced options to 1.
- RR: Options that require a server restart before taking effect.
- SC: Self-configuring options.
Execute permissions on sp_configure with no parameters, or with only the first parameter, default to all users. Execute permissions for sp_configure with both parameters, used to change a configuration option, default to the sysadmin and serveradmin fixed server roles. RECONFIGURE permissions default to the sysadmin fixed server role and serveradmin fixed server role, and are not transferable.
When using sp_configure, you must run either RECONFIGURE or RECONFIGURE WITH OVERRIDE after setting a configuration option. The RECONFIGURE WITH OVERRIDE statement is usually reserved for configuration options that should be used with extreme caution (for example, setting the allow updates option to 1 allows users to update fields in system tables). However, RECONFIGURE WITH OVERRIDE works for all configuration options, and you can use it in place of RECONFIGURE.
Example:
USE master
EXEC sp_configure ‘show advanced option’, ‘1’
RECONFIGURE