1 Version 4.0 -- 1/15/89 sp_dboption
______________________________________________________________________
NAME: sp_dboption
FUNCTION:
Displays or changes database options.
SYNTAX:
sp_dboption [dbname, optname, {true | false}]
EXAMPLES:
1) sp_dboption
Displays a list of the database options:
sp_dboption Version 4.0 -- 1/15/89 2
______________________________________________________________________
Settable database options.
database_options
------------------------
ALL SETTABLE OPTIONS
dbo use only
no chkpt on recovery
read only
select into/bulkcopy
single user
trunc. log on chkpt.
(7 rows affected)
2) use master
go
3 Version 4.0 -- 1/15/89 sp_dboption
______________________________________________________________________
sp_dboption pubs, read, true
use pubs
go
checkpoint
Makes the database pubs read only.
3) use master
go
sp_dboption pubs, read, false
use pubs
go
checkpoint
Makes the database pubs writable again.
sp_dboption Version 4.0 -- 1/15/89 4
______________________________________________________________________
PARAMETERS:
dbname - is the name of the database in which you want to set
the option. You must be using master to execute sp_dboption
with parameters (that is, in order to change a database
option).
However, the database name cannot be master-you cannot change
master's database option settings.
optname - is the name of the option you want to set or unset.
SQL Server understands any unique string that is part of the
option name. Use quotes around the option name if it
includes embedded blanks.
{true | false} - true if you want to set the option, false if
you want to unset the option.
COMMENTS:
5 Version 4.0 -- 1/15/89 sp_dboption
______________________________________________________________________
o You cannot change any of the database option settings for the
Master Database.
o To display a list of the user-settable database options, exe-
cute sp_dboption with no parameters from inside the Master
Database.
o For a report on which database options are set in a particular
database, execute sp_helpdb.
o The Database Owner or System Administrator can set or unset
particular database options for all new databases by executing
sp_dboption on model.
o After sp_dboption has been executed, the change does not take
effect until the CHECKPOINT command is issued in the database
for which the option was changed.
o If you change a database option with sp_dboption inside a
sp_dboption Version 4.0 -- 1/15/89 6
______________________________________________________________________
user-defined transaction, and then roll back that transaction,
you must issue a CHECKPOINT command in order to make the
ROLLBACK's effect on the option change take place. Here's an
example:
begin tran
use master
go
sp_dboption orderentry, single, true
go
use orderentry
go
checkpoint
go
rollback tran
7 Version 4.0 -- 1/15/89 sp_dboption
______________________________________________________________________
go
/*
** If the following CHECKPOINT is not issued,
** the orderentry
** database remains single-user.
*/
checkpoint
go
o While the dbo use only option is set on (or true), only the
database's owner can use the database.
o The read only option means that users can retrieve data from
the database, but can't modify any data.
o When single user is set to true, only one user at a time can
access the database.
sp_dboption Version 4.0 -- 1/15/89 8
______________________________________________________________________
o The trunc. log on chkpt. option means that the transaction log
is truncated (committed transactions are removed) every time
the CHECKPOINT checking process occurs (usually more than once
per minute). It may be useful to turn this option on while
doing development work, in order to prevent the log from grow-
ing.
While the trunc. log on chkpt. option is on, DUMP TRANsaction
is prohibited, since dumps from the truncated transaction log
dumps cannot be used to recover from a media failure. Issuing
the DUMP TRANsaction statement produces an error message
instructing you to use DUMP DATABASE instead.
o The select into/bulkcopy option must be set on in order to be
able to use WRITETEXT, SELECT INTO a permanent table, or do a
``fast'' bulk copy into a table that has no indexes.
9 Version 4.0 -- 1/15/89 sp_dboption
______________________________________________________________________
The fast version of bulk copy (using bcp or the bulk copy
library routines), copies data in to tables that have no
indexes. Since these operations are not logged, DUMP TRANsac-
tion is prohibited when this option is set. The user is
instructed to use DUMP DATABASE instead.
You do not have to set the select into/bulkcopy option on in
order to SELECT INTO a temporary table, since the Temporary
Database is never recovered. The option need not be on in
order to run bcp on a table that has indexes, because tables
with indexes are always copied with the slower version, and are
logged.
By default, the select into/bulkcopy option is off in newly
created databases. To change the default situation, turn this
option on in the Model Database.
o The no chkpt on recovery option is set on (true) when an up-
sp_dboption Version 4.0 -- 1/15/89 10
______________________________________________________________________
to-date copy of a database is kept. In these situations, there
is a ``primary'' and a ``secondary'' database. Initially, the
primary database is dumped and loaded into the secondary data-
base. Then, at intervals, the transaction log of the primary
database is dumped and loaded into the secondary database.
If this option is set off (false)-the default condition-a
checkpoint record is added to a database after it is recovered,
when you restart the SQL Server. This checkpoint, which
insures that the recovery mechanism won't unnecessarily be re-
run, changes the sequence number and causes a subsequent load
of the transaction log from the primary database to fail.
Turning on this option for the secondary database causes it not
to get a checkpoint from the recovery process, so that subse-
quent transaction log dumps from the primary database can be
loaded into it.
11 Version 4.0 -- 1/15/89 sp_dboption
______________________________________________________________________
o See System Administration Guide for additional details on data-
base options.
MESSAGES:
No database with the supplied name exists. Run sp_helpdb to get
a list of databases.
No one can change any of master's database option settings.
Executing sp_dboption with no parameters displays a list of the
user-settable options.
Run sp_dboption with no parameters to see options. This message
is generated in two cases: either the option doesn't exist,
or the user does not have permission to set or unset it. Run
the procedure with no parameters to display a list of the
user-settable options.
The name supplied as the optname parameter is not unique. No
database option value was changed. For example, two of the
database options are dbo use only and read only. Using only
sp_dboption Version 4.0 -- 1/15/89 12
______________________________________________________________________
for the optname parameter generates this message because it
matches both names. The complete names that match the string
supplied are printed out so you can see how to make the
optname more specific.
Although all users can look at a list of the settable database
options, only the System Administrator or Database Owner can
set or unset them. You aren't the System Administrator or
the Database Owner.
In order to change a database option (of any database other than
master), execute the sp_dboption procedure, with the
appropriate parameters, while using master.
Either the optname parameter was omitted or the optvalue parame-
ter was something other than true or false.
The change in the database option takes effect only after the
CHECKPOINT command is run.
PERMISSIONS:
13 Version 4.0 -- 1/15/89 sp_dboption
______________________________________________________________________
Execute permission for no parameters (display options only) to
public. Execute permission for parameters (change an option) to
System Administrator and Database Owner of the specified data-
base.
TABLES USED:
master.dbo.spt_values, master.dbo.sysdatabases
SEE ALSO:
sp_configure, sp_helpdb, sp_helpjoins, CHECKPOINT, SELECT