Microsoft SQL Server 2012 Bible

Configuration Options

Because so many similar configuration options are controlled by different commands and at different levels (server, database, connection), this section organizes the configuration options by topic, rather than by command or level.

Displaying the Advanced Options

As with many operations, displaying advanced options can be achieved using several methods. One method is to query the sys.configurations catalog view as shown here:

SELECT  name, minimum, maximum, value, value_in_use
FROM   sys.configurations
WHERE  is_advanced = 1
ORDER BY name;

The preceding example displays all the SQL Server advanced options. To display all the options, comment out the WHERE clause. Another method to display the advanced options is to turn on the show advanced options configuration using the following code:

EXEC sp_configure ‘show advanced options', 1;
RECONFIGURE;

Note

After a configuration setting is changed with sp_configure, the RECONFIGURE command causes the changes to take effect. If you don't run RECONFIGURE, then the config_value field still shows the change, but the change won't appear in the run_value field, even if you restart the service. Some configuration changes take effect only after SQL Server is restarted.

After you set the advanced option display on, you can use the sp_configure command to display a list of all the options.

EXEC sp_configure;

Result (with advanced options enabled):

name minimum maximum config_value run_value ------------------------------- ...