sp_dboption

Description

Displays or changes database options, and enables the asynchronous log service feature.

Syntax

sp_dboption [dbname, optname, optvalue [, dockpt]]

Parameters

dbname

is the name of the database in which the option is to be set. You must be using master to execute sp_dboption with parameters (that is, to change a database option). You cannot, however, change option settings in the master database.

optname

is the name of the option to be set. Adaptive Server understands any unique string that is part of the option name. Use quotes around the option name if it is a keyword or includes embedded blanks or punctuation.

You can turn on more than one database option at a time. You cannot change database options inside a user-defined transaction.

optvalue

is the value of the setting. true turns the option on, and false turns it off.

dockpt

specifies whether sp_dboption performs the checkpoint command on dbname. The default value is 1, which automatically performs checkpoint. You can run checkpoint on the dbname by performing the checkpoint command manually.

Examples

Example 1

Displays a list of the database options:

sp_dboption
Settable database options
 database_options
 ------------------------
abort tran on log full
allow nulls by default
async log service
auto identity
dbo use only
ddl in tran
delayed commit
disable alias access
identity in nonunique index
no chkpt on recovery
no free space acctg
read only
select into/bulkcopy/pllsort
single user
trunc log on chkpt
trunc. log on chkpt.
unique auto_identity index

Example 2

Makes the database pubs2 read only. :

1> use pubs2
2> go
1> master..sp_dboption pubs2, "read", true
2> go
Database option 'read only' turned ON for database 'pubs2'.
Running CHECKPOINT on database 'pubs2' for option 'read only' to take effect.
(return status = 0) 

The read string uniquely identifies the read only option from among all available database options. Note the use of quotes around the keyword read

Example 3

Makes the database pubs2 writable again, but by specifying 0 for the dockpt option, you see the message, “Run the CHECKPOINT command in the database that was changed”:

1> use pubs2
2> go
1> master..sp_dboption pubs2, "read", false, 0
2> go
Database option 'read only' turned OFF for database 'pubs2'.
Run the CHECKPOINT command in the database that was changed.
(return status = 0) 

To manually perform a checkpoint on pubs2, enter:

1> checkpoint
2> go

Example 4

Allows select into, bcp and parallel sort operations on tables in the pubs2 database. The select into string uniquely identifies the select into/ bulkcopy option from among all available database options:

use pubs2
go
master..sp_dboption pubs2, "select into", true
go

NoteQuotes are required around the option because of the embedded space.

Example 5

Automatically defines 10-digit IDENTITY columns in new tables created in mydb. The IDENTITY column, SYB_IDENTITY_COL, is defined in each new table that is created without specifying either a primary key, a unique constraint, or an IDENTITY column:

use mydb 
go
master..sp_dboption mydb, "auto identity", true
go

Example 6

Automatically includes an IDENTITY column in the mydb tables’ index keys, provided these tables already have an IDENTITY column. All indexes created on the tables will be internally unique:

use master 
go 
sp_dboption mydb, "identity in nonunique index", true 
go 
use mydb 
go

Example 7

Automatically includes an IDENTITY column with a unique, nonclustered index for new tables in the pubs2 database:

use master 
go 
sp_dboption pubs2, "unique auto_identity index", true 
go 
use pubs2 
go

Example 8

Sets asynchronous log service (ALS) in a specified database, enabling the user log cache and the log writer threads.

sp_dboption "mydb", "async log service", true
use mydb

Example 9

Disables ALS in a specified database.

sp_dboption "mydb", "async log service", false
use mydb

Usage


Asynchronous log service (ALS) options

Enabling async log service (ALS) allows for greater scalability in Adaptive Server, providing higher throughput in logging subsystems for high-end symmetric multiprocessor systems.


Database options

Permissions

Only a System Administrator or the Database Owner can execute sp_dboption with parameters to change database options. A user aliased to the Database Owner cannot execute sp_dboption to change database options. Any user can execute sp_dboption with no parameters to view database options.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Documents See the System Administration Guide for more information on database options.

Commands checkpoint, select

System procedures sp_configure, sp_helpdb, sp_helpindex, sp_helpjoins

Utilities bcp