sp_dboption

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. The SAP ASE 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 list of database options:

sp_dboption

Settable database options

 database_options
 ------------------------
abort tran on log full
allow incremental dumps
allow nulls by default
allow wide dol rows
async log service
auto identity
dbo use only
ddl in tran
deallocate first text page
deferred table allocation
delayed commit
enforce dump tran sequence
erase residual data
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 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, parallel sort operations on tables in pubs2. 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
```
Note: Quotes are required around the option because of the embedded space.
Example 5 – Automatically defines 10-digit IDENTITY 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 index keys of mydb tables, provided these tables already have an IDENTITY column. All indexes created on the tables are internally unique:
```
use master 
go 
sp_dboption mydb, "identity in nonunique index", true 
go 
use mydb 
go
```
Example 7 – Automatically includes IDENTITY With unique, nonclustered index for new tables in pubs2:
```
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 a dump transaction sequence for big_db:
```
sp_dboption 'big_db', 'enforce dump tran sequence',
true
```

Example 11 – Enables full logging for select into and alter table in mydb:

The create database command creates mydb:

create database mydb on datadev=20 log on logdev=10
go

CREATE DATABASE: allocating 10240 logical pages (20.0 megabytes) on disk
'datadev' (10240 logical pages requested).
CREATE DATABASE: allocating 5120 logical pages (10.0 megabytes) on disk
'logdev' (5120 logical pages requested).
Database 'mydb' is now online.

Turns on the full-logging option for select into in mydb:

sp_dboption "mydb", "full logging for select into", "true"
go

Database option 'full logging for select into' turned ON for database 
'mydb'.
Running CHECKPOINT on database 'mydb' for option 'full logging for select 
into' to take effect.
(return status = 0)

Turns on the full-logging option for alter table in mydb:

sp_dboption "mydb", "full logging for alter table", "true"
go

Database option 'full logging for alter table' turned ON for database 
'mydb'.
Running CHECKPOINT on database 'mydb' for option 'full logging for alter 
table' to take effect.
(return status = 0)

Running sp_helpdb shows the settings for mydb:

sp_helpdb mydb
go

 name db_size owner dbid created      durability status
 ---- ------- ----- ---- ------------ --------- -----------------------
 mydb 30.0 MB  sa      5 Dec 16, 2010 full       full logging for select
                                                 into/alter table

(1 row affected)
 device_fragments size    usage      created             free kbytes
----------------- ------- ---------- ------------------- --------------
 datadev          20.0 MB data only  Dec 16 2010 6:08PM  18696
 logdev           10.0 MB log only   Dec 16 2010 6:08PM  not applicable

--------------------------------------------------------------
log only free kbytes = 10184
(return status = 0)
1>

Example 12 – Enables back-up and restoration of cumulative dumps:
```
sp_dboption mydb, "allow incremental dumps", true 
```
Example 13 – Enables deferred table creation for pubs2:
```
sp_dboption pubs2, 'deferred table allocation', true
```
Example 14 – The syntax to enable the removal of residual data at the database level for these two examples is:
```
sp_dboption dbname, "erase residual data", true
```
The following examples use these two tables:
- create table t1 (col1 int) with "erase residual data" on
- create table t2 (col1 int) with "erase residual data" off
The option to erase residual data is turned on for table t1 because it is set at the database level, so that both the drop table and truncate table commands for t1 result in the cleanup of all residual data from its pages.
Table t2, however, has the erase residual data option turned off explicitly, as it was created with the "erase residual data off" clause. Residual data is not removed, even though the "erase residual data" option is set to true at the database level. As a result, residual data remains, even after running drop table and truncate table on t2:
```
create database db1
go
sp_dboption db1, "erase residual data", true
go
use db1
go
create table t1 (col int)
go
insert t1 values ...
go
create table t2 (col1 int, col2 char(10)) with "erase residual data" off
go
truncate table t1
go
drop table t1
go
truncate table t2
go
drop table t2
go
```
In the second example:
- Table t1 does not have "erase residual data off" set explicitly, but does have it set at the database level, resulting in the removal of residual data from t1 when you run truncate table t1.
- Table t2 has the "erase residual data" option set at creation, because the option was set at the database level. This results in the removal of residual data from t2 when you run truncate table t2.
- Table t3 is marked with "erase residual data off" explicitly, so that even though sp_dboption sets "erase residual data" to true, residual data is not removed when SAP ASE runs truncate table t3.
```
create database db1
go
use db1
go
create table t1 (col int)
go
sp_dboption db1, "erase residual data", true
go
create table t2 (col1 int, col2 char(10))
go
create table t3 (col1 int, col2 char(10)) with "erase residual data" off
go
truncate table t1
go
truncate table t2
go
truncate table t3
go
```
Example 15 – Deallocate the first text page after a NULL update:
```
sp_dboption mydb, "deallocate first text page", true
```

Usage

When you enable the "erase residual data" setting at the database level, any operation that results in deallocation is followed by the cleaning of its pages. By default, this option is disabled
You cannot change master database option settings.
If you enter an ambiguous value for optname, an error message appears. For example, two of the database options are dbo use only and read only. Using “only” for the optname parameter generates a message because it matches both names. The complete names that match the string supplied are printed out so that you can see how to make the optname more specific.
To display a list of database options, execute sp_dboption with no parameters from inside the master database.
For a report on which database options are set in a particular database, execute sp_helpdb.
The no chkpt on recovery option disables the trunc log on chkpt option when both are set with sp_dboption for the same database. This conflict is especially possible in the tempdb database which has trunc log on chkpt set to on as the default.
The database owner or system administrator can set or unset particular database options for all new databases by executing sp_dboption on model.
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.

Permissions

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

Setting	Description
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.
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:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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