Setting Database Options

A number of database options that determine the characteristics of the database can be set for each database. Only the system administrator or database owner can modify these options. These options are unique to each database and do not affect other databases. The database options can be set by using the sp_dboption system stored procedure or, in some cases, SQL Server Enterprise Manager.

Server-wide settings are set by using the sp_configure system stored procedure or SQL Server Enterprise Manager. For more information, see Setting Configuration Options.

After you set a database option, a checkpoint is automatically issued which causes the modification to take effect immediately.

To change the default values for any of the database options for newly created databases, change the database option in the model database. For example, if you want the default setting of the autoshrink database option to be true for any new databases subsequently created, set the autoshrink option in model to true.

These are the database options:

ANSI null default

Allows the user to control the database default nullability. When NULL or NOT NULL are not specified explicitly, a user-defined data type or a column definition will use the default setting for nullability. Nullability is determined by session and database settings. Microsoft® SQL Server™ defaults to NOT NULL. For ANSI compatibility, setting ANSI null default to true changes the database default to NULL.

When this option is set to true, all user-defined data types or columns that are not explicitly defined as NOT NULL during a CREATE TABLE or ALTER TABLE statement default to allowing null values. Columns that are defined with constraints follow constraint rules regardless of this setting.

Session-level settings (set using the SET statement) override the default database setting for ANSI null default. By default, ODBC and OLE DB clients issue a SET statement setting ANSI null default to true for the session when connecting to SQL Server. For more information, see SET ANSI_NULL_DFLT_ON.

The status of this option can be determined by examining the IsAnsiNullDefault property of the DATABASEPROPERTY function.

ANSI nulls

When true, all comparisons to a null value evaluate to NULL (unknown). When false, comparisons of non-Unicode values to a null value evaluate to TRUE if both values are NULL. By default, ANSI nulls is false.

Session-level settings (set using the SET statement) override the default database setting for ANSI nulls. By default, ODBC and OLE DB clients issue a SET statement setting the ANSI nulls to true for the session when connecting to SQL Server. For more information, see SET ANSI_NULLS.

The status of this option can be determined by examining the IsAnsiNullsEnabled property of the DATABASEPROPERTY function.

ANSI warnings

When true, errors or warnings are issued when conditions such as “divide by zero” occur or null values appear in aggregate functions. When false, no warnings are raised when null values appear in aggregate functions, and null values are returned when conditions such as “divide by zero” occur. By default, ANSI warnings is false.

Session-level settings (set using the SET statement) override the default database setting for ANSI warnings. By default, ODBC and OLE DB clients issue a SET statement setting ANSI warnings to true for the session when connecting to SQL Server. For more information, see SET ANSI_WARNINGS.

The status of this option can be determined by examining the IsAnsiWarningsEnabled property of the DATABASEPROPERTY function.

auto create statistics

When true, statistics are automatically created on columns used in a predicate. Adding statistics improves query performance because the SQL Server query optimizer can better determine how to evaluate a query. If the statistics are not used, SQL Server automatically deletes them. When false, statistics are not automatically created by SQL Server; instead, statistics can be manually created. For more information, see Statistical Information.

By default, auto create statistics is true.

The status of this option can be determined by examining the IsAutoCreateStatistics property of the DATABASEPROPERTY function.

auto update statistics

When true, existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed. When false, existing statistics are not automatically updated; instead, statistics can be manually updated. For more information, see Statistical Information.

By default, auto update statistics is true.

The status of this option can be determined by examining the IsAutoUpdateStatistics property of the DATABASEPROPERTY function.

autoclose

When true, the database is closed and shut down cleanly when the last user of the database exits and all processes in the database complete, thereby freeing any resources. By default, this option is set to true for all databases when using SQL Server Desktop Edition, and false for all other editions, regardless of operating system. The database reopens automatically when a user tries to use the database again. If the database was shut down cleanly, the database is not reopened until a user tries to use the database the next time SQL Server is restarted. When false, the database remains open even if no users are currently using the database.

The autoclose option is useful for desktop databases because it allows database files to be managed as normal files. They can be moved, copied to make backups, or even e-mailed to other users. The autoclose option should not be used for databases accessed by an application that repeatedly makes and breaks connections to SQL Server. The overhead of closing and reopening the database between each connection will impair performance.

The status of this option can be determined by examining the IsAutoClose property of the DATABASEPROPERTY function.

autoshrink

When true, the database files are candidates for periodic shrinking. Both data file and log files can be shrunk automatically by SQL Server. When false, the database files are not automatically shrunk during periodic checks for unused space. By default, this option is set to true for all databases when using SQL Server Desktop Edition, and false for all other editions, regardless of operating system.

The only way to free space in the log files so that they can be shrunk is to back up the transaction log, or set trunc. log on chkpt to true. The log files shrink when the log is backed up or truncated. Therefore, setting autoshrink to true will cause the log to shrink only if the log is backed up or truncated.

