1 Version 4.0 -- 1/15/89 sp_configure
______________________________________________________________________
NAME: sp_configure
FUNCTION:
Displays or changes configuration variables.
SYNTAX:
sp_configure [configname [, configvalue]]
EXAMPLES:
1) sp_configure
Displays a list of all the configuration parameters with
sp_configure Version 4.0 -- 1/15/89 2
______________________________________________________________________
their current and permissible range of values, like this:
name minimum maximum config_value run_value
----------------- ----------- ----------- ------------ ---------
recovery interval 1 32767 0 5
allow updates 0 1 0 0
user connections * 5 30 0 25
memory 1000 2147483647 0 2000
open databases 5 2147483647 0 10
locks 5000 2147483647 0 5000
open objects 100 2147483647 0 500
procedure cache 1 99 0 20
fill factor 0 100 0 0
time slice 50 1000 0 100
database size 2 10000 0 2
tape retention 0 365 0 0
recovery flags 0 1 0 0
3 Version 4.0 -- 1/15/89 sp_configure
______________________________________________________________________
serial number 1 999999 0 0
nested triggers 0 1 0 1
devices 4 256 40 10
remote access 0 1 1 0
remote logins 0 2147483647 0 0
remote sites 0 2147483647 0 0
remote connections 0 2147483647 0 0
pre-read packets 0 2147483647 0 0
upgrade version 0 2147483647 400 400
(22 rows affected, return status = 0)
* SQL Server maximum is 250; the effective number is based
upon your system configuration. The maximum number of con-
nections in your computer environment is stored in the global
variable @@max_connections.
sp_configure Version 4.0 -- 1/15/89 4
______________________________________________________________________
The config_value column of the report contains the value to
which the configuration variable has been set with
sp_configure. It changes after you execute sp_configure.
(This is the value in sysconfigures.value.)
The run_value column contains the value SQL Server is using.
It changes after you run the RECONFIGURE command, or, in some
cases, after SQL Server is rebooted. (This is the value in
syscurconfigs.value.)
5 Version 4.0 -- 1/15/89 sp_configure
______________________________________________________________________
2) sp_configure "recovery interval", 3
Sets the system recovery interval to 3 minutes.
PARAMETERS:
configname - is the name of the configuration option.
SQL Server understands any unique string that is part of the
configuration name.
configvalue - is the value for the configuration option.
COMMENTS:
o All users can execute sp_configure with no parameters
(SQL Server displays a list of all the configuration variables
and their current values) or with one parameter, the name of
the configuration variable (SQL Server displays the value for
that variable).
sp_configure Version 4.0 -- 1/15/89 6
______________________________________________________________________
o The System Administrator can execute sp_configure with both
parameters in order to change the value of one of the confi-
guration variables. The second parameter is the new
config_value.
After sp_configure has been executed, the System Administrator
must issue the RECONFIGURE command to install the changed
value. Then, for all variables except allow updates and
recovery interval, the System Administrator should restart
SQL Server for the new values to take effect.
o Use RECONFIGURE WITH OVERRIDE when you set allow updates on, or
when you set a configuration variable to a value that
SQL Server considers less than optimal.
o To instruct SQL Server to supply a default configuration vari-
able, give the value 0 as the config_value.
o See the System Administration Guide for details.
7 Version 4.0 -- 1/15/89 sp_configure
______________________________________________________________________
MESSAGES:
After changing a configuration variable with sp_configure, the
change does not take affect until the RECONFIGURE command is
issued and (for all but allow updates and recovery interval)
SQL Server is restarted.
The name supplied as the config_name parameter is unknown.
The name supplied as the config_name parameter is not unique. No
configuration variable was changed. For example, two of the
configuration variables are recovery interval and recovery
flags. Using recovery for the config_name parameter gen-
erates this message because it matches both names. The com-
plete names that match the string supplied are printed out so
you can see how to make the config_name more specific.
Although any user can examine the state of the configuration
variables, only the System Administrator can change them.
The config_value supplied is not in the range of permissible
sp_configure Version 4.0 -- 1/15/89 8
______________________________________________________________________
values for the specified configuration variable. For a
display of the range of permissible values, re-run
sp_configure with the name of the configuration variable as
the only parameter.
A config_value of 0 is always legal. It instructs SQL Server
to set the configuration value to its default.
PERMISSIONS:
Execute permission for no parameters or first parameter only to
public. Execute permission for both parameters to System
Administrator only.
TABLES USED:
sysconfigures, syscurconfigs, spt_values
SEE ALSO:
9 Version 4.0 -- 1/15/89 sp_configure
______________________________________________________________________
sp_dboption, RECONFIGURE