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 manually executing the checkpoint.

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
allow wide dol rows
async log service
auto identity
dbo use only
ddl in tran
delayed commit
deferred table allocation
enforce dump tran sequence
full logging for all
full logging for alter table
full logging for reorg rebuild
full logging for select into
identity in nonunique index
no chkpt on recovery
no free space acctg
read only
scratch database
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 “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

Example 10

Enforces the dump tran sequence for the big_db database:

sp_dboption 'big_db', 'enforce dump tran sequence',
true

Example 11

Enables full logging for select into and alter table commands in the mydb database:

Example 12

Enables deferred table creation for the pubs2 database:

sp_dboption pubs2, 'deferred table allocation', true

Usage


Full logging

By default, select into, certain types of alter table, and reorg rebuild are run in minimally logged mode. Before executing these commands, first set the select into/bulk copy database option to true to allow Adaptive Server to break the dump sequence—that is, to perform operations that prevent the ability to use dump transaction.

When you use the “full logging for [select into | alter table | reorg rebuild | all]” option, the command is run with full logging. Any previously set value of select into/bulk copy becomes irrelevant for any of the now-fully logged commands.

Full logging for fast bcp and parallel sort is not supported, and cannot take place unless you set select into/bulk copy to true.

Once the operation is set to run with full logging, you can run dump transaction/load transaction and recovery for these operations, just like any other fully logged operation.

The syntax to fully log commands that are, by default, minimally logged is:

sp_dboption dbname, “full logging for 
	[select into | alter table | reorg rebuild | all]”,
	true | false

where:

NoteThe syntax requires that you specify what you want to fully log; “full logging” by itself is not a valid option.

When you use any of the full logging for option, the command is run with full logging. Any previously set value of select into/bulk copy/pllsort becomes irrelevant for any of the now-fully logged commands. Full logging for fast bcp and parallel sort is not supported and cannot take place unless you set select into/bulk copy/pllsort to true.

Once the operation is set to run with full logging, you can run dump transaction/load transaction and recovery for these operations, just like any other fully logged operation.

The dboption is “full logging for all” and not just “full logging” on its own.

NoteThe execution of a fully logged select into, alter table, or reorg rebuild command may require a significant amount of log space to accommodate the transaction log.


Shrinking the log

Issuing select into, alter table, and reorg rebuild when full logging is enabled can greatly increase the demand for log space, particularly for large tables. You may need to increase the size of the log. Once you have completed the command, you may remove the extra log space using the alter database log off command. See alter database and “Shrinking log space” in System Administration Guide Volume I.

You cannot set full logging for select into, alter database, or reorg rebuild for:

You can change the settings of:

These restrictions apply because none of the databases allow you to execute a dump transaction command. The use of fully recoverable DDLs enables dump transaction.


Allowing wide rows

allow wide dol rows configures databases to allow wide, variable-length data-only locked (DOL) rows.


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.


Considerations for enforce dump tran sequence

enforce dump tran sequence prevents operations that disallow a subsequent dump transaction:

You can set this option to true, only if the database:


Database options

Permissions

The permission checks for sp_dboption differ based on your granular permissions settings.

Granular permissions enabled

With granular permissions enabled, you must be the database owner or a user with own database privilege on the database.

Any user can display database options.

Granular permissions disabled

With granular permissions disabled, you must be the database owner or a user with sa_role.

Any user can display database options. A user aliased to the database owner cannot execute sp_dboption to change 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