SQL SERVER – SP_CONFIGURE – Displays or Changes Global Configuration Settings

The global configuration list shows configured and effective SQL Server settings. I compare both before deciding that a change has taken effect.

The original SP_CONFIGURE article toggled show advanced options, ran RECONFIGURE, and listed the settings. That toggle is itself an instance-level change. A read-only catalog query provides the inventory without changing the display option.

SELECT name, value AS configured_value, value_in_use AS running_value,
       is_dynamic, is_advanced, description
FROM sys.configurations
ORDER BY is_advanced, name;
-- Administrator reference only, not required to inspect the catalog:
-- EXEC sys.sp_configure N'show advanced options', 1;
-- RECONFIGURE;

is_advanced identifies advanced settings, and is_dynamic helps explain whether a change can take effect without a restart. Check the specific option documentation when configured and running values differ; some differences have documented meanings.

For an authorized change, save the original setting, use the full option name, and verify the result. RECONFIGURE can apply other pending dynamic settings, so review pending state first. Don’t set show advanced options to zero as cleanup unless that was its original value.

The commented commands show the advanced-settings workflow. Use sp_configure without arguments to list the settings visible under the current display option. Restore that option to its recorded original value after an authorized change.

Reference: Server configuration catalog.

Related reading

• SQL SERVER – Check Advanced Server Configuration
• SQL SERVER – What is Change Pending State of in SQL Server Configuration Manager?
• Why to Use SQL Server Configuration Manager Over Services applet (services.msc)? – Interview Question of the Week #112
• SQL SERVER – Unable to See SQL Server Configuration Manager. How to Open it?

A configuration value is not always the running value, it is a setting whose effective state needs checking.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.