Using sp_configure

sp_configure displays and resets configuration parameters.

You can restrict the number of parameters that sp_configure shows by using sp_displaylevel to set your display level to one of:

For information about sp_displaylevel, see the Reference Manual: Procedures.

In this table, the “Effect” column assumes that your display level is set to “comprehensive.”

Command

Effect

sp_configure

Displays all configuration parameters by group, their current values, their default values, the value to which they have most recently been set, and the amount of memory used by this particular setting.

sp_configure “parameter”

Displays current value, default value, most recently changed value, and amount of memory used by the specified parameter.

sp_configure “parameter”, value

Resets parameter to value.

sp_configure “parameter”, 0, “default”

Resets the specified parameter to its default value.

sp_configure “group_name”

Displays all configuration parameters in group_name, their current values, their default values, the values to which they were recently set, and the amount of memory used by each.

sp_configure “configuration file”, 0, “sub_command”, “file_name”

Sets configuration parameters from the configuration file.
Parent topic: Setting Configuration Parameters