sp_dboption System Stored Procedure

For SQL Server 6.5 information, see sp_dboption in What's New for SQL Server 6.5.

Displays or changes database options.

Syntax

sp_dboption [dbname, optname, {true | false}]

where

dbname

Specifies the database in which you want to set the option.

optname

Is the name of the option you want to set or turn off. SQL Server understands any unique string that is part of the option name. Enclose the option name with quotation marks if it includes embedded blanks or is a keyword. The available options are:

ANSI null default

Allows the user to control the database default nullability. When not explicitly defined, a user-defined datatype or a column definition will use the default setting for nullability. Nullability is determined by session settings and database settings. SQL Server defaults to NOT NULL. However, the ANSI-standard is NULL. For ANSI compatibility, setting ANSI null default to TRUE changes the database default nullability to NULL.

With this option set to true, all user-defined datatypes or columns not explicitly defined as NOT NULL during a CREATE TABLE or ALTER TABLE statement will default to allowing NULLs. Columns defined with constraints will follow constraint rules regardless of this setting (for example, PRIMARY KEY columns, IDENTITY columns, and columns of type bit cannot be NULL).

Session settings override the default database setting for ANSI null default. For more information, see the SET statement.

dbo use only

Sets a database for use only by the database owner. Active users of the database can continue to access the database, but no new users are allowed. When active users disconnect or change database context (with the USE statement), they will not be allowed access to this database unless this option has been set to false.

no chkpt on recovery

Defines whether or not a checkpoint record is added to the database after it is recovered during a SQL Server startup. If this option is off (false, the default), a checkpoint record is added.

The no chkpt on recovery option is useful 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 on (true) in the secondary database, no checkpoint record is added to a database after it is recovered, so subsequent transaction log dumps from the primary database can be loaded into it.

offline

Allows a database to be placed online (when set to false, the database is ready to be used) or offline (when set to true, the database is unavailable and cannot be used). This option is primarily used on databases with removable media devices but it can be used on any database.

When a database is placed online, all devices belonging to the specified database are opened (if they are not already open) and marked as non-deferred, and the database is recovered and becomes available for use.

When a database is placed offline, all devices belonging to that database are closed and marked as deferred, except those that contain space belonging to other online databases. Databases placed offline are not recovered automatically at server startup.

Note A database cannot be placed offline if it has an active user.

published

Allows the database to be published for replication. This option does not publish a database. Instead, it permits the tables of a database to be published.

When set to true, this option enables publishing and adds the user repl_subscriber to the database.

When set to false , it disables publishing, drops all publications, unmarks all transactions that were marked for replication in the transaction log, and removes the database user repl_subscriber.

read only

Defines a database as read only. This option means that users can retrieve data from the database but cannot modify anything. Because a read-only database does not allow data modifications, automatic recovery is skipped at system startup.

select into/bulkcopy

Allows a database to accept non-logged operations. Non-logged operations include: using the UPDATETEXT or WRITETEXT statement, using SELECT INTO into a permanent table, using fast bulk copy (bcp), or performing a table load. This option need not be set to run bcp on a table that has indexes, because tables with indexes are always copied with the slower version of bcp and are logged.

After performing non-logged operations, using the DUMP TRANSACTION statement is prohibited. Issuing DUMP TRANSACTION after making unlogged changes to the database with SELECT INTO or bulk copy produces an error message telling you to use DUMP DATABASE instead. Once all non-logged operations are completed, turn off select into/bulkcopy and issue the DUMP DATABASE statement.

By default, the select into/bulkcopy option is turned off in newly created databases. To change the default situation, set this option in the model database.

Note Because SELECT is a keyword, you must enclose select into/
bulkcopy in quotation marks to avoid a syntax error.

single user

Restricts database access to a single user. Any user accessing the database when single user is set to true can continue to use the database. A new user will be allowed into this database only if all other users have disconnected or changed to another database. When this option is set, the trunc. log on chkpt. option is not supported. Truncate the log after single user operations are completed.

subscribed

Allows the database to be subscribed for replication. Setting subscribed to true aliases the login ID of repl_publisher to DBO. This allows the distribution process to replicate to this database. Setting subscribed to false removes the alias.

This stored procedure does not subscribe to a database. It permits a database to subscribe to a published database.

trunc. log on chkpt.

Causes the transaction log to be truncated (committed transactions are removed) every time the CHECKPOINT process occurs (usually 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 set, backups of the transaction log are not allowed since dumps from the truncated transaction log dumps cannot be used to recover from media failure. Issuing the DUMP TRANSACTION statement produces an error message instructing you to use the DUMP DATABASE statement instead.

true | false

Specifies how to set the option. Use true if you want to set the option and false if you want to turn off the option.

Remarks

To display the list of available database options, execute sp_dboption with no parameters. To list all of the configured options for a particular database, execute the sp_helpdb system stored procedure. The only option you can set for the master database is trunc. log on chkpt. The database owner or system administrator can set or turn off particular database options for all new databases by executing sp_dboption on the model database.

After sp_dboption has been executed, a checkpoint will be executed in the database for which the option was changed. This will cause the change to take effect immediately.

For additional details on database options, see the Microsoft SQL Server Administrator's Companion.

Permission

Execute permission to display the list of options only (using sp_dboption with no parameters) defaults to all users. Execute permission to change an option (using sp_dboption with parameters) defaults to the system administrator and the database owner of the database for which the option is to be changed.

Examples

A.    sp_dboption with No Parameters

This example displays a list of the database options.

sp_dboption
go

    Settable database options: 
    -------------------------- 
    ANSI null default          
    dbo use only               
    no chkpt on recovery       
    offline                    
    published                  
    read only                  
    select into/bulkcopy       
    single user                
    subscribed                 
    trunc. log on chkpt.       

B.    Set a Database to Read Only

This example makes the pubs database read only.

sp_dboption pubs, 'read', TRUE
go

    CHECKPOINTing database that was changed.

C.    Turn Off an Option

This example makes the pubs database writable again.

sp_dboption pubs, 'read', FALSE
go

    CHECKPOINTing database that was changed.

D.    Take a Database Offline

This example takes the sales database offline if there are no users accessing the sales database.

USE master
go
sp_dboption sales, offline, TRUE 
go

    CHECKPOINTing database that was changed.

Tables Used

master.dbo.spt_values, master.dbo.sysdatabases

See Also