The autoshrink option causes files to be shrunk when more than 25 percent of the file contains unused space. The file is shrunk to a size where 25 percent of the file is unused space, or to the size of the file when it was created, whichever is greater.

It is not possible to shrink a read-only database.

The status of this option can be determined by examining the IsAutoShrink property of the DATABASEPROPERTY function.

concat null yields null

When true, if one of the operands in a concatenation operation is NULL, the result of the operation is NULL. For example, concatenating the character string “This is” and NULL results in the value NULL, rather than the value “This is”.

When false, concatenating a null value with a character string yields the character string as the result; the null value is treated as an empty character string. By default, concat null yields null is false.

Session-level settings (set using the SET statement) override the default database setting for concat null yields null. By default, ODBC and OLE DB clients issue a SET statement setting concat null yields null to true for the session when connecting to SQL Server. For more information, see SET CONCAT_NULL_YIELDS_NULL.

The status of this option can be determined by examining the IsNullConcat property of the DATABASEPROPERTY function.

cursor close on commit

When true, any open cursors are closed automatically (in compliance with SQL-92) when a transaction is committed. By default, this setting is false and cursors remain open across transaction boundaries, closing only when the connection is closed or when they are explicitly closed.

Session-level settings (set using the SET statement) override the default database setting for cursor close on commit. By default, ODBC and OLE DB clients issue a SET statement setting cursor close on commit to false for the session when connecting to SQL Server. For more information, see SET CURSOR_CLOSE_ON_COMMIT.

The status of this option can be determined by examining the IsCloseCursorsOnCommitEnabled property of the DATABASEPROPERTY function.

dbo use only

Sets a database for use only by a member of the db_owner fixed database role. 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 are not allowed access to this database again unless this option has been set to false. When false, any users who have permission to access the database can use the database. By default, dbo use only is false.

Set this option when you want the database to be inaccessible to users who are not administrators of the database, for example, when you want to change the definitions of tables and so need to prevent users from accessing those tables until the changes have been completed.

The status of this option can be determined by examining the IsDboOnly property of the DATABASEPROPERTY function.

default to local cursor

When true, and cursors are not specified as GLOBAL when created, the scope of the cursor is local to the batch, stored procedure, or trigger in which the cursor was created. The cursor name is valid only within this scope. The cursor can be referenced by local cursor variables in the batch, stored procedure, or trigger, or a stored procedure OUTPUT parameter. The cursor is implicitly deallocated when the batch, stored procedure, or trigger terminates, unless it was passed back in an OUTPUT parameter. If it is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable referencing it is deallocated or goes out of scope.

When false, and cursors are not specified as LOCAL when created, the scope of the cursor is global to the connection. The cursor name can be referenced in any stored procedure or batch executed by the connection. The cursor is implicitly deallocated only at disconnect. By default, default to local cursor is false. For more information, see DECLARE CURSOR.

The status of this option can be determined by examining the IsLocalCursorsDefault property of the DATABASEPROPERTY function.

merge publish

When true, the database can be used for merge replication publications. When false, the database cannot be published for merge replication. By default, merge publish is false.

offline

When true, the database is closed and shutdown cleanly and marked offline. Use this option when a database is to be distributed on removable media. The database cannot be modified while the database is offline. Use the sp_create_removable and sp_certify_removeable system stored procedures to create and check removable databases. Use sp_detach_db and sp_attach_db system stored procedures to detach the removable database from a server and reattach the database to another server. When false, the database is open and available for use. By default, offline is false.

The status of this option can be determined by examining the IsOffline property of the DATABASEPROPERTY function.

published

When replication is installed, this option permits the tables of a database to be published for replication.

When true, this option enables publication.

When false, it disables publishing, drops all publications, and unmarks all transactions that were marked for replication in the transaction log. By default, published is false.

quoted identifier

When true, identifiers can be delimited by double quotation marks and literals must be delimited by single quotation marks. All strings that are delimited by double quotation marks are interpreted as object identifiers. Quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be keywords and can include characters not generally allowed in Transact-SQL identifiers. Double quotation marks cannot be used to delimit literal string expressions; single quotation marks must be used to enclose literal strings. If a single quotation mark (‘) is part of the literal string, it can be represented by two single quotation marks (“). This setting must be true if reserved keywords are used for object names in the database.

When false (default), identifiers cannot be in quotation marks and must follow all Transact-SQL rules for identifiers. Literals can be delimited by either single or double quotation marks. Literal strings in expressions can be delimited by single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.

Session-level settings (set using the SET statement) override the default database setting for quoted identifier. By default, ODBC and OLE DB clients issue a SET statement setting quoted identifier to true when connecting to SQL Server. For more information, see SET QUOTED_IDENTIFIER.

SQL Server also allows identifiers to be delimited by square brackets ([ ]). Bracketed identifiers can always be used, regardless of the setting of quoted identifier. For more information, see Delimited Identifiers.

The status of this option can be determined by examining the IsQuotedIdentifiersEnabled property of the DATABASEPROPERTY function.

read only

When true, defines a database as read-only. Users can retrieve data from the database, but cannot modify the data. Because a read-only database does not allow data modifications:

• Automatic recovery is skipped at system startup.
• Shrinking the database is not possible.

By default, read only is false.

No locking takes place in read-only databases, which can result in faster query performance.

The status of this option can be determined by examining the IsReadOnly property of the DATABASEPROPERTY function.

recursive triggers

When true, allows triggers to fire recursively. Indirect recursion occurs when a trigger fires and performs an action that causes a trigger on another table to fire, thereby causing an update to occur on the original table, which causes the original trigger to fire again. For more information, see the discussion of recursive triggers in Nested Triggers.

When false (default), triggers cannot be fired recursively.

The status of this option can be determined by examining the IsRecursiveTriggersEnabled property of the DATABASEPROPERTY function.

select into/bulkcopy

When true, allows a database to accept nonlogged operations. For more information, see Nonlogged Operations.

After you perform nonlogged operations, a backup of the transaction log cannot be created. For more information, see Creating and Applying Transaction Log Backups. Once all nonlogged operations are completed, set the select into/bulkcopy option to false and issue the BACKUP DATABASE statement. When select into/bulkcopy is enabled, the transaction log is truncated on each checkpoint even if trunc. log on chkpt is not set to true.

By default, the select into/bulkcopy option is false in newly created databases.

The status of this option can be determined by examining the IsBulkCopy property of the DATABASEPROPERTY function.

single user

When true, restricts database access to a single user connection. Any user who accesses the database when single user is set to true can continue to use the database. A new user can access this database only if all other users have disconnected or switched to another database. With this option set, the trunc. log on chkpt. database option is not supported. The log should be truncated after single-user operations are completed. When false, any number of currently connected users can access the database at the same time. By default, single user is false.
Set single user to true to ensure that no one is using the database when:

• Renaming a database.
• Restoring a database.

The status of this option can be determined by examining the IsSingleUser property of the DATABASEPROPERTY function.

subscribed

When true, the database can be subscribed for publication. When false, the database cannot be subscribed for publication. By default, subscribed is false.

torn page detection

This option allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages.

When true, it causes a bit to be flipped for each 512-byte sector in an 8-kilobyte (KB) database page whenever the page is written to disk. If a bit is in the wrong state when the page is later read by SQL Server, then the page was written incorrectly; a torn page is detected. Torn pages are usually detected during recovery because any page that was written incorrectly is likely to be read by recovery.

Although SQL Server database pages are 8 KB, disks perform I/O operations using a 512-byte sector. Therefore, 16 sectors are written per database page. A torn page can occur if the system fails (for example, due to power failure) between the time the operating system writes the first 512-byte sector to disk and the completion of the 8 KB I/O operation. If the first sector of a database page is successfully written before the failure, the database page on disk will appear as updated, although it may not have succeeded.

Using battery-backed disk caches can ensure that data is successfully written to disk or not written at all. In this case, do not set torn page detection to true, for it is not needed.

If a torn page is detected, an I/O error is raised and the connection is killed. If detected during recovery, the database is also marked suspect. The database backup should be restored, and any transaction log backups applied, because it is physically inconsistent. By default, torn page detection is false.

There is little performance impact by enabling torn page detection.

trunc. log on chkpt.

Causes the inactive portion of the transaction log to be truncated (committed transactions are removed) every time the CHECKPOINT process (manual or automatic) occurs. For more information, see Checkpoints and the Active Portion of the Log.

When trunc. log on chkpt. is set to true, the database is in log truncate mode. When a database is in log truncate mode, an automatic checkpoint occurs in the database every time the log becomes either 70 percent full or the number of records in the transaction log reaches the number SQL Server estimates it can process during the time specified by the recovery interval server configuration option. For more information, see Truncating the Transaction Log and recovery interval Option.

SQL Server does not, however, issue the checkpoint if the log cannot be truncated because of an outstanding transaction. When trunc. log on chkpt. is set, SQL Server also attempts a checkpoint on a log full error. It can be useful to select this option while developing the database to prevent the log from growing.

While the trunc. log on chkpt. database option is set to true, a backup of the transaction log cannot be created because the truncated transaction logs in the backups cannot be used to recover from media failure. Issuing the BACKUP LOG statement produces an error message which instructs you to use the BACKUP DATABASE statement. For more information, see Creating and Applying Transaction Log Backups.

By default, trunc. log on chkpt. is true when using SQL Server Desktop Edition, and false for all other editions.

The tempdb database is always truncated by the checkpoint process even if the trunc. log on chkpt. database option is set to false.

The status of this option can be determined by examining the IsTruncLog property of the DATABASEPROPERTY function.

To change the configuration settings for a database

See Also