sp_dboption

Description

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

Syntax

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

Parameters

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

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:

• 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 deferred table creation for the pubs2 database:

sp_dboption pubs2, 'deferred table allocation', true

Usage

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

  • alter table partition

  Other variants of alter table are already executed in fully logged mode.

  Changing the locking scheme between an allpages-locked table and a datapages-locked/data rows-locked table by alter table lock requires data movement, however, this behavior is not supported by full logging for alter table.

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

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.

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.