Microsoft® SQL Server® 2012 Bible

478

Part V: Enterprise Data Management

Displaying the Advanced Options

As with many operations, displaying advanced options can be achieved using several meth-

ods. One method is to query the sys.confi gurations 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 confi guration using the following code:

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

After a confi guration 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 fi eld still shows the change, but the

change won’t appear in the run_value fi eld, even if you restart the service. Some confi guration 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

------------------------------- ----------- ----------- ------------ -------

access check cache bucket count 0 16384 0 0

access check cache quota 0 2147483647 0 0

Ad Hoc Distributed Queries 0 1 0 0

affinity I/O mask -2147483648 2147483647 0 0

affinity64 I/O mask -2147483648 2147483647 0 0

affinity mask -2147483648 2147483647 0 0

affinity64 mask -2147483648 2147483647 0 0

Agent XPs 0 1 1 1

allow updates 0 1 0 0

awe enabled 0 1 0 0

backup compression default 0 1 0 0

blocked process threshold (s) 0 86400 0 0

c2 audit mode 0 1 0 0

clr enabled 0 1 0 0

common criteria compliance enabled 0 1 0 0

c19.indd 478c19.indd 478 7/30/2012 5:42:48 PM7/30/2012 5:42:48 PM

http://www.it-ebooks.info