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. Valid values are directio or dsync:

• directio – enables Adaptive Server to write directly to disk, bypassing the operating system’s buffer system.

• dsync – enables updates to the device take place directly on the storage media, or are buffered by the UNIX file system

  The directio and dsync options are mutually exclusive; you cannot specify “true” for both at the same time.

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

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

• The master database option settings cannot be changed.

• 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.

Asynchronous log service (ALS) options

• The ALS option is disabled by default.

• The ALS option cannot be enabled in system databases, such as master or model.

• The ALS option is persistent; once you enable ALS on a specified database, you can dump and reload the database without disabling ALS. To disable this feature, you must use sp_dboption to set the parameter to false.

Database options

• The abort tran on log full option determines the fate of a transaction that is running when the last-chance threshold is crossed in the log segment of the specified database. The default value is false, meaning that the transaction is suspended and is awakened only when space has been freed. If you change the setting to true, all user queries that need to write to the transaction log are killed until space in the log has been freed.

• Setting the allow nulls by default option to true changes the default value of a column from not null to null, in compliance with the SQL standards. The Transact-SQL default value for a column is not null, meaning that null values are not allowed in a column unless null is specified in the create table or alter table column definition. allow nulls by default true reverses this.

• While the auto identity option is set to true (on), a 10-digit IDENTITY column is defined in each new table that is created without specifying either a primary key, a unique constraint, or an IDENTITY column. The column is not visible when you select all columns with the select * statement. To retrieve it, you must explicitly mention the column name, SYB_IDENTITY_COL, in the select list.

  To set the precision of the automatic IDENTITY column, use the size of auto identity column configuration parameter.

  Though you can set auto identity to true in tempdb, it is not recognized or used, and temporary tables created there do not automatically include an IDENTITY column.

  For a report on indexes in a particular table that includes the IDENTITY column, execute sp_helpindex.

• While the dbo use only option is set to true (on), only the database’s owner can use the database.

• When the ddl in tran option is set to true (on), you can use certain data definition language commands in transactions. If ddl in tran is true in a particular database, commands such as create table, grant, and alter table are allowed inside transactions in that database. If ddl in tran is true in the model database, the commands are allowed inside transactions in all databases created after ddl in tran was set in model.

  WARNING! Data definition language (DDL) commands hold locks on system tables such as sysobjects. Avoid using them inside transactions; if you must use them, keep the transactions short.

  Using any DDL commands on tempdb within transactions may cause your system to grind to a halt. Always leave ddl in tran set to false in tempdb.

• The following commands can be used inside a user-defined transaction when the ddl in tran option is set to true:

  alter table – clauses other than partition and unpartition are allowed create default create index create procedure create rule create schema create table create trigger create view

  drop default drop index drop procedure drop rule drop table drop trigger drop view grant revoke

• The following commands cannot be used inside a user-defined transaction under any circumstances:

  alter database alter table...lock alter table...partition alter table...unpartition create database disk init dump database

  dump transaction drop database load transaction load database select into truncate table update statistics

  In addition, system procedures that create temporary tables or change the master database cannot be used inside user-defined transactions.

• The identity in nonunique index option automatically includes an IDENTITY column in a table’s index keys, so that all indexes created on the table are unique. This database option makes logically nonunique indexes internally unique, and allows these indexes to be used to process updatable cursors and isolation level 0 reads.

  The table must already have an IDENTITY column for the identity in nonunique index option to work, either from a create table statement or by setting the auto identity database option to true before creating the table.

  Use identity in nonunique index if you plan to use cursors and isolation level 0 reads on tables with nonunique indexes. A unique index ensures that the cursor will be positioned at the correct row the next time a fetch is performed on that cursor. If you plan to use cursors on tables with unique indexes and any isolation level, you may want to use the unique auto_identity index option.

  For a report on indexes in a particular table that includes the IDENTITY column, execute sp_helpindex.

• The no free space acctg option suppresses free-space accounting and execution of threshold actions for the non-log segments. This speeds recovery time because the free-space counts are not recomputed for those segments.

• The no chkpt on recovery option is set to true (on) when an up-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 database. Then, at intervals, the transaction log of the primary database is dumped and loaded into the secondary database.

  If this option is set to false (off), the default condition, a checkpoint record is added to a database after it is recovered when you restart Adaptive Server. This checkpoint, which ensures that the recovery mechanism will not be rerun unnecessarily, changes the sequence number and causes a subsequent load of the transaction log from the primary database to fail.

  Setting this option to true (on) for the secondary database causes it not to get a checkpoint from the recovery process so that subsequent transaction log dumps from the primary database can be loaded into it.

• The read only option means that users can retrieve data from the database, but cannot modify any data.

• Setting the select into/bulkcopy/pllsort option to true (on) enables the use of writetext, select into a permanent table, “fast” bulk copy into a table that has no indexes or triggers, using bcp or the bulk copy library routines, and parallel sort. A transaction log dump cannot recover these minimally logged operations, so dump transaction to a dump device is prohibited. After non-logged operations are completed, set select into/bulk copy/pllsort to false (off) and issue dump database.

  Issuing the dump transaction statement after unlogged changes have been made to the database with select into, bulk copy, or parallel sort produces an error message instructing you to use dump database instead. (The writetext command does not have this protection.)

  You do not have to set the select into/bulkcopy/pllsort option to true in order to select into a temporary table, since tempdb is never recovered. The option need not be set to true in order to run bcp on a table that has indexes, because tables with indexes are always copied with the slower version of bulk copy and are logged.

• When single user is set to true, only one user at a time can access the database (single-user mode).

  You cannot set single user to true in a user database from within a stored procedure or while users have the database open. You cannot set single user to true for tempdb.

• The trunc log on chkpt option means that if the transaction log has more than 50 rows of committed transactions, the transaction log is truncated (the committed transactions are removed) every time the checkpoint checking process occurs (usually more than once per minute). When the Database Owner runs checkpoint manually, however, the log is not truncated. It may be useful to turn this option on while doing development work, to prevent the log from growing.

  While the trunc log on chkpt option is on, dump transaction to a dump device is prohibited, since dumps from the truncated transaction log 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.

• The delayed commit option is disabled by default. When this is enabled, all local transactions use delayed commits. That is, at the time of commit, control returns to the client without waiting for the I/O on the log pages to complete, and the I/O is not issued on the last log buffer for delayed commit transactions. Delayed commits are not used when both delayed commit and ALS options are enabled for a database.

• When the unique auto_identity index option is set to true, it adds an IDENTITY column with a unique, nonclustered index to new tables. By default, the IDENTITY column is a 10-digit numeric datatype, but you can change this default with the size of auto identity column configuration parameter. As with auto identity, the IDENTITY column is not visible when you select all columns with the select * statement. To retrieve it, you must explicitly mention the column name, SYB_IDENTITY_COL, in the select list.

  If you need to use cursors or isolation level 0 reads with nonunique indexes, use the identity in nonunique index option.

  Though you can set unique auto_identity index to true in tempdb, it is not recognized or used, and temporary tables created there do not automatically include an IDENTITY column with a unique index.

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:

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

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF