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][number of histogram steps, n]
sp_configure "configuration file", 0, {"write" | "read" | "verify" | "restore"} 
	"file_name"
sp_configure “max concurrently recovered db”, config_value
sp_configure “number of checkpoint tasks”, config_value

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.

number of histogram steps, n

used when creating an index or running update statistics where you can specify the number of steps when using create index or update statistics. The default is 20.

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.

max concurrently recovered db config_value

sets the limit to the number of databases recovered concurrently.

The default value of config_value is 0, where Adaptive Server determines the number of databases recovered concurrently, based on a self-tuning approach. The number of engines and number of open databases limit the value of this configuration parameter. There is no absolute maximum.The value of 1 indicates serial recovery.

number of checkpoint tasks config_value

sets the limit to the number of checkpoint tasks in Adaptive Server.

The default value of config_value is 1, indicating serial checkpoints. The number of engines and number of open databases limit number of checkpoint tasks. It has an absolute ceiling of 8.

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"

Example 5

To reset the default number of steps for create index and update statistics:

sp_configure 'number of histogram steps', 30

Example 6

To configure 4 databases to be recovered concurrently, enter:

sp_configure "max concurrently recovered db", 4

Example 7

To configure Adaptive Server for the self-tuning approach, enter:

sp_configure "max concurrently recovered db", 0

Example 8

To configure Adaptive Server to have serial recovery, enter:

sp_configure "max concurrently recovered db", 1

Example 9

To start four checkpoint tasks, enter:

sp_configure "number of checkpoint tasks", 4

Example 10

To revert to serial checkpoints, enter:

sp_configure "number of checkpoint tasks", 1

Usage


max concurrently recovered db

This parameter determines the degree of parallelism during database recovery:


number of checkpoint tasks

This parameter configures parallel checkpoints:

Permissions

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

Only System Administrators can execute sp_configure to modify values for:

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

For more information on max concurrently recovered db and number of checkpoint tasks, see Chapter 27, “Backing up and Restoring User Databases,” in the System Administration Guide.

Commands set

System procedures sp_dboption, sp_displaylevel, sp_helpconfig, sp_monitorconfig