Share via


sp_dboption (Transact-SQL)

Displays or changes database options. Do not use sp_dboption to modify options on either the master database or the tempdb database.

Important

This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER DATABASE instead. To modify database options that are associated with replication (merge publish, published, subscribed), use sp_replicationdboption.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_dboption [ [ @dbname= ] 'database' ] 
    [ , [ @optname= ] 'option_name' ] 
    [ , [ @optvalue= ] 'value' ] 
[;]

Arguments

  • [ @dbname= ] 'database'
    Is the name of the database in which to set the specified option. database is sysname, with a default of NULL.

  • [ @optname= ] 'option_name'
    Is the name of the option to set. You do not have to enter the complete option name. SQL Server recognizes any part of the name that is unique. Enclose the option name with quotation marks when it includes embedded blanks or is a keyword. If this parameter is omitted, sp_dboption lists the options that are on. option_name is varchar(35), with a default of NULL.

  • [ @optvalue=] 'value'
    Is the new setting for option_name. If this parameter is omitted, sp_dboptionreturns the current setting. value can be true, false, on, or off. value is varchar(10), with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Result Sets

The following table shows the result set when no parameters are supplied.

Column name

Data type

Description

Settable database options

nvarchar(35)

All of the settable database options.

The following table shows the result set when database is the only supplied parameter.

Column name

Data type

Description

The following options are set:

nvarchar(35)

The options that are set for the specified database.

The following table shows the result set when option_name is supplied.

Column name

Data type

Description

OptionName

nvarchar(35)

Name of the option.

CurrentSetting

char(3)

Whether the option is on or off.

If value is supplied, sp_dboption does not return a result set.

Remarks

The following table lists the options set by sp_dboption. For more information about each option, see Setting Database Options.

Option

Description

auto create statistics

When true, any missing statistics needed by a query for optimization are automatically built during optimization. For more information, see CREATE STATISTICS (Transact-SQL).

auto update statistics

When true, any out-of-date statistics needed by a query for optimization are automatically built during optimization. For more information, see UPDATE STATISTICS (Transact-SQL).

autoclose

When true, the database is shut down cleanly and its resources are freed after the last user logs off.

autoshrink

When true, the database files are candidates for automatic periodic shrinking.

ANSI null default

When true, CREATE TABLE follows the ISO rules to determine whether a column allows null values.

ANSI nulls

When true, all comparisons to a null value evaluate to UNKNOWN. When false, comparisons of non-UNICODE values to a null value evaluate to TRUE if both values are NULL.

ANSI warnings

When true, errors or warnings are issued when conditions such as "divide by zero" occur.

arithabort

When true, an overflow or divide-by-zero error causes the query or batch to terminate. If the error occurs in a transaction, the transaction is rolled back. When false, a warning message is displayed, but the query, batch, or transaction continues as if no error occurred.

concat null yields null

When true, if either operand in a concatenation operation is NULL, the result is NULL.

cursor close on commit

When true, any cursors that are open when a transaction is committed or rolled back are closed. When false, such cursors remain open when a transaction is committed. When false, rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.

dbo use only

When true, only the database owner can use the database.

default to local cursor

When true, cursor declarations default to LOCAL.

merge publish

When true, the database can be published for a merge replication.

numeric roundabort

When true, an error is generated when loss of precision occurs in an expression. When false, losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result.

offline

When true (on), the database is offline. When false (off), the database is online.

published

When true, the database can be published for replication.

quoted identifier

When true, double quotation marks can be used to enclose delimited identifiers.

read only

When true, users can only read data in the database. Users cannot modify the data or database objects; however, the database itself can be deleted by using the DROP DATABASE statement. The database cannot be in use when a new value for the read only option is specified. The master database is the exception, and only the system administrator can use master while the read-only option is being set.

recursive triggers

When true, enables recursive firing of triggers. When false, prevents direct recursion only. To disable indirect recursion, set the nested triggers server option to 0 using sp_configure.

select into/bulkcopy

Starting with Microsoft SQL Server 2000, if the recovery model of the database is currently set to FULL, using the select into/bulkcopy option resets the recovery model to BULK_LOGGED. The proper way to change the recovery model is by using the SET RECOVERY clause of the ALTER DATABASE statement.

single user

When true, only one user at a time can access the database.

subscribed

When true, the database can be subscribed for publication.

torn page detection

When true, incomplete pages can be detected.

trunc. log on chkpt.

When true, a checkpoint truncates the inactive part of the log when the database is in log truncate mode. This is the only option that you can set for the master database.

Important noteImportant
Starting with SQL Server 2000, setting the trunc. log on chkpt. option to true sets the recovery model of the database to SIMPLE. Setting the option to false sets the recovery model to FULL.

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 executes in the database for which the option was changed. This causes the change to take effect immediately.

sp_dboption changes settings for a database. Use sp_configure to change server-level settings, and the SET statement to change settings that affect only the current session.

Permissions

To display the complete list of database options and their current values requires membership in the public role. To change the value of a database option, requires membership in the db_owner fixed database role.

Examples

A. Setting a database to read-only

The following example makes the AdventureWorks database read-only.

USE master;
GO
EXEC sp_dboption 'AdventureWorks', 'read only', 'TRUE';

B. Turning off an option

The following example makes the AdventureWorks database writable again.

USE master;
GO
EXEC sp_dboption 'AdventureWorks', 'read only', 'FALSE';