Setting Database Options
A number of database-level options that determine the characteristics of the database can be set for each database. Only the SA, database owner, db creator fixed server roles and db_owner fixed database roles 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 SET clause of the ALTER DATABASE statement, the sp_dboption system stored procedure or, in some cases, SQL Server Enterprise Manager.
Note Server-wide settings are set using the sp_configure system stored procedure or SQL Server Enterprise Manager
After you set a database option, a checkpoint is automatically issued that causes the modification to take effect immediately.
To change the default values for any of the database options for newly created databases, change the appropriate database option in the model database. For example, if you want the default setting of the AUTO_SHRINK database option to be ON for any new databases subsequently created, set the AUTO_SHRINK option for model to ON.
There are five categories of database options:
- Automatic options
- Cursor options
- Recovery options
- SQL options
- State options
- Service Broker
Containment :- Containment type
Specify none or partial to designate if this is a contained database. For more information about contained databases
Specify whether the database shuts down cleanly and frees resources after the last user exits. Possible values are True and False. When True, the database is shut down cleanly and its resources are freed after the last user logs off.
Auto Create Statistics
Specify whether the database automatically creates missing optimization statistics. Possible values are True and False. When True, any missing statistics needed by a query for optimization are automatically built during optimization.
Specify whether the database files are available for periodic shrinking. Possible values are True and False..
Auto Update Statistics
Specify whether the database automatically updates out-of-date optimization statistics. Possible values are True and False. When True, any out-of-date statistics needed by a query for optimization are automatically built during optimization.
Auto Update Statistics Asynchronously
When True, queries that initiate an automatic update of out-of-date statistics will not wait for the statistics to be updated before compiling. Subsequent queries will use the updated statistics when they are available.
When False, queries that initiate an automatic update of out-of-date statistics, wait until the updated statistics can be used in the query optimization plan.
Setting this option to True has no effect unless Auto Update Statistics is also set to True.
Cursor options control cursor behavior and scope.
When set to ON, any open cursors are closed automatically (in compliance with SQL-92) when a transaction is committed. By default, this setting is OFF and cursors remain open across transaction boundaries, closing only when the connection is closed or when they are explicitly closed.
Connection-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 connection-level SET statement setting CURSOR_CLOSE_ON_COMMIT to OFF for the session when connecting to SQL Server.
The status of this option can be determined by examining the IsCloseCursorsOnCommitEnabled property of the DATABASEPROPERTYEX function.
CURSOR_DEFAULT LOCAL | GLOBAL
When CURSOR_DEFAULT LOCAL is set, and a cursor is not defined as GLOBAL when it is 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 CURSOR_DEFAULT GLOBAL is set, and a cursor is not defined 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. CURSOR_DEFAULT GLOBAL is the default setting.
The status of this option can be determined by examining the IsLocalCursorsDefault property of the DATABASEPROPERTYEX function.
Recovery options controls the recovery model for the database.
RECOVERY FULL | BULK_LOGGED | SIMPLE
When FULL is specified, database backups and transaction log backups are used to provide full recoverability from media failure. All operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged.
When BULK_LOGGED is specified, logging for all SELECT INTO, CREATE INDEX, and bulk loading data operations is minimal and therefore requires less log space. In exchange for better performance and less log space usage, the risk of exposure to loss is greater than with full recovery.
When SIMPLE is specified, the database can be recovered only to the last full database backup or last differential backup.
This recovery option allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages.
When set to ON, this option causes a bit to be reversed for each 512-byte sector in an 8-kilobyte (KB) database page when the page is written to disk. If a bit is in the wrong state when the page is later read by SQL Server, 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.
Note Using battery-backed disk caches can ensure that data is successfully written to disk or not written at all.
If a torn page is detected, an I/O error is raised and the connection is killed. If the torn page is 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 ON.
The current setting of this option can be determined by examining the IsTornPageDetectionEnabled property of DATABASEPROPERTYEX.
Database Read Only
Specify whether the database is read only. Possible values are True and False. 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 using the DROP DATABASE statement. The database cannot be in use when a new value for the Database Read Only option is specified. The master database is the exception, and only the system administrator can use master while the option is being set.
View the current state of the database. It is not editable
Specify which users may access the database. Possible values are:
The normal state for a production database, allows multiple users to access the database at once.
Used for maintenance actions, only one user is allowed to access the database at once.
Only members of the db_owner, dbcreator, or sysadmin roles can use the database.
When True, this database is enabled for database encryption. A Database Encryption Key is required for encryption.
OFFLINE | ONLINE
When OFFLINE is specified, the database is closed and shutdown cleanly and marked offline. The database cannot be modified while the database is offline.
When ONLINE is specified, the database is open and available for use. ONLINE is the default setting.
The status of this option can be determined by examining the Status property of the DATABASEPROPERTYEX function.
READ_ONLY | READ_WRITE
When READ_ONLY is specified, the database is in read-only mode. 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.
- No locking takes place in read-only databases, which can result in faster query performance.
When READ_WRITE is specified, users can retrieve and modify data. READ_WRITE is the default setting.
The status of this option can be determined by examining the Updateability property of the DATABASEPROPERTYEX function.
SINGLE_USER | RESTRICTED_USER | MULTI_USER
SINGLE_USER allows one user at a time to connect to the database. All other user connections are broken. The timeframe for breaking the connection is controlled by the termination clause of the ALTER DATABASE statement. New connection attempts are refused. The database remains in SINGLE_USER mode even if the user who set the option logs off. At that point, a different user (but only one) can connect to the database.
To allow multiple connections, the database must be changed to RESTRICTED_USER or MULTI_USER mode.
RESTRICTED_USER allows only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but it does not limit their number. Users who are not members of these roles are disconnected in the timeframe specified by the termination clause of the ALTER DATABASE statement. Moreover, new connection attempts by unqualified users are refused.
MULTI_USER allows all users with the appropriate permissions to connect to the database. MULTI_USER is the default setting.
The status of this option can be determined by examining the UserAccess property of the DATABASEPROPERTYEX function.
Specify the option used to discover and report incomplete I/O transactions caused by disk I/O errors. Possible values are None, TornPageDetection, and Checksum.
Target Recovery Time (Seconds)
Specifies the maximum bound on the time, expressed in seconds, to recover the specified database in the event of a crash. For more information
FILESTREAM Directory Name
Specify the directory name for the FILESTREAM data associated with the selected database.
FILESTREAM Non-transacted Access
Specify one of the following options for non-transactional access through the file system to FILESTREAM data stored in FileTables: OFF, READ_ONLY, or FULL. If FILESTREAM is not enabled on the server, this value is set to OFF and is disabled.