Displays or changes database options, and enables the asynchronous log service feature.
sp_dboption [dbname, optname, optvalue [, dockpt]]
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.
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.
is the value of the setting. true turns the option on, and false turns it off.
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.
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 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
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.
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
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
Quotes are required around the option because of the
embedded space.
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
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
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
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
Disables ALS in a specified database.
sp_dboption "mydb", "async log service", false use mydb
Enforces the dump tran sequence for the big_db database:
sp_dboption 'big_db', 'enforce dump tran sequence', true
Enables full logging for select into and alter table commands in the mydb database:
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>
The master database option settings cannot be changed.
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.
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:
full logging for select into – in order to have a select into proxy table fully logged, set the “full logging for select into” option to true on the remote server that hosts the actual table. If you set the full logging for select into option to false on the server that hosts the actual table, the command is then executed with minimal logging in that database and the dump transaction sequence breaks.
full logging for alter table – enables full logging for these versions of alter table that require data movement:
alter table add column not null
alter table drop column not null
alter table modify datatype of not null column
Other variants of alter table are already executed in fully logged mode.
full logging for reorg rebuild – involves table data movement. This has no impact on the reorg rebuild index command, which is already fully logged. This parameter enables full logging for reorg rebuild table statements. When you do not set this option (or set this option to false), Adaptive Server executes the reorg rebuild table command with minimal logging.
full logging for all – enables all the above full logging options. Setting all to false disables all the full logging options.
The 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.
The 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.
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:
The master database
In-memory databases
You can change the settings of:
Any database that has mixed log and data segments, but the option is ignored until such time as the database is altered to no longer have mixed log and data segments.
A database that does not have a durability level of full, but the option is ignored until the database is altered to have full durability.
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.
allow wide dol rows configures databases to allow wide, variable-length data-only locked (DOL) rows.
You must enable allow wide dol rows separately for each database.
You can set the allow wide dol rows database option in user databases only. You cannot set the allow wide dol rows database option for the master database.
Enabling allow wide dol rows in an Adaptive Server configured with page size of 8K or less has no effect.
Disabling allow wide dol rows prevents Adaptive Server from creating wide, variable-length DOL rows; it does not prevent you from selecting data that includes such rows. However, until you enable allow wide dol rows, you cannot change rows that contain wide data, unless the change produces rows that no longer contain wide data.
Temporary databases cannot use wide DOL worktables until you enable their allow wide dol rows setting. If you use tempdb groups, enable allow wide dol rows either for all databases in the group or for none of them, so worktable and query processing behavior is consistent across the group, regardless of the tempdb to which a particular user session is bound.
Enabling async log service (ALS) allows for greater scalability in Adaptive Server, providing higher throughput in logging subsystems for high-end symmetric multiprocessor systems.
The ALS option is disabled by default.
You cannot enable the ALS option 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.
enforce dump tran sequence prevents operations that disallow a subsequent dump transaction:
false – (the default) does not affect operations that interfere with dump transactions.
true – disallows operations that prevent a dump transaction.
You can set this option to true, only if the database:
Is a dedicated log database.
Is not an archive database.
Is not a local or global temporary database.
Is not read-only.
Was not brought online for standby access.
Has full durability. Databases with at_shutdown and no_recovery durability are not allowed.
Has select into/bulk copy/pllsort or trunc log on chkpt set to false. If any of these options are true, they automatically reset to false.
Does not need a dump database due to one of the following reasons. Perform a dump database before setting this database option to true.
A partially logged update has been done, for example, select into, alter table modify, reorg rebuild, fast bcp, and writetext.
The transaction log was truncated.
It is a newly created or upgraded database.
If the database option enforce dump tran sequence is true, you cannot:
Set select into/bulk copy/pllsort to true. Commands with partial logging are not allowed.
Set trunc log on chkpt to true. The log cannot be truncated by the checkpoint process.
Execute dump tran with truncate_only or dump tran with no_log. The log cannot be truncated without dumping it to an archive device.
Mark the database as read-only.
Change durability from full to at_shutdown or no_recovery.
Change to be a mixed-log-and-data database. In cases like load database and dbcc findstranded where the database may be changed to mixed log and data.
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.
You cannot use allow nulls by default to change the nullibility of a column during select into statements. Instead, use convert to specify the nullibility of the resulting columns.
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:
|
The following commands cannot be used inside a user-defined transaction under any circumstances:
|
In addition, system procedures that create temporary tables or change the master database cannot be used inside user-defined transactions.
identity in nonunique index 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.
Do not confuse the identity in nonunique index option with unique auto_identity index, which is used to add an IDENTITY column with a unique, nonclustered index to new tables.
For a report on indexes in a particular table that includes the IDENTITY column, execute sp_helpindex.
no free space acctg suppresses free-space accounting and execution of threshold actions for the nonlog segments. This speeds recovery time because the free-space counts are not recomputed for those segments. no free space acctg disables updating the rows-per-page value stored for each table, so system procedures that estimate space usage may report inaccurate values.
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.
select into/bulkcopy/pllsort must be set to on to perform operations that do not keep a complete record of the transaction in the log, which include:
Using the writetext utility.
Doing a select into a permanent table.
Doing a “fast” bulk copy with bcp. By default, fast bcp is used on tables that do not have indexes.
Performing a parallel sort.
A transaction log dump cannot recover these minimally logged operations, so dump transaction to a dump device is prohibited. However, you can still use dump transaction...with no_log and dump transaction...with truncate_onlyAfter 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.
Setting select into/bulkcopy/pllsort does not block log dumping, but making minimally logged changes to data does block the use of a regular dump transaction. .
By default, select into/bulkcopy/pllsort is turned off in newly created databases. To change the default, turn this option on in the model database.
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.
trunc log on chkpt is off in newly created databases. To change the default, turn this option on in the model database.
WARNING! If you set trunc log on chkpt on in model, and you need to load a set of database and transaction logs into a newly created database, be sure to turn the option off in the new database.
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.
The unique auto_identity index option provides a mechanism for creating tables that have an automatic IDENTITY column with a unique index that can be used with updatable cursors. The unique index on the table ensures that the cursor is positioned at the correct row after a fetch. (If you are using isolation level 0 reads and need to make logically nonunique indexes internally unique so that they can process updatable cursors, use the identity in nonunique index option.)
In some cases, the unique auto_identity index option can avoid the Halloween problem for the following reasons:
Users cannot update an IDENTITY column; hence, it cannot be used in the cursor update.
The IDENTITY column is automatically created with a unique, nonclustered index so that it can be used for the updatable cursor scan.
For more information about the Halloween problem, IDENTITY columns, and cursors, see Chapter 18, “Cursors: Accessing Data,” in the Transact-SQL Users Guide and Chapter 8, “Optimization for Cursors,” in Performance and Tuning Series: Query Processing and Abstract Plans.
Do not confuse the unique auto_identity index option with the identity in nonunique index option, which is used to make all indexes in a table unique by including an IDENTITY column in the table’s index keys.
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.
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 |
|
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
