ALTER DATABASE SET Options (Transact-SQL)
This topic contains the ALTER DATABASE syntax that is related to setting database options. For other ALTER DATABASE syntax, see ALTER DATABASE (Transact-SQL). Database mirroring and compatibility levels are SET options but are described in separate topics because of their length. For more information, see ALTER DATABASE Database Mirroring (Transact-SQL) and ALTER DATABASE Compatibility Level (Transact-SQL).
ALTER DATABASE database_name
SET
{
{ <optionspec> [ ,...n ] [ WITH <termination> ] }
}
<optionspec>::=
{
<auto_option>
| <change_tracking_option>
| <cursor_option>
| <database_mirroring_option>
| <date_correlation_optimization_option>
| <db_encryption_option>
| <db_state_option>
| <db_update_option>
| <db_user_access_option>
| <external_access_option>
| <parameterization_option>
| <recovery_option>
| <service_broker_option>
| <snapshot_option>
| <sql_option>
}
<auto_option> ::=
{
AUTO_CLOSE { ON | OFF }
| AUTO_CREATE_STATISTICS { ON | OFF }
| AUTO_SHRINK { ON | OFF }
| AUTO_UPDATE_STATISTICS { ON | OFF }
| AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}
<change_tracking_option> ::=
{
CHANGE_TRACKING {
= ON [ <change_tracking_option_list > ] |
<change_tracking_option_list> |
= OFF
}
}
<change_tracking_option_list> ::=
{
( <change_tracking_option> | <change_tracking_option_list> ,
<change_tracking_option> )
}
<change_tracking_option> ::=
{
AUTO_CLEANUP = { ON | OFF }
| CHANGE_RETENTION = { retention_period { DAYS | HOURS | MINUTES } ]
}
<cursor_option> ::=
{
CURSOR_CLOSE_ON_COMMIT { ON | OFF }
| CURSOR_DEFAULT { LOCAL | GLOBAL }
}
<database_mirroring_option>ALTER DATABASE Database Mirroring<date_correlation_optimization_option> ::=
{
DATE_CORRELATION_OPTIMIZATION { ON | OFF }
}
<db_encryption_option> ::=
ENCRYPTION { ON | OFF }
<db_state_option> ::=
{ ONLINE | OFFLINE | EMERGENCY }
<db_update_option> ::=
{ READ_ONLY | READ_WRITE }
<db_user_access_option> ::=
{ SINGLE_USER | RESTRICTED_USER | MULTI_USER }
<external_access_option> ::=
{
DB_CHAINING { ON | OFF }
| TRUSTWORTHY { ON | OFF }
}
<parameterization_option> ::=
{
PARAMETERIZATION { SIMPLE | FORCED }
}
<recovery_option> ::=
{
RECOVERY { FULL | BULK_LOGGED | SIMPLE }
| TORN_PAGE_DETECTION { ON | OFF }
| PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
}
<service_broker_option> ::=
{
ENABLE_BROKER
| DISABLE_BROKER
| NEW_BROKER
| ERROR_BROKER_CONVERSATIONS
| HONOR_BROKER_PRIORITY { ON | OFF}
}
<snapshot_option> ::=
{
ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
| READ_COMMITTED_SNAPSHOT {ON | OFF }
}
<sql_option> ::=
{
ANSI_NULL_DEFAULT { ON | OFF }
| ANSI_NULLS { ON | OFF }
| ANSI_PADDING { ON | OFF }
| ANSI_WARNINGS { ON | OFF }
| ARITHABORT { ON | OFF }
| COMPATIBILITY_LEVEL = { 80 | 90 | 100 }
| CONCAT_NULL_YIELDS_NULL { ON | OFF }
| NUMERIC_ROUNDABORT { ON | OFF }
| QUOTED_IDENTIFIER { ON | OFF }
| RECURSIVE_TRIGGERS { ON | OFF }
}
<termination>::=
{
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT
}
<auto_option>::=
Controls automatic options.
- database_name
Is the name of the database to be modified.
- AUTO_CLOSE { ON | OFF }
The AUTO_CLOSE option is useful for desktop databases because it allows for database files to be managed as regular files. They can be moved, copied to make backups, or even e-mailed to other users.
NoteIn earlier versions of SQL Server, AUTO_CLOSE is a synchronous process that can degrade performance when the database is accessed by an application that repeatedly makes and breaks connections to the Database Engine. Starting in SQL Server 2005, the AUTO_CLOSE process is asynchronous; repeatedly opening and closing the database no longer reduces performance.
The status of this option can be determined by examining the is_auto_close_on column in the sys.databases catalog view or the IsAutoClose property of the DATABASEPROPERTYEX function.
NoteWhen AUTO_CLOSE is ON, some columns in the sys.databases catalog view and DATABASEPROPERTYEX function will return NULL because the database is unavailable to retrieve the data. To resolve this, execute a USE statement to open the database.
NoteDatabase mirroring requires AUTO_CLOSE OFF.
When the database is set to AUTOCLOSE = ON, an operation that initiates an automatic database shutdown clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2, for each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval.
- AUTO_CREATE_STATISTICS { ON | OFF }
The status of this option can be determined by examining the is_auto_create_stats_on column in the sys.databases catalog view or the IsAutoCreateStatistics property of the DATABASEPROPERTYEX function.
For more information, see the section "Using the Database-Wide Statistics Options" in Using Statistics to Improve Query Performance.
- AUTO_SHRINK { ON | OFF }
The status of this option can be determined by examining the is_auto_shrink_on column in the sys.databases catalog view or the IsAutoShrink property of the DATABASEPROPERTYEX function.
- AUTO_UPDATE_STATISTICS { ON | OFF }
The status of this option can be determined by examining the is_auto_update_stats_on column in the sys.databases catalog view or the IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.
For more information, see the section "Using the Database-Wide Statistics Options" in Using Statistics to Improve Query Performance.
- AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
The status of this option can be determined by examining the is_auto_update_stats_async_on column in the sys.databases catalog view.
For more information that describes when to use synchronous or asynchronous statistics updates, see the section "Using the Database-Wide Statistics Options" in Using Statistics to Improve Query Performance.
<change_tracking_option>::=
Controls change tracking options. You can enable change tracking, set options, change options, and disable change tracking. For examples, see the Examples section later in this topic.
<cursor_option>::=
Controls cursor options.
<database_mirroring>
For the argument descriptions, see ALTER DATABASE Database Mirroring (Transact-SQL).
<date_correlation_optimization_option> ::=
Controls the date_correlation_optimization option.
<db_encryption_option>::=
Controls the database encryption state.
When encryption is enabled at the database level all filegroups will be encrypted. Any new filegroups will inherit the encrypted property. If any filegroups in the database are set to READ ONLY, the database encryption operation will fail.
You can see the encryption state of the database by using the sys.dm_database_encryption_keys dynamic management view.
<db_state_option>::=
Controls the state of the database.
The status of this option can be determined by examining the state and state_desc columns in the sys.databases catalog view or the Status property of the DATABASEPROPERTYEX function. For more information, see Database States.
A database marked as RESTORING cannot be set to OFFLINE, ONLINE, or EMERGENCY. A database may be in the RESTORING state during an active restore operation or when a restore operation of a database or log file fails because of a corrupted backup file. For more information, see Responding to SQL Server Restore Errors Caused by Damaged Backups.
<db_update_option>::=
Controls whether updates are allowed on the database.
To change this state, you must have exclusive access to the database. For more information, see the SINGLE_USER clause.
<db_user_access_option> ::=
Controls user access to the database.
The status of this option can be determined by examining the user_access column in the sys.databases catalog view or the UserAccess property of the DATABASEPROPERTYEX function.
<external_access_option>::=
Controls whether the database can be accessed by external resources, such as objects from another database.
- DB_CHAINING { ON | OFF }
- Important
The instance of SQL Server will recognize this setting when the cross db ownership chaining server option is 0 (OFF). When cross db ownership chaining is 1 (ON), all user databases can participate in cross-database ownership chains, regardless of the value of this option. This option is set by using sp_configure.
To set this option, requires CONTROL SERVER permission on the database. The DB_CHAINING option cannot be set on these system databases: master, model, and tempdb.
The status of this option can be determined by examining the is_db_chaining_on column in the sys.databases catalog view.
For more information, see Ownership Chains.
- TRUSTWORTHY { ON | OFF }
TRUSTWORTHY is set to OFF whenever the database is attached.
By default, all system databases except the msdb database have TRUSTWORTHY set to OFF. The value cannot be changed for the model and tempdb databases. We recommend that you never set the TRUSTWORTHY option to ON for the master database.
To set this option, requires CONTROL SERVER permission on the database.
The status of this option can be determined by examining the is_trustworthy_on column in the sys.databases catalog view.
<parameterization_option> ::=
Controls the parameterization option.
<recovery_option> ::=
Controls database recovery options and disk I/O error checking.
- FULL
Provides full recovery after media failure by using transaction log backups. If a data file is damaged, media recovery can restore all committed transactions. For more information, see Backup Under the Full Recovery Model.
- BULK_LOGGED
Provides recovery after media failure by combining the best performance and least amount of log-space use for certain large-scale or bulk operations. For information about what operations are bulk logged, see Operations That Can Be Minimally Logged. Under the BULK_LOGGED recovery model, logging for these operations is minimal. For more information, see Backup Under the Bulk-Logged Recovery Model.
- SIMPLE
A simple backup strategy that uses minimal log space is provided. Log space can be automatically reused when it is no longer required for server failure recovery. For more information, see Backup Under the Simple Recovery Model.
ImportantThe simple recovery model is easier to manage than the other two models but at the expense of greater data loss exposure if a data file is damaged. All changes since the most recent database or differential database backup are lost and must be manually reentered.
The default recovery model is determined by the recovery model of the model database. For more information about selecting the appropriate recovery model, see Choosing the Recovery Model for a Database.
The status of this option can be determined by examining the recovery_model and recovery_model_desc columns in the sys.databases catalog view or the Recovery property of the DATABASEPROPERTYEX function.
- TORN_PAGE_DETECTION { ON | OFF }
- Important
The syntax structure TORN_PAGE_DETECTION ON | OFF will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the syntax structure. Use the PAGE_VERIFY option instead.
- PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
Discovers damaged database pages caused by disk I/O path errors. Disk I/O path errors can be the cause of database corruption problems and are generally caused by power failures or disk hardware failures that occur at the time the page is being written to disk.
Consider the following important points when you use the PAGE_VERIFY option:
In SQL Server 2005 and SQL Server 2008, the default is CHECKSUM. In SQL Server 2000, TORN_PAGE_DETECTION is the default.
When a user or system database is upgraded to SQL Server 2005 or SQL Server 2008, the PAGE_VERIFY value (NONE or TORN_PAGE_DETECTION) is retained. We recommend that you use CHECKSUM.
NoteIn earlier versions of SQL Server, the PAGE_VERIFY database option is set to NONE for the tempdb database and cannot be modified. In SQL Server 2008, the default value for the tempdb database is CHECKSUM for new installations of SQL Server. When upgrading an installation SQL Server, the default value remains NONE. The option can be modified. We recommend that you use CHECKSUM for the tempdb database.
TORN_PAGE_DETECTION may use fewer resources but provides a minimal subset of the CHECKSUM protection.
PAGE_VERIFY can be set without taking the database offline, locking the database, or otherwise impeding concurrency on that database.
CHECKSUM is mutually exclusive to TORN_PAGE_DETECTION. Both options cannot be enabled at the same time.
When a torn page or checksum failure is detected, you can recover by restoring the data or potentially rebuilding the index if the failure is limited only to index pages. If you encounter a checksum failure, to determine the type of database page or pages affected, run DBCC CHECKDB. For more information about restore options, see RESTORE Arguments (Transact-SQL). Although restoring the data will resolve the data corruption problem, the root cause, for example, disk hardware failure, should be diagnosed and corrected as soon as possible to prevent continuing errors.
SQL Server will retry any read that fails with a checksum, torn page, or other I/O error four times. If the read is successful in any one of the retry attempts, a message will be written to the error log and the command that triggered the read will continue. If the retry attempts fail, the command will fail with error message 824.
For more information about checksum, torn page, read-retry, error messages 823 and 824, and other SQL Server I/O auditing features, see this Microsoft Web site.
The current setting of this option can be determined by examining the page_verify_option column in the sys.databases catalog view or the IsTornPageDetectionEnabled property of the DATABASEPROPERTYEX function.
<service_broker_option>::=
Controls the following Service Broker options: enables or disables message delivery, sets a new Service Broker identifier, or sets conversation priorities to ON or OFF. For more information about message deliver and Service Broker identifiers, see Managing Service Broker Identities. For more information about conversation priority levels, see Conversation Priorities. For examples that show how to use the HONOR_BROKER_PRIORITY option, see Managing Conversation Priorities.
- ENABLE_BROKER
Specifies that Service Broker is enabled for the specified database. Message delivery is started, and the is_broker_enabled flag is set to true in the sys.databases catalog view. The database retains the existing Service Broker identifier.
NoteENABLE_BROKER requires an exclusive database lock. If other sessions have locked resources in the database, ENABLE_BROKER will wait until the other sessions release their locks. To enable Service Broker in a user database, ensure that no other sessions are using the database before you run the ALTER DATABASE SET ENABLE_BROKER statement, such as by putting the database in single user mode. To enable Service Broker in the msdb database, first stop SQL Server Agent so that Service Broker can obtain the necessary lock.
- DISABLE_BROKER
Specifies that Service Broker is disabled for the specified database. Message delivery is stopped, and the is_broker_enabled flag is set to false in the sys.databases catalog view. The database retains the existing Service Broker identifier.
- NEW_BROKER
Specifies that the database should receive a new broker identifier. Because the database is considered to be a new service broker, all existing conversations in the database are immediately removed without producing end dialog messages. Any route that references the old Service Broker identifier must be re-created with the new identifier.
- ERROR_BROKER_CONVERSATIONS
Specifies that Service Broker message delivery is enabled. This preserves the existing Service Broker identifier for the database. Service Broker ends all conversations in the database with an error. This enables applications to perform regular cleanup for existing conversations.
- HONOR_BROKER_PRIORITY {ON | OFF}
Changes to the HONOR_BROKER_PRIORITY option take effect immediately for new dialogs or dialogs that have no messages waiting to be sent. Dialogs that have messages waiting to be sent when ALTER DATABASE is run will not pick up the new setting until some of the messages for the dialog have been sent. The amount of time before all dialogs start using the new setting can vary considerably.
The current setting of this property is reported in the is_broker_priority_honored column in the sys.databases catalog view.
<snapshot_option>::=
Determines the transaction isolation level.
<sql_option>::=
Controls the ANSI compliance options at the database level.
- ANSI_NULL_DEFAULT { ON | OFF }
Determines the default value, NULL or NOT NULL, of a column, alias data type, or CLR user-defined type for which the nullability is not explicitly defined in CREATE TABLE or ALTER TABLE statements. Columns that are defined with constraints follow constraint rules regardless of this setting.
Connection-level settings that are set by using the SET statement override the default database-level setting for ANSI_NULL_DEFAULT. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULL_DEFAULT to ON for the session when connecting to an instance of SQL Server. For more information, see SET ANSI_NULL_DFLT_ON (Transact-SQL).
For ANSI compatibility, setting the database option ANSI_NULL_DEFAULT to ON changes the database default to NULL.
The status of this option can be determined by examining the is_ansi_null_default_on column in the sys.databases catalog view or the IsAnsiNullDefault property of the DATABASEPROPERTYEX function.
- ANSI_NULLS { ON | OFF }
- Important
In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Connection-level settings that are set by using the SET statement override the default database setting for ANSI_NULLS. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULLS to ON for the session when connecting to an instance of SQL Server. For more information, see SET ANSI_NULLS (Transact-SQL).
SET ANSI_NULLS also must be set to ON when you create or make changes to indexes on computed columns or indexed views.
The status of this option can be determined by examining the is_ansi_nulls_on column in the sys.databases catalog view or the IsAnsiNullsEnabled property of the DATABASEPROPERTYEX function.
- ANSI_PADDING { ON | OFF }
When OFF is specified, this setting affects only the definition of new columns.
ImportantIn a future version of SQL Server, ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
char(n) and binary(n) columns that allow for nulls are padded to the length of the column when ANSI_PADDING is set to ON, but trailing blanks and zeros are trimmed when ANSI_PADDING is OFF. char(n) and binary(n) columns that do not allow nulls are always padded to the length of the column.
Connection-level settings that are set by using the SET statement override the default database-level setting for ANSI_PADDING. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_PADDING to ON for the session when connecting to an instance of SQL Server. For more information, see SET ANSI_PADDING (Transact-SQL).
ImportantWe recommend that ANSI_PADDING always be set to ON. ANSI_PADDING must be ON when you create or manipulate indexes on computed columns or indexed views.
The status of this option can be determined by examining the is_ansi_padding_on column in the sys.databases catalog view or the IsAnsiPaddingEnabled property of the DATABASEPROPERTYEX function.
- ANSI_WARNINGS { ON | OFF }
SET ANSI_WARNINGS must be set to ON when you create or make changes to indexes on computed columns or indexed views.
Connection-level settings that are set by using the SET statement override the default database setting for ANSI_WARNINGS. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_WARNINGS to ON for the session when connecting to an instance of SQL Server. For more information, see SET ANSI_WARNINGS (Transact-SQL).
The status of this option can be determined by examining the is_ansi_warnings_on column in the sys.databases catalog view or the IsAnsiWarningsEnabled property of the DATABASEPROPERTYEX function.
- ARITHABORT { ON | OFF }
SET ARITHABORT must be set to ON when you create or make changes to indexes on computed columns or indexed views.
The status of this option can be determined by examining the is_arithabort_on column in the sys.databases catalog view or the IsArithmeticAbortEnabled property of the DATABASEPROPERTYEX function.
- COMPATIBILITY_LEVEL { 80 | 90 | 100 }
For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).
- CONCAT_NULL_YIELDS_NULL { ON | OFF }
CONCAT_NULL_YIELDS_NULL must be set to ON when you create or make changes to indexes on computed columns or indexed views.
ImportantIn a future version of SQL Server, CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Connection-level settings that are set by using the SET statement override the default database setting for CONCAT_NULL_YIELDS_NULL. By default, ODBC and OLE DB clients issue a connection-level SET statement setting CONCAT_NULL_YIELDS_NULL to ON for the session when connecting to an instance of SQL Server. For more information, see SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).
The status of this option can be determined by examining the is_concat_null_yields_null_on column in the sys.databases catalog view or the IsNullConcat property of the DATABASEPROPERTYEX function.
- QUOTED_IDENTIFIER { ON | OFF }
SQL Server also allows for 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 (Database Engine).
When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the metadata of the table, even if the option is set to OFF when the table is created.
Connection-level settings that are set by using the SET statement override the default database setting for QUOTED_IDENTIFIER. By default, ODBC and OLE DB clients issue a connection-level SET statement setting QUOTED_IDENTIFIER to ON when connecting to an instance of SQL Server. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).
The status of this option can be determined by examining the is_quoted_identifier_on column in the sys.databases catalog view or the IsQuotedIdentifiersEnabled property of the DATABASEPROPERTYEX function.
- NUMERIC_ROUNDABORT { ON | OFF }
NUMERIC_ROUNDABORT must be set to OFF when you create or make changes to indexes on computed columns or indexed views.
The status of this option can be determined by examining the is_numeric_roundabort_on column in the sys.databases catalog view or the IsNumericRoundAbortEnabled property of the DATABASEPROPERTYEX function.
- RECURSIVE_TRIGGERS { ON | OFF }
- Note
Only direct recursion is prevented when RECURSIVE_TRIGGERS is set to OFF. To disable indirect recursion, you must also set the nested triggers server option to 0.
The status of this option can be determined by examining the is_recursive_triggers_on column in the sys.databases catalog view or the IsRecursiveTriggersEnabled property of the DATABASEPROPERTYEX function.
WITH <termination>::=
Specifies when to roll back incomplete transactions when the database is transitioned from one state to another. If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely if there is any lock on the database. Only one termination clause can be specified, and it follows the SET clauses.
| Note |
|---|
Not all database options use the WITH <termination> clause. For more information, see the table under "Setting Options" in the Remarks section. |
Setting Options
To retrieve current settings for database options, use the sys.databases catalog view or DATABASEPROPERTYEX. For a list of default values assigned to the database when it is first created, see Setting Database Options.
After you set a database option, the modification takes effect immediately.
To change the default values for any one of the database options for all newly created databases, change the appropriate database option in the model database.
Not all database options use the WITH <termination> clause or can be specified in combination with other options. The following table lists these options and their option and termination status.
Options category | Can be specified with other options | Can use the WITH <termination> clause |
|---|---|---|
<db_state_option> | Yes | Yes |
<db_user_access_option> | Yes | Yes |
<db_update_option> | Yes | Yes |
<external_access_option> | Yes | No |
<cursor_option> | Yes | No |
<auto_option> | Yes | No |
<sql_option> | Yes | No |
<recovery_option> | Yes | No |
<database_mirroring_option> | No | No |
ALLOW_SNAPSHOT_ISOLATION | No | No |
READ_COMMITTED_SNAPSHOT | No | Yes |
<service_broker_option> | Yes | No |
DATE_CORRELATION_OPTIMIZATION | Yes | Yes |
<parameterization_option> | Yes | Yes |
<change_tracking_option> | Yes | Yes |
<db_encryption> | Yes | No |
The plan cache for the instance of SQL Server is cleared by setting one of the following options:
OFFLINE | READ_WRITE |
ONLINE | MODIFY FILEGROUP DEFAULT |
MODIFY_NAME | MODIFY FILEGROUP READ_WRITE |
COLLATE | MODIFY FILEGROUP READ_ONLY |
READ_ONLY |
|
Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval.
A. Setting options on a database
The following example sets the recovery model and data page verification options for the AdventureWorks sample database.
USE master; GO ALTER DATABASE AdventureWorks SET RECOVERY FULL, PAGE_VERIFY CHECKSUM; GO
B. Setting the database to READ_ONLY
Changing the state of a database or filegroup to READ_ONLY or READ_WRITE requires exclusive access to the database. The following example sets the database to SINGLE_USER mode to obtain exclusive access. The example then sets the state of the AdventureWorks database to READ_ONLY and returns access to the database to all users.
| Note |
|---|
This example uses the termination option WITH ROLLBACK IMMEDIATE in the first ALTER DATABASE statement. All incomplete transactions will be rolled back and any other connections to the AdventureWorks sample database will be immediately disconnected. |
USE master; GO ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO ALTER DATABASE AdventureWorks SET READ_ONLY; GO ALTER DATABASE AdventureWorks SET MULTI_USER; GO
C. Enabling snapshot isolation on a database
The following example enables the snapshot isolation framework option for the AdventureWorks database.
USE AdventureWorks;
GO
-- Check the state of the snapshot_isolation_framework
-- in the database.
SELECT name, snapshot_isolation_state,
snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks';
GO
USE master;
GO
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Check again.
SELECT name, snapshot_isolation_state,
snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks';
GO
The result set shows that the snapshot isolation framework is enabled.
name snapshot_isolation_state description --------------- ------------------------ ----------- AdventureWorks 1 ON
D. Enabling, modifying, and disabling change tracking
The following example enables change tracking for the AdventureWorks database and sets the retention period to 4 days.
ALTER DATABASE AdventureWorks SET CHANGE_TRACKING = ON (AUTO_CLEANUP = ON, CHANGE_RETENTION = 2 DAYS);
The following example shows how to change the retention period to 3 days.
ALTER DATABASE AdventureWorks SET CHANGE_TRACKING (CHANGE_RETENTION = 3 DAYS);
The following example shows how to disable change tracking for the AdventureWorks database.
ALTER DATABASE AdventureWorks SET CHANGE_TRACKING = OFF;
