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.
sp_configure [configname [, configvalue] | group_name | non_unique_parameter_fragment]
sp_configure "configuration file", 0, {"write" | "read" | "verify" | "restore"}
"file_name"
displays the current value, default value, most recently changed value, and amount of memory used by the setting for all parameters matching parameter.
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.
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.
displays all parameter names that match non_unique_parameter_fragment, their current values, default values, configured values, and the amount of memory used.
is the name of the file you want to us sp_configure on.
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.
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.
performs validation checking on the values in file_name.
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.
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
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 . . .
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.
Resets the value for number of devices to the Adaptive Server default:
sp_configure "number of device", 0, "default"
Any user can execute sp_configure to display information about parameters and their current values, but not to modify parameters. System Administrators can execute sp_configure to change the values of most configuration parameters. Only System Security Officers can execute certain parameters. These are listed under “Permissions” in this section.
sp_configure allows you to specify the value for configuration paramters in unit specifiers. The unit specifiers are p or P for pages, m or M for megabytes, and g or G for gigabytes. If you do not specify a unit, and you are configuring a parameter that controls memory, Adaptive Server uses the logical page size for the basic unit.
When you execute sp_configure to modify a dynamic parameter:
The configuration and run values are updated.
The configuration file is updated.
The change takes effect immediately.
When you execute sp_configure to modify a static parameter:
The configuration value is updated.
The configuration file is updated.
The change takes effect only when you restart Adaptive Server.
When issued with no parameters, sp_configure displays a report of 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:
The default column in the report displays the value Adaptive Server is shipped with. If you do not explicitly reconfigure a parameter, it retains its default value.
The memory used column displays the amount of memory used by the parameter at its current value in kilobytes. Some related parameters draw from the same memory pool. For instance, the memory used for stack size and stack guard size is already accounted for in the memory used for number of user connections. If you added the memory used by each of these parameters separately, it would total more than the amount actually used. In the memory used column, parameters that “share” memory with other parameters are marked with a hash mark (#).
The config_value column displays the most recent value to which the configuration parameter has been set with sp_configure.
The run_value column displays the value being used by Adaptive Server. It changes after you modify a parameter’s value with sp_configure and, for static parameters, after you restart Adaptive Server. This is the value stored in syscurconfigs.value.
If the server uses a case-insensitive sort
order, sp_configure with no parameters
returns a list of all configuration parameters and groups in alphabetical
order with no grouping displayed.
Each configuration parameter has an associated display level. There are three display levels:
The “basic” level displays only the most basic parameters. It is appropriate for very general server tuning.
The “intermediate” level displays parameters that are somewhat more complex, as well as showing you all the “basic” parameters. This level is appropriate for a moderately complex level of server tuning.
The “comprehensive” level displays all parameters, including the most complex ones. This level is appropriate for users who do highly detailed server tuning.
The default display level is “comprehensive”. Setting one of the other display levels lets you work with a subset of the configuration parameter, shortening the amount of information displayed by sp_configure.
The syntax for showing your current display level is:
sp_displaylevel
For information on the individual configuration parameters, see the System Administration Guide.
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:
allow procedure grouping
allow select on syscomments.text
allow updates
audit queue size
auditing
current audit table
remote access
suspend auditing when full
systemwide password expiration
System Administrators can modify all other parameters.
Commands set
System procedures sp_dboption, sp_displaylevel, sp_helpconfig, sp_monitorconfig
