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


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




  • When you enable the "erase residual data" setting at the database level, any operation that results in deallocation is followed by the cleaning of its pages. By default, this option is disabled

  • You cannot change master database option settings.

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

See also:
  • alter table, checkpoint, 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, select in Reference Manual: Commands

  • See the System Administration Guide for more information on database options.

  • bcp in the Utility Guide


The permission checks for sp_dboption differ based on your granular permissions settings.


With granular permissions enabled, you must be the database owner or a user with own database privilege on the database.

Any user can display database options.


With granular permissions disabled, you must be the database owner or a user with sa_role.

Any user can display database options. A user aliased to the database owner cannot execute sp_dboption to change database options.


Values in event and extrainfo columns from the sysaudits table are:



Audit option


Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

Related reference