sp_configure

Description

Displays 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 setting. Displays only the parameters whose display level is the same as or below that of the user.

Syntax

sp_configure [configname [, configvalue] | group_name | 
	non_unique_parameter_fragment]
sp_configure "configuration file", 0, {"write" | "read" | "verify" | "restore"} 
	"file_name"

Parameters

configname

displays the current value, default value, most recently changed value, and amount of memory used by the setting for all parameters matching parameter.

configvalue

resets configname to configvalue and displays the current value, default value, configured value, and amount of memory used by configname.

sp_configure configname, 0, “default” resets configname to its default value and displays current value, default value, configured value, and amount of memory used by configname.

group_name

displays all configuration parameters in group_name, their current values, their default values, the value (if applicable) to which they have most recently been set, and the amount of memory used by this setting.

non_unique_parameter_fragment

displays all parameter names that match non_unique_parameter_fragment, their current values, default values, configured values, and the amount of memory used.

file_name

is the name of the file you want to us sp_configure on.

write

creates file_name from the current configuration. If file_name already exists, a message is written to the error log and the existing file is renamed using the convention file_name.001, file_name.002, and so on. If you have changed a static parameter but have not restarted your server, “write” gives you the currently running value for that parameter.

read

performs validation checking on values contained in file_name and reads those values that pass validation into the server. If any parameters are missing from file_name, the current running values for those parameters are used.

verify

performs validation checking on the values in file_name.

restore

creates file_name with the values in sysconfigures. This is useful if all copies of the configuration file have been lost and you need to generate a new copy.

Examples

Example 1

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

sp_configure

Example 2

Displays all configuration parameters that include the word “identity”:

sp_configure "identity"

Configuration option is not unique.

Parameter Name      Default Memory Used Config Value Run Value Unit Type
--------------      -------- ----------- ------------ --------- ------ ----
identity burning set       1         0            1        1     id    static
identity grab size         0         0            0        0     id    dyna
size of auto identit      10         0           10       10     bytes dyna
. . . 

Example 3

Sets the system recovery interval in minutes to 3 minutes:

sp_configure "recovery interval in minutes", 3

Parameter Name      Default Memory Used Config Value Run Value Unit Type
--------------       ------- ----------- ------------ --------- ------ ----
recovery interval   5        0          3            3         min   dyn

Configuration option changed. The SQL Server need not be rebooted since the option is dynamic.

Example 4

Resets the value for number of devices to the Adaptive Server default:

sp_configure "number of device", 0, "default"

Usage

Permissions

Any user can execute sp_configure to display information about parameters and their current values.

Only System Administrators and System Security Officers can execute sp_configure to modify configuration parameters.

Only System Security Officers can execute sp_configure to modify values for:

System Administrators can modify all other parameters.

See also

Commands set

System procedures sp_dboption, sp_displaylevel, sp_helpconfig, sp_monitorconfig