database_name | CURRENT

Is the name of the database to be modified. CURRENT performs the action in the current database. CURRENT is not supported for all options in all contexts. If CURRENT fails, provide the database name.

<auto_option> ::=

Controls automatic options.

AUTO_CLOSE { ON | OFF }

ON

The database is shut down cleanly and its resources are freed after the last user exits.

The database automatically reopens when a user tries to use the database again. For example, by issuing a USE database_name statement. If the database is shut down cleanly while AUTO_CLOSE is set to ON, the database is not reopened until a user tries to use the database the next time the Database Engine is restarted.

OFF

The database remains open after the last user exits.

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. The AUTO_CLOSE process is asynchronous; repeatedly opening and closing the database does not reduce performance.

Note
The AUTO_CLOSE option is not available in a Contained Database.

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.

Note
When 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.

Note
Database 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 and higher, 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 }

ON

The query optimizer creates statistics on single columns in query predicates, as necessary, to improve query plans and query performance. These single-column statistics are created when the query optimizer compiles queries. The single-column statistics are created only on columns that are not already the first column of an existing statistics object.

The default is ON. We recommend that you use the default setting for most databases.

OFF

The query optimizer does not create statistics on single columns in query predicates when it is compiling queries. Setting this option to OFF can cause suboptimal query plans and degraded query performance.

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

AUTO_SHRINK { ON | OFF }

ON

The database files are candidates for periodic shrinking.

Both data file and log files can be automatically shrunk. AUTO_SHRINK reduces the size of the transaction log only if the database is set to SIMPLE recovery model or if the log is backed up. When set to OFF, the database files are not automatically shrunk during periodic checks for unused space.

The AUTO_SHRINK 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 larger.

You cannot shrink a read-only database.

OFF

The database files are not automatically shrunk during periodic checks for unused space.

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.

Note
The AUTO_SHRINK option is not available in a Contained Database.

AUTO_UPDATE_STATISTICS { ON | OFF }

ON

Specifies that the query optimizer updates statistics when they are used by a query and when they might be out-of-date. Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. The query optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view.

The query optimizer checks for out-of-date statistics before compiling a query and before executing a cached query plan. Before compiling a query, the query optimizer uses the columns, tables, and indexed views in the query predicate to determine which statistics might be out-of-date. Before executing a cached query plan, the Database Engine verifies that the query plan references up-to-date statistics.

The AUTO_UPDATE_STATISTICS option applies to statistics created for indexes, single-columns in query predicates, and statistics that are created by using the CREATE STATISTICS statement. This option also applies to filtered statistics.

The default is ON. We recommend that you use the default setting for most databases.

Use the AUTO_UPDATE_STATISTICS_ASYNC option to specify whether the statistics are updated synchronously or asynchronously.

OFF

Specifies that the query optimizer does not update statistics when they are used by a query and when they might be out-of-date. Setting this option to OFF can cause suboptimal query plans and degraded query performance.

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

AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

ON

Specifies that statistics updates for the AUTO_UPDATE_STATISTICS option are asynchronous. The query optimizer does not wait for statistics updates to complete before it compiles queries.

Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is set to ON.

By default, the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF, and the query optimizer updates statistics synchronously.

OFF

Specifies that statistics updates for the AUTO_UPDATE_STATISTICS option are synchronous. The query optimizer waits for statistcs updates to complete before it compiles queries.

Setting this option to OFF has no effect unless AUTO_UPDATE_STATISTICS is set to ON.

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

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

ON

Enables change tracking for the database. When you enable change tracking, you can also set the AUTO CLEANUP and CHANGE RETENTION options.

AUTO_CLEANUP = { ON | OFF }

ON

Change tracking information is automatically removed after the specified retention period.

OFF

Change tracking data is not removed from the database.

CHANGE_RETENTION =retention_period { DAYS | HOURS | MINUTES }

Specifies the minimum period for keeping change tracking information in the database. Data is removed only when the AUTO_CLEANUP value is ON.

retention_period is an integer that specifies the numerical component of the retention period.

The default retention period is 2 days. The minimum retention period is 1 minute.

OFF

Disables change tracking for the database. You must disable change tracking on all tables before you can disable change tracking off the database.

<containment_option> ::=

Controls database containment options.

CONTAINMENT = { NONE | PARTIAL}

NONE

The database is not a contained database.

PARTIAL

The database is a contained database. Setting database containment to partial will fail if the database has replication, change data capture, or change tracking enabled. Error checking stops after one failure. For more information about contained databases, see Contained Databases.

<cursor_option> ::=

Controls cursor options.

CURSOR_CLOSE_ON_COMMIT { ON | OFF }

ON

Any cursors open when a transaction is committed or rolled back are closed.

OFF

Cursors remain open when a transaction is committed; rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.

Connection-level settings that are set by 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 an instance of SQL Server. For more information, see SET CURSOR_CLOSE_ON_COMMIT (Transact-SQL).

The status of this option can be determined by examining the is_cursor_close_on_commit_on column in the sys.databases catalog view or the IsCloseCursorsOnCommitEnabled property of the DATABASEPROPERTYEX function.

CURSOR_DEFAULT { LOCAL | GLOBAL }

Controls whether cursor scope uses LOCAL or GLOBAL.

LOCAL

When LOCAL is specified and a cursor is not defined 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 ends, unless it was passed back in an OUTPUT parameter. If the cursor is passed back in an OUTPUT parameter, the cursor is deallocated when the last variable that references it is deallocated or goes out of scope.

GLOBAL

When GLOBAL is specified, 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. For more information, see DECLARE CURSOR (Transact-SQL).

The status of this option can be determined by examining the is_local_cursor_default column in the sys.databases catalog view or the IsLocalCursorsDefault property of the DATABASEPROPERTYEX function.

<database_mirroring>

For the argument descriptions, see ALTER DATABASE Database Mirroring (Transact-SQL).

<date_correlation_optimization_option> ::=

Controls the date_correlation_optimization option.

DATE_CORRELATION_OPTIMIZATION { ON | OFF }

ON

SQL Server maintains correlation statistics between any two tables in the database that are linked by a FOREIGN KEY constraint and have datetime columns.

OFF

Correlation statistics are not maintained.

To set DATE_CORRELATION_OPTIMIZATION to ON, there must be no active connections to the database except for the connection that is executing the ALTER DATABASE statement. Afterwards, multiple connections are supported.

The current setting of this option can be determined by examining the is_date_correlation_on column in the sys.databases catalog view.

<db_encryption_option> ::=

Controls the database encryption state.

ENCRYPTION {ON | OFF}

Sets the database to be encrypted (ON) or not encrypted (OFF). For more information about database encryption, see Transparent Data Encryption (TDE).

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.

OFFLINE

The database is closed, shut down cleanly, and marked offline. The database cannot be modified while it is offline.

ONLINE

The database is open and available for use.

EMERGENCY

The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect due to a corrupted log file can be set to the EMERGENCY state. This could enable the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.

Note
Permissions: ALTER DATABASE permission for the subject database is required to change a database to the offline or emergency state. The server level ALTER ANY DATABASE permission is required to move a database from offline to online.

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.

<db_update_option> ::=

Controls whether updates are allowed on the database.

READ_ONLY

Users can read data from the database but not modify it.

Note
To improve query performance, update statistics before setting a database to READ_ONLY. If additional statistics are needed after a database is set to READ_ONLY, the Database Engine will create statistics in tempdb. For more information about statistics for a read-only database, see Statistics.

READ_WRITE

The database is available for read and write operations.

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.

SINGLE_USER

Specifies that only one user at a time can access the database. If SINGLE_USER is specified and there are other users connected to the database the ALTER DATABASE statement will be blocked until all users disconnect from the specified database. To override this behavior, see the WITH <termination> clause.

The database remains in SINGLE_USER mode even if the user that set the option logs off. At that point, a different user, but only one, can connect to the database.

Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. To view the status of this option, query the is_auto_update_stats_async_on column in the sys.databases catalog view. If the option is set to ON, perform the following tasks:

1. Set AUTO_UPDATE_STATISTICS_ASYNC to OFF.

2. Check for active asynchronous statistics jobs by querying the sys.dm_exec_background_job_queue dynamic management view.

If there are active jobs, either allow the jobs to complete or manually terminate them by using KILL STATS JOB.

RESTRICTED_USER

RESTRICTED_USER allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but does not limit their number. All connections to the database are disconnected in the timeframe specified by the termination clause of the ALTER DATABASE statement. After the database has transitioned to the RESTRICTED_USER state, connection attempts by unqualified users are refused.

MULTI_USER

All users that have the appropriate permissions to connect to the database are allowed.

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 }

ON

Database can be the source or target of a cross-database ownership chain.

OFF

Database cannot participate in cross-database ownership chaining.

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.

TRUSTWORTHY { ON | OFF }

ON

Database modules (for example, user-defined functions or stored procedures) that use an impersonation context can access resources outside the database.

OFF

Database modules in an impersonation context cannot access resources outside the database.

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.

DEFAULT_FULLTEXT_LANGUAGE

Specifies the default language value for full-text indexed columns.

Important
This option is allowable only when CONTAINMENT has been set to PARTIAL. If CONTAINMENT is set to NONE, errors will occur.

DEFAULT_LANGUAGE

Specifies the default language for all newly created logins. Language can be specified by providing the local id (lcid), the language name, or the language alias. For a list of acceptable language names and aliases, see sys.syslanguages (Transact-SQL).

Important
This option is allowable only when CONTAINMENT has been set to PARTIAL. If CONTAINMENT is set to NONE, errors will occur.

NESTED_TRIGGERS

Specifies whether an AFTER trigger can cascade; that is, perform an action that initiates another trigger, which initiates another trigger, and so on.

Important
This option is allowable only when CONTAINMENT has been set to PARTIAL. If CONTAINMENT is set to NONE, errors will occur.

TRANSFORM_NOISE_WORDS

Used to suppress an error message if noise words, or stopwords, cause a Boolean operation on a full-text query to fail.

Important
This option is allowable only when CONTAINMENT has been set to PARTIAL. If CONTAINMENT is set to NONE, errors will occur.

TWO_DIGIT_YEAR_CUTOFF

Specifies an integer from 1753 to 9999 that represents the cutoff year for interpreting two-digit years as four-digit years.

Important
This option is allowable only when CONTAINMENT has been set to PARTIAL. If CONTAINMENT is set to NONE, errors will occur.

<FILESTREAM_option> ::=

Controls the settings for FileTables.

NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }

OFF

Non-transactional access to FileTable data is disabled.

READ_ONLY

FILESTREAM data in FileTables in this database can be read by non-transactional processes.

FULL

Full non-transactional access to FILESTREAM data in FileTables is enabled.

DIRECTORY_NAME = <directory_name>

A windows-compatible directory name. This name should be unique among all the database-level directory names in the SQL Server instance. Uniqueness comparison is case-insensitive, regardless of collation settings. This option must be set before creating a FileTable in this database.

<parameterization_option> ::=

Controls the parameterization option.

PARAMETERIZATION { SIMPLE | FORCED }

SIMPLE

Queries are parameterized based on the default behavior of the database.

FORCED

SQL Server parameterizes all queries in the database.

The current setting of this option can be determined by examining the is_parameterization_forced column in the sys.databases catalog view.

<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 Recovery Models (SQL Server).

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 can be minimally logged, see The Transaction Log (SQL Server). Under the BULK_LOGGED recovery model, logging for these operations is minimal. For more information, see Recovery Models (SQL Server).

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 Recovery Models (SQL Server).

Important
The 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 Recovery Models (SQL Server).

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 }

ON

Incomplete pages can be detected by the Database Engine.

OFF

Incomplete pages cannot be detected by the Database Engine.

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.

CHECKSUM

Calculates a checksum over the contents of the whole page and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header. If the values do not match, error message 824 (indicating a checksum failure) is reported to both the SQL Server error log and the Windows event log. A checksum failure indicates an I/O path problem. To determine the root cause requires investigation of the hardware, firmware drivers, BIOS, filter drivers (such as virus software), and other I/O path components.

TORN_PAGE_DETECTION

Saves a specific 2-bit pattern for each 512-byte sector in the 8-kilobyte (KB) database page and stored in the database page header when the page is written to disk. When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information. Unmatched values indicate that only part of the page was written to disk. In this situation, error message 824 (indicating a torn page error) is reported to both the SQL Server error log and the Windows event log. Torn pages are typically detected by database recovery if it is truly an incomplete write of a page. However, other I/O path failures can cause a torn page at any time.

NONE

Database page writes will not generate a CHECKSUM or TORN_PAGE_DETECTION value. SQL Server will not verify a checksum or torn page during a read even if a CHECKSUM or TORN_PAGE_DETECTION value is present in the page header.

Consider the following important points when you use the PAGE_VERIFY option:

• The default is CHECKSUM.

• When a user or system database is upgraded to SQL Server 2005 or a later version, the PAGE_VERIFY value (NONE or TORN_PAGE_DETECTION) is retained. We recommend that you use CHECKSUM.

  Note

  In 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 and later versions, 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.

<target_recovery_time_option> ::=

Specifies the frequency of indirect checkpoints on a per-database basis. The default is 0, which indicates that the database will use automatic checkpoints, whose frequency depends on the recovery interval setting of the server instance.

TARGET_RECOVERY_TIME =target_recovery_time { SECONDS | MINUTES }

target_recovery_time

Specifies the maximum bound on the time to recover the specified database in the event of a crash.

SECONDS

Indicates that target_recovery_time is expressed as the number of seconds.

MINUTES

Indicates that target_recovery_time is expressed as the number of minutes.

For more information about indirect checkpoints, see Database Checkpoints (SQL Server).

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

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.

Note

ENABLE_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}

ON

Send operations take into consideration the priority levels that are assigned to conversations. Messages from conversations that have high priority levels are sent before messages from conversations that are assigned low priority levels.

OFF

Send operations run as if all conversations have the default priority level.

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.

ALLOW_SNAPSHOT_ISOLATION { ON | OFF }

ON

Enables Snapshot option at the database level. When it is enabled, DML statements start generating row versions even when no transaction uses Snapshot Isolation. Once this option is enabled, transactions can specify the SNAPSHOT transaction isolation level. When a transaction runs at the SNAPSHOT isolation level, all statements see a snapshot of data as it exists at the start of the transaction. If a transaction running at the SNAPSHOT isolation level accesses data in multiple databases, either ALLOW_SNAPSHOT_ISOLATION must be set to ON in all the databases, or each statement in the transaction must use locking hints on any reference in a FROM clause to a table in a database where ALLOW_SNAPSHOT_ISOLATION is OFF.

OFF

Turns off the Snapshot option at the database level. Transactions cannot specify the SNAPSHOT transaction isolation level.

When you set ALLOW_SNAPSHOT_ISOLATION to a new state (from ON to OFF, or from OFF to ON), ALTER DATABASE does not return control to the caller until all existing transactions in the database are committed. If the database is already in the state specified in the ALTER DATABASE statement, control is returned to the caller immediately. If the ALTER DATABASE statement does not return quickly, use sys.dm_tran_active_snapshot_database_transactions to determine whether there are long-running transactions. If the ALTER DATABASE statement is canceled, the database remains in the state it was in when ALTER DATABASE was started. The sys.databases catalog view indicates the state of snapshot-isolation transactions in the database. If snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF will pause six seconds and retry the operation.

You cannot change the state of ALLOW_SNAPSHOT_ISOLATION if the database is OFFLINE.

If you set ALLOW_SNAPSHOT_ISOLATION in a READ_ONLY database, the setting will be retained if the database is later set to READ_WRITE.

You can change the ALLOW_SNAPSHOT_ISOLATION settings for the master, model, msdb, and tempdb databases. If you change the setting for tempdb, the setting is retained every time the instance of the Database Engine is stopped and restarted. If you change the setting for model, that setting becomes the default for any new databases that are created, except for tempdb.

The option is ON, by default, for the master and msdb databases.

The current setting of this option can be determined by examining the snapshot_isolation_state column in the sys.databases catalog view.

READ_COMMITTED_SNAPSHOT { ON | OFF }

ON

Enables Read-Committed Snapshot option at the database level. When it is enabled, DML statements start generating row versions even when no transaction uses Snapshot Isolation. Once this option is enabled, the transactions specifying the read committed isolation level use row versioning instead of locking. When a transaction runs at the read committed isolation level, all statements see a snapshot of data as it exists at the start of the statement.

OFF

Turns off Read-Committed Snapshot option at the database level. Transactions specifying the READ COMMITTED isolation level use locking.

To set READ_COMMITTED_SNAPSHOT ON or OFF, there must be no active connections to the database except for the connection executing the ALTER DATABASE command. However, the database does not have to be in single-user mode. You cannot change the state of this option when the database is OFFLINE.

If you set READ_COMMITTED_SNAPSHOT in a READ_ONLY database, the setting will be retained when the database is later set to READ_WRITE.

READ_COMMITTED_SNAPSHOT cannot be turned ON for the master, tempdb, or msdb system databases. If you change the setting for model, that setting becomes the default for any new databases created, except for tempdb.

The current setting of this option can be determined by examining the is_read_committed_snapshot_on column in the sys.databases catalog view.

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

ON

The default value is NULL.

OFF

The default value is NOT NULL.

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 }

ON

All comparisons to a null value evaluate to UNKNOWN.

OFF

Comparisons of non-UNICODE values to a null value evaluate to TRUE if both values are NULL.

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 }

ON

Strings are padded to the same length before conversion or inserting to a varchar or nvarchar data type.

Trailing blanks in character values inserted into varchar or nvarchar columns and trailing zeros in binary values inserted into varbinary columns are not trimmed. Values are not padded to the length of the column.

OFF

Trailing blanks for varchar or nvarchar and zeros for varbinary are trimmed.

When OFF is specified, this setting affects only the definition of new columns.

Important

In 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. We recommend that you always set ANSI_PADDING to ON. ANSI_PADDING must be ON when you create or manipulate indexes on computed columns or indexed views.

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

Important

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 }

ON

Errors or warnings are issued when conditions such as divide-by-zero occur or null values appear in aggregate functions.

OFF

No warnings are raised and null values are returned when conditions such as divide-by-zero occur.

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 }

ON

A query is ended when an overflow or divide-by-zero error occurs during query execution.

OFF

A warning message is displayed when one of these errors occurs, but the query, batch, or transaction continues to process as if no error occurred.

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 { 90 | 100 | 110 }

For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).

CONCAT_NULL_YIELDS_NULL { ON | OFF }

ON

The result of a concatenation operation is NULL when either operand is NULL. For example, concatenating the character string "This is" and NULL causes the value NULL, instead of the value "This is".

OFF

The null value is treated as an empty character string.

CONCAT_NULL_YIELDS_NULL must be set to ON when you create or make changes to indexes on computed columns or indexed views.

Important
In 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 }

ON

Double quotation marks can be used to enclose delimited identifiers.

All strings 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. If a single quotation mark (') is part of the literal string, it can be represented by double quotation marks (").

OFF

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.

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 Database Identifiers.

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 }

ON

An error is generated when loss of precision occurs in an expression.

OFF

Losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result.

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 }

ON

Recursive firing of AFTER triggers is allowed.

OFF

Only direct recursive firing of AFTER triggers is not allowed. To also disable indirect recursion of AFTER triggers, set the nested triggers server option to 0 by using sp_configure.

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.

ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE

Specifies whether to roll back after the specified number of seconds or immediately.

NO_WAIT

Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.

To retrieve current settings for database options, use the sys.databases catalog view or DATABASEPROPERTYEX

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.

The plan cache for the instance of SQL Server is cleared by setting one of the following options:

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.

The following example sets the recovery model and data page verification options for the AdventureWorks2012 sample database.

USE master;
GO
ALTER DATABASE AdventureWorks2012 
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO

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 AdventureWorks2012 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 AdventureWorks2012 database will be immediately disconnected.

USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO

The following example enables the snapshot isolation framework option for the AdventureWorks2012 database.

USE AdventureWorks2012;
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'AdventureWorks2012';
GO
USE master;
GO
ALTER DATABASE AdventureWorks2012
    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'AdventureWorks2012';
GO

The result set shows that the snapshot isolation framework is enabled.

name                 snapshot_isolation_state  description

-------------------- ------------------------  ----------

AdventureWorks2012 1                         ON

The following example enables change tracking for the AdventureWorks2012 database and sets the retention period to 4 days.

ALTER DATABASE AdventureWorks2012
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 AdventureWorks2012
SET CHANGE_TRACKING (CHANGE_RETENTION = 3 DAYS);

The following example shows how to disable change tracking for the AdventureWorks2012 database.

ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING = OFF;

database_name | CURRENT

Es el nombre de la base de datos que se va a modificar. CURRENT realiza la acción en la base de datos actual. CURRENT no se admite en todas las opciones en todos los contextos. Si CURRENT produce un error, proporcione el nombre de la base de datos.

<auto_option> ::=

Controla las opciones automáticas.

AUTO_CLOSE { ON | OFF }

ON

La base de datos se cierra correctamente y se liberan sus recursos después de que salga el último usuario.

La base de datos se vuelve a abrir automáticamente cuando un usuario intenta utilizarla de nuevo. Por ejemplo, al generar una instrucción USE database_name. Si la base de datos se cierra correctamente mientras AUTO_CLOSE está establecido en ON, la base de datos no se volverá a abrir hasta que un usuario intente utilizar la base de datos la próxima vez que se reinicie el Motor de base de datos.

OFF

La base de datos permanece abierta después de que haya salido el último usuario.

La opción AUTO_CLOSE es útil para las bases de datos de escritorio porque permite administrar los archivos de la base de datos como archivos normales. Se pueden mover, copiar para realizar copias de seguridad e incluso enviar por correo electrónico a otros usuarios. El proceso AUTO_CLOSE es asincrónico. La apertura y cierre repetidos de la base de datos no reduce el rendimiento.

Nota
La opción AUTO_CLOSE no está disponible en una base de datos independiente.

El estado de esta opción se puede determinar mediante el examen de la columna is_auto_close_on en la vista de catálogo sys.databases o la propiedad IsAutoClose de la función DATABASEPROPERTYEX.

Nota
Si AUTO_CLOSE es ON, algunas columnas de la vista de catálogo sys.databases y de la función DATABASEPROPERTYEX devuelven NULL porque la base de datos no está disponible para recuperar los datos. Para resolver este problema, ejecute la instrucción USE para abrir la base de datos.

Nota
La creación de reflejo de la base de datos requiere AUTO_CLOSE OFF.

Cuando la base de datos se establece en AUTOCLOSE = ON, una operación que inicia el cierre automático de la base de datos borra la memoria caché del plan para la instancia de SQL Server. Al borrar la memoria caché del plan, se provoca una nueva compilación de todos los planes de ejecución posteriores y puede ocasionar una disminución repentina y temporal del rendimiento de las consultas. En SQL Server 2005 Service Pack 2 y posterior, para cada almacén de caché borrado de la memoria caché del plan, el registro de errores de SQL Server contendrá el siguiente mensaje informativo: "SQL Server ha detectado %d instancias de vaciado del almacén de caché '%s' (parte de la memoria caché del plan) debido a determinadas operaciones de mantenimiento de base de datos o reconfiguración". Este mensaje se registra cada cinco minutos siempre que se vacíe la memoria caché dentro de ese intervalo de tiempo.

AUTO_CREATE_STATISTICS { ON | OFF }

ON

El optimizador de consultas crea las estadísticas en columnas únicas de los predicados de consulta, según sea necesario, para mejorar los planes de consulta y el rendimiento de las consultas. Estas estadísticas de columna única se crean cuando el optimizador de consultas las compila. Las estadísticas de columna única solamente se crean en las columnas que ya no son la primera columna de un objeto de estadísticas existente.

El valor predeterminado es ON. Recomendamos utilizar la configuración predeterminada para la mayoría de las bases de datos.

OFF

El optimizador de consultas no crea las estadísticas en columnas únicas de los predicados de consulta cuando está compilando consultas. Establecer esta opción en OFF puede producir planes de consulta poco óptimos y un rendimiento degradado de las consultas.

El estado de esta opción se puede determinar mediante el examen de la columna is_auto_create_stats_on en la vista de catálogo sys.databases o la propiedad IsAutoCreateStatistics de la función DATABASEPROPERTYEX.

Para obtener más información, vea la sección "Utilizar las opciones de estadísticas de toda la base de datos" en Estadísticas.

AUTO_SHRINK { ON | OFF }

ON

Los archivos de la base de datos se pueden reducir periódicamente.

Pueden reducirse automáticamente los archivos de datos y los archivos de registro. AUTO_SHRINK reduce el tamaño del registro de transacciones solo si el modelo de recuperación de la base de datos se establece en SIMPLE o si se realiza una copia de seguridad del registro. Cuando el valor es OFF, los archivos de la base de datos no se reducen de forma automática durante las comprobaciones periódicas del espacio no utilizado.

La opción AUTO_SHRINK reduce los archivos cuando no se utiliza más de un 25% del espacio del archivo. El tamaño del archivo se reduce hasta un tamaño en el que el 25% del archivo corresponde al espacio sin utilizar o hasta el tamaño que tenía el archivo cuando se creó (el tamaño mayor de los dos).

No puede reducir una base de datos de solo lectura.

OFF

Los archivos de la base de datos no se reducen automáticamente durante las comprobaciones periódicas del espacio no utilizado.

El estado de esta opción se puede determinar mediante el examen de la columna is_auto_shrink_on en la vista de catálogo sys.databases o la propiedad IsAutoShrink de la función DATABASEPROPERTYEX.

Nota
La opción AUTO_SHRINK no está disponible en una base de datos independiente.

AUTO_UPDATE_STATISTICS { ON | OFF }

ON

Especifica que el optimizador de consultas actualiza las estadísticas cuando son usadas por una consulta y pudieran estar obsoletas. Las estadísticas se vuelven obsoletas después de que operaciones de inserción, actualización, eliminación o combinación cambien la distribución de los datos en la tabla o la vista indizada. El optimizador de consultas determina cuándo han podido quedar obsoletas las estadísticas contando el número de modificaciones de datos desde la actualización más reciente de las estadísticas, comparando el número de modificaciones con respecto a un umbral. El umbral se basa en el número de filas de la tabla o la vista indizada.

El optimizador de consultas comprueba que hay estadísticas obsoletas antes de compilar una consulta y antes de ejecutar un plan de consulta almacenado en la memoria caché. Antes de compilar una consulta, el optimizador de consultas utiliza las columnas, tablas y vistas indizadas en el predicado de consulta, para determinar qué estadísticas podrían estar obsoletas. Antes de ejecutar un plan de consulta almacenado en la memoria caché, Motor de base de datos comprueba que el plan de consulta hace referencia a las estadísticas actualizadas.

La opción AUTO_UPDATE_STATISTICS se aplica a las estadísticas creadas para índices y columnas únicas de los predicados de consulta, así como a las estadísticas creadas con la instrucción CREATE STATISTICS. Esta opción también se aplica a las estadísticas filtradas.

El valor predeterminado es ON. Recomendamos utilizar la configuración predeterminada para la mayoría de las bases de datos.

Utilice la opción AUTO_UPDATE_STATISTICS_ASYNC para especificar si las estadísticas se actualizan sincrónica o asincrónicamente.

OFF

Especifica que el optimizador de consultas no actualiza las estadísticas cuando son usadas por una consulta y parece que puedan estar obsoletas. Establecer esta opción en OFF puede producir planes de consulta poco óptimos y un rendimiento degradado de las consultas.

El estado de esta opción se puede determinar mediante el examen de la columna is_auto_update_stats_on en la vista de catálogo sys.databases o la propiedad IsAutoUpdateStatistics de la función DATABASEPROPERTYEX.

Para obtener más información, vea la sección "Utilizar las opciones de estadísticas de toda la base de datos" en Estadísticas.

AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

ON

Especifica que las actualizaciones de las estadísticas para la opción AUTO_UPDATE_STATISTICS son asincrónicas. El optimizador de consultas no espera a que finalicen las actualizaciones de las estadísticas para compilar las consultas.

La configuración de esta opción en ON no surte efecto a menos que AUTO_UPDATE_STATISTICS se establezca en ON.

De forma predeterminada, la opción AUTO_UPDATE_STATISTICS_ASYNC está configurada en OFF y el optimizador de consultas actualiza las estadísticas sincrónicamente.

OFF

Especifica que las actualizaciones de las estadísticas para la opción AUTO_UPDATE_STATISTICS son sincrónicas. El optimizador de consultas espera a que finalicen las actualizaciones de las estadísticas para compilar las consultas.

La configuración de esta opción en OFF no surte efecto a menos que AUTO_UPDATE_STATISTICS esté configurado en ON.

El estado de esta opción se puede determinar mediante el examen de la columna is_auto_update_stats_async_on en la vista de catálogo sys.databases.

Para obtener más información que describa cuándo utilizar las actualizaciones de estadísticas sincrónicas o asincrónicas, vea la sección "Utilizar las opciones de estadísticas de toda la base de datos" en Estadísticas.

<change_tracking_option> ::=

Controla las opciones de seguimiento de cambios. Puede habilitar el seguimiento de cambios, establecer y cambiar opciones, y deshabilitar el seguimiento de cambios. Para ver ejemplos, consulte la sección de ejemplos más adelante en este tema.

ON

Habilita el seguimiento de cambios para la base de datos. Si habilita el seguimiento de cambios, también puede establecer las opciones AUTO CLEANUP y CHANGE RETENTION.

AUTO_CLEANUP = { ON | OFF }

ON

La información sobre el seguimiento de cambios se quita de forma automática después del período de retención especificado.

OFF

Los datos del seguimiento de cambios no se quitan de la base de datos.

CHANGE_RETENTION =retention_period { DAYS | HOURS | MINUTES }

Especifica el período mínimo para mantener la información del seguimiento de cambios en la base de datos. Los datos solamente se quitan cuando el valor AUTO_CLEANUP es ON.

retention_period es un entero que especifica el componente numérico del período de retención.

El período de retención predeterminado es de 2 días. El período de retención mínimo es de 1 minuto.

OFF

Deshabilita el seguimiento de cambios para la base de datos. Debe deshabilitar el seguimiento de cambios en todas las tablas para poder deshabilitarlo en la base de datos.

<containment_option> ::=

Controla las opciones de contención de la base de datos.

CONTAINMENT = { NONE | PARTIAL}

NONE

La base de datos no es una base de datos independiente.

PARTIAL

La base de datos es una base de datos independiente. El establecimiento de la contención de la base de datos en parcial producirá un error si la base de datos tiene habilitados la replicación, la captura de datos modificados o el seguimiento de cambios. La comprobación de errores se detiene después de un error. Para obtener más información acerca de las bases de datos independientes, vea Bases de datos independientes.

<cursor_option> ::=

Controla las opciones del cursor.

CURSOR_CLOSE_ON_COMMIT { ON | OFF }

ON

Todos los cursores abiertos al confirmar o revertir una transacción se cierran.

OFF

Los cursores permanecen abiertos cuando se confirma una transacción. Cuando se revierte se cierran todos los cursores, excepto los que están definidos como INSENSITIVE o STATIC.

La configuración del nivel de conexión, establecida mediante la instrucción SET, invalida la configuración predeterminada de la base de datos para CURSOR_CLOSE_ON_COMMIT. De forma predeterminada, los clientes ODBC y OLE DB generan una instrucción SET en el nivel de conexión y establecen CURSOR_CLOSE_ON_COMMIT en OFF para la sesión al establecer la conexión con una instancia de SQL Server. Para obtener más información, vea SET CURSOR_CLOSE_ON_COMMIT (Transact-SQL).

El estado de esta opción se puede determinar mediante el examen de la columna is_cursor_close_on_commit_on en la vista de catálogo sys.databases o la propiedad IsCloseCursorsOnCommitEnabled de la función DATABASEPROPERTYEX.

CURSOR_DEFAULT { LOCAL | GLOBAL }

Controla si el ámbito del cursor utiliza LOCAL o GLOBAL.

LOCAL

Cuando se especifica LOCAL y no se define ningún cursor como GLOBAL al crearlo, el ámbito del cursor es local para el lote, procedimiento almacenado o desencadenador en el que se creó el cursor. El nombre del cursor solamente es válido dentro de este ámbito. Es posible hacer referencia al cursor mediante variables de cursor locales del lote, procedimiento almacenado, desencadenador o parámetro OUTPUT del procedimiento almacenado. La asignación del cursor se desasigna implícitamente cuando el lote, el procedimiento almacenado o el desencadenador finaliza, a menos que se haya pasado en un parámetro OUTPUT. Si el cursor se pasa en un parámetro OUTPUT, el cursor se desasigna cuando se cancela la asignación de la última variable que hace referencia a él o se sale del ámbito.

GLOBAL

Si se especifica GLOBAL y no se define ningún cursor como LOCAL al crearlo, el ámbito del cursor es global para la conexión. Se puede hacer referencia al nombre del cursor en cualquier procedimiento almacenado o lote que se ejecute durante la conexión.

El cursor se desasigna implícitamente solamente cuando se realiza la desconexión. Para obtener más información, vea DECLARE CURSOR (Transact-SQL).

El estado de esta opción se puede determinar mediante el examen de la columna is_local_cursor_default en la vista de catálogo sys.databases o la propiedad IsLocalCursorsDefault de la función DATABASEPROPERTYEX.

<database_mirroring>

Para obtener descripciones de los argumentos, vea Reflejo de la base de datos ALTER DATABASE (Transact-SQL).

<date_correlation_optimization_option> ::=

Controla la opción date_correlation_optimization.

DATE_CORRELATION_OPTIMIZATION { ON | OFF }

ON

SQL Server mantiene estadísticas de correlación entre dos tablas de la base de datos que estén vinculadas mediante una restricción FOREIGN KEY y tengan columnas datetime.

OFF

No se mantiene ninguna estadística de correlación.

Para establecer DATE_CORRELATION_OPTIMIZATION en ON, no debe haber ninguna conexión activa con la base de datos, salvo la conexión que está ejecutando la instrucción ALTER DATABASE. Después se admitirán múltiples conexiones.

El valor actual de esta opción se puede determinar mediante el examen de la columna is_date_correlation_on en la vista de catálogo sys.databases.

<db_encryption_option> ::=

Controla el estado del cifrado de la base de datos.

ENCRYPTION {ON | OFF}

Establece que se cifre (ON) o no se cifre (OFF) la base de datos. Para obtener más información acerca del cifrado de bases de datos, vea Cifrado de datos transparente (TDE).

Cuando el cifrado está habilitado en el nivel de la base de datos, se cifrarán todos los grupos de archivos. Todos los grupos de archivos nuevos heredarán la propiedad de cifrado. Si en la base de datos hay grupos de archivos establecidos en READ ONLY, se producirá un error en la operación de cifrado de la base de datos.

Puede ver el estado del cifrado de la base de datos utilizando la vista de administración dinámica sys.dm_database_encryption_keys.

<db_state_option> ::=

Controla el estado de la base de datos.

OFFLINE

La base de datos está cerrada, se ha cerrado correctamente y se ha marcado como sin conexión. La base de datos no se puede modificar mientras está desconectada.

ONLINE

La base de datos está abierta y disponible para su uso.

EMERGENCY

La base de datos está marcada como READ_ONLY, el registro está deshabilitado y el acceso está limitado a los miembros del rol fijo de servidor sysadmin. EMERGENCY se utiliza principalmente para la solución de problemas. Por ejemplo, una base de datos marcada como sospechosa debido a un archivo de registro dañado se puede establecer en el estado EMERGENCY. Esto puede habilitar el acceso de solo lectura del administrador del sistema a la base de datos. Solamente los miembros del rol fijo de servidor sysadmin pueden establecer una base de datos en el estado EMERGENCY.

Nota
Permisos: se requiere el permiso ALTER DATABASE para la base de datos de asunto con el fin de cambiar una base de datos al estado Sin conexión o Emergencia. Se requiere el permiso ALTER ANY DATABASE de nivel de servidor para mover una base de datos de Sin conexión a En línea.

El estado de esta opción se puede determinar mediante el examen de las columnas state y state_desc en la vista de catálogo sys.databases o la propiedad Status de la función DATABASEPROPERTYEX. Para obtener más información, vea Estados de base de datos.

Una base de datos marcada como RESTORING no se puede establecer como OFFLINE, ONLINE o EMERGENCY. Es posible que una base de datos se encuentre en estado RESTORING durante una operación de restauración activa o cuando se produce un error en una operación de restauración de una base de datos o de un archivo de registro, debido a un archivo de copia de seguridad dañado.

<db_update_option> ::=

Controla si se permiten las actualizaciones en la base de datos.

READ_ONLY

Los usuarios pueden leer datos de la base de datos, pero no pueden modificarlos.

Nota
Para mejorar el rendimiento de las consultas, actualice las estadísticas antes de establecer una base de datos en READ_ONLY. Si se necesitan estadísticas adicionales después de establecer una base de datos en READ_ONLY, el Motor de base de datos creará las estadísticas en tempdb. Para obtener más información acerca de las estadísticas para una base de datos de solo lectura, vea Estadísticas.

READ_WRITE

La base de datos está disponible para operaciones de lectura y escritura.

Para cambiar este estado, debe tener acceso exclusivo a la base de datos. Para obtener más información, vea la cláusula SINGLE_USER.

<db_user_access_option> ::=

Controla el acceso del usuario a la base de datos.

SINGLE_USER

Especifica que solamente puede tener acceso a la base de datos un usuario cada vez. Si se especifica SINGLE_USER y hay otros usuarios conectados a la base de datos, la instrucción ALTER DATABASE se bloquea hasta que todos los usuarios se desconecten de la base de datos especificada. Para invalidar este comportamiento, vea la cláusula WITH <termination>.

La base de datos permanece en modo SINGLE_USER incluso si es el propio usuario que estableció la opción el que cierra la sesión. A partir de ese momento, un usuario distinto, pero solo uno, puede conectarse a la base de datos.

Antes de establecer la base de datos como SINGLE_USER, compruebe que la opción AUTO_UPDATE_STATISTICS_ASYNC está establecida en OFF. Cuando se establece en ON, el subproceso en segundo plano usado para actualizar las estadísticas realiza una conexión con la base de datos y no se podrá tener acceso a la base de datos en modo de usuario único. Para ver el estado de esta opción, consulte la columna is_auto_update_stats_async_on en la vista de catálogo sys.databases. Si la opción está establecida en ON, realice las tareas siguientes:

1. Establezca AUTO_UPDATE_STATISTICS_ASYNC en OFF.

2. Compruebe si hay trabajos de estadísticas asincrónicos consultando la vista de administración dinámica sys.dm_exec_background_job_queue.

Si hay trabajos activos, permita que estos se completen o termínelos de forma manual con KILL STATS JOB.

RESTRICTED_USER

RESTRICTED_USER solo permite a los miembros del rol fijo de base de datos db_owner y de los roles fijos de servidor dbcreator y sysadmin conectarse a la base de datos, pero no limita la cantidad de miembros. Todas las conexiones a la base de datos se desconectan en el margen de tiempo especificado por la cláusula de terminación de la instrucción ALTER DATABASE. Una vez que la base de datos ha cambiado al estado RESTRICTED_USER, se rechazan los intentos de conexión por parte de usuarios no cualificados.

MULTI_USER

Todos los usuarios que tengan los permisos correspondientes pueden conectarse a la base de datos.

El estado de esta opción se puede determinar mediante el examen de la columna user_access en la vista de catálogo sys.databases o la propiedad UserAccess de la función DATABASEPROPERTYEX.

<external_access_option> ::=

Controla si recursos externos como los objetos de otra base de datos pueden tener acceso a la base de datos.

DB_CHAINING { ON | OFF }

ON

La base de datos puede ser el origen o el destino de un encadenamiento de propiedad entre bases de datos.

OFF

La base de datos no puede participar en el encadenamiento de propiedad entre bases de datos.

Importante
La instancia de SQL Server reconocerá este valor cuando la opción de servidor cross db ownership chaining sea 0 (OFF). Si cross db ownership chaining es 1 (ON), todas las bases de datos de usuario pueden participar en cadenas de propiedad entre bases de datos, independientemente del valor de esta opción. Esta opción se establece mediante sp_configure.

Para establecer esta opción, se requiere el permiso de CONTROL SERVER en la base de datos.

La opción DB_CHAINING no se puede establecer en estas bases de datos del sistema: master, model y tempdb.

El estado de esta opción se puede determinar mediante el examen de la columna is_db_chaining_on en la vista de catálogo sys.databases.

TRUSTWORTHY { ON | OFF }

ON

Los módulos de base de datos (por ejemplo, las funciones definidas por el usuario o los procedimientos almacenados) que utilizan un contexto de suplantación pueden tener acceso a recursos externos a la base de datos.

OFF

Los módulos de base de datos en un contexto de suplantación no pueden tener acceso a recursos externos a la base de datos.

TRUSTWORTHY se establece en OFF siempre que la base de datos se adjunta.

De forma predeterminada, el valor TRUSTWORTHY se establece en OFF en todas las bases de datos de sistema, excepto msdb. El valor no se puede cambiar en las bases de datos model ni tempdb. Se recomienda no establecer la opción TRUSTWORTHY en ON en la base de datos master.

Para establecer esta opción, se requiere el permiso de CONTROL SERVER en la base de datos.

El estado de esta opción se puede determinar mediante el examen de la columna is_trustworthy_on en la vista de catálogo sys.databases.

DEFAULT_FULLTEXT_LANGUAGE

Especifica el valor de idioma predeterminado para las columnas indizadas de texto completo.

Importante
Esta opción solo se permite cuando CONTAINMENT se ha establecido en PARTIAL. Si CONTAINMENT se establece en NONE, se producirán errores.

DEFAULT_LANGUAGE

Especifica el idioma predeterminado de los nuevos inicios de sesión creados. El idioma se puede especificar proporcionando el identificador local (LCID), el nombre de idioma o el alias de idioma. Para obtener una lista de nombres y alias de idioma aceptables, vea sys.syslanguages (Transact-SQL).

Importante
Esta opción solo se permite cuando CONTAINMENT se ha establecido en PARTIAL. Si CONTAINMENT se establece en NONE, se producirán errores.

NESTED_TRIGGERS

Especifica si un desencadenador AFTER puede actuar en cascada; es decir, realizar una acción que inicia otro desencadenador que, a su vez, inicia otro desencadenador, etc.

Importante
Esta opción solo se permite cuando CONTAINMENT se ha establecido en PARTIAL. Si CONTAINMENT se establece en NONE, se producirán errores.

TRANSFORM_NOISE_WORDS

Se utiliza para suprimir un mensaje de error si las palabras irrelevantes producen un error en una operación booleana en una consulta de texto completo.

Importante
Esta opción solo se permite cuando CONTAINMENT se ha establecido en PARTIAL. Si CONTAINMENT se establece en NONE, se producirán errores.

TWO_DIGIT_YEAR_CUTOFF

Especifica un número entero comprendido entre 1753 y 9999 que representa el año límite para interpretar años de dos dígitos como años de cuatro dígitos.

Importante
Esta opción solo se permite cuando CONTAINMENT se ha establecido en PARTIAL. Si CONTAINMENT se establece en NONE, se producirán errores.

<FILESTREAM_option> ::=

Controla los valores de objetos FileTable.

NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }

OFF

El acceso no transaccional a los datos de FileTable está deshabilitado.

READ_ONLY

Los procesos no transaccionales pueden leer datos FILESTREAM en los objetos FileTable de esta base de datos.

FULL

El acceso no transaccional total a datos FILESTREAM en objetos FileTable está habilitado.

DIRECTORY_NAME = <directory_name>

Un nombre de directorio compatible con Windows. Este nombre debe ser único entre todos los nombres de directorio de nivel de base de datos en la instancia de SQL Server. La comparación de unicidad no distingue mayúsculas de minúsculas, independientemente de la configuración de intercalación. Esta opción se debe establecer antes de crear un objeto FileTable en esta base de datos.

<parameterization_option> ::=

Controla la opción de parametrización.

PARAMETERIZATION { SIMPLE | FORCED }

SIMPLE

Las consultas incluyen parámetros en función del comportamiento predeterminado de la base de datos.

FORCED

SQL Server incluye parámetros para todas las consultas de la base de datos.

El valor actual de esta opción se puede determinar mediante el examen de la columna is_parameterization_forced en la vista de catálogo sys.databases.

<recovery_option> ::=

Controla las opciones de recuperación de base de datos y la comprobación de errores de E/S de disco.

FULL

Proporciona una restauración completa tras un error del medio, utilizando copias de seguridad del registro de transacciones. Si un archivo de datos está dañado, la recuperación del medio puede restaurar todas las transacciones confirmadas. Para obtener más información, vea Modelos de recuperación (SQL Server).

BULK_LOGGED

Proporciona una recuperación tras un error del medio mediante la combinación del rendimiento óptimo y el uso del espacio de registro mínimo para determinadas operaciones a gran escala o masivas. Para obtener información acerca de las operaciones que se pueden registrar mínimamente, vea El registro de transacciones (SQL Server). En el modelo de recuperación BULK_LOGGED, el registro de estas operaciones es mínimo. Para obtener más información, vea Modelos de recuperación (SQL Server).

SIMPLE

Se proporciona una estrategia de copia de seguridad sencilla que utiliza un espacio de registro mínimo. Se puede volver a utilizar el espacio de registro de forma automática cuando ya no se necesite para la recuperación tras errores del servidor. Para obtener más información, vea Modelos de recuperación (SQL Server).

Importante
El modelo de recuperación simple es más fácil de administrar que los otros dos modelos, pero a costa de un mayor riesgo de perder los datos si se daña un archivo de datos. Todos los cambios se pierden, desde la copia de seguridad de base de datos más reciente o desde la copia de seguridad diferencial de la base de datos, y se deben volver a incluir de forma manual.

El modelo de recuperación predeterminado se determina mediante el modelo de recuperación de la base de datos model. Para obtener más información acerca de cómo seleccionar el modelo de recuperación adecuado, vea Modelos de recuperación (SQL Server).

El estado de esta opción se puede determinar examinando las columnas recovery_model y recovery_model_desc de la vista de catálogo sys.databases o la propiedad Recovery de la función DATABASEPROPERTYEX.

TORN_PAGE_DETECTION { ON | OFF }

ON

Las páginas incompletas se pueden detectar mediante el Motor de base de datos.

OFF

Las páginas incompletas no se pueden detectar mediante Motor de base de datos.

Importante
La estructura de sintaxis TORN_PAGE_DETECTION ON | OFF se quitará en una versión futura de SQL Server. Evite utilizar esta estructura de sintaxis en nuevos trabajos de desarrollo y prevea modificar las aplicaciones que actualmente la utilizan. Utilice la opción PAGE_VERIFY en su lugar.

PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }

Detecta páginas de bases de datos dañadas por errores de ruta de E/S de disco. Los errores de ruta de E/S de disco pueden producir daños en la base de datos debidos por lo general a problemas con el suministro eléctrico o a errores del hardware del disco que ocurren en el momento en que se está escribiendo en el disco.

CHECKSUM

Calcula una suma de comprobación del contenido de toda la página y almacena el valor en el encabezado de página si se escribe una página en el disco. Si la página se lee desde el disco, la suma de comprobación se vuelve a calcular y se compara con el valor de suma de comprobación almacenado en el encabezado de página. Si el valor no coincide, se genera el mensaje de error 824 (indica un error de la suma de comprobación) para el registro de errores de SQL Server, así como para el registro de eventos de Windows. Un error de la suma de comprobación indica un problema de ruta de E/S. Para determinar la causa, es necesario investigar el hardware, los controladores de firmware, el BIOS, los controladores de filtro (por ejemplo, software antivirus) y otros componentes de ruta de E/S.

TORN_PAGE_DETECTION

Guarda una pauta específica de 2 bits por cada sector de 512 bytes en la página de base de datos de 8 kilobytes (KB) y la almacena en el encabezado de página de la base de datos al escribir la página en el disco. Si la página se lee desde el disco, los bits rasgados almacenados en el encabezado de página se comparan con la información del sector de la página real. Los valores no coincidentes indican que solamente se ha escrito en el disco una parte de la página. En esta situación, se genera el mensaje de error 824 (indica un error de página rasgada) tanto para el registro de errores de SQL Server como para el registro de eventos de Windows. Las páginas rasgadas se suelen detectar mediante la recuperación de la base de datos si se trata realmente de la escritura incompleta de una página. No obstante, otros errores de ruta de E/S pueden generar una página rasgada en cualquier momento.

NONE

Las escrituras de páginas de bases de datos no generan un valor CHECKSUM o TORN_PAGE_DETECTION. SQL Server no comprobará ninguna suma de comprobación o página rasgada durante una lectura aunque haya un valor CHECKSUM o TORN_PAGE_DETECTION en el encabezado de página.

Tenga en cuenta los siguientes puntos importantes cuando utilice la opción PAGE_VERIFY:

• El valor predeterminado es CHECKSUM.

• Si una base de datos de usuario o del sistema se actualiza a SQL Server 2005 o una versión posterior, se conserva el valor de PAGE_VERIFY (NONE o TORN_PAGE_DETECTION). Se recomienda utilizar CHECKSUM.

  Nota

  En las versiones anteriores de SQL Server, la opción de base de datos PAGE_VERIFY está establecida en NONE para la base de datos tempdb y no se puede modificar. En SQL Server 2008 y versiones posteriores, el valor predeterminado para la base de datos tempdb es CHECKSUM para las nuevas instalaciones de SQL Server. Al actualizar una instalación de SQL Server, el valor predeterminado sigue siendo NONE. La opción se puede modificar. Se recomienda usar CHECKSUM para la base de datos tempdb.

• Es posible que TORN_PAGE_DETECTION utilice menos recursos, pero proporciona en cambio un subconjunto mínimo de la protección de CHECKSUM.

• PAGE_VERIFY se puede configurar sin dejar la base de datos sin conexión, ni bloquearla ni impedir la simultaneidad en ella.

• CHECKSUM y TORN_PAGE_DETECTION se excluyen mutuamente. No se pueden habilitar ambas opciones al mismo tiempo.

Si se detecta un error de suma de comprobación o de página rasgada, puede realizar una recuperación mediante la restauración de los datos o una regeneración del índice, si el error se limita únicamente a las páginas de índice. Si detecta un error de suma de comprobación, ejecute DBCC CHECKDB para determinar el tipo de página o páginas de base de datos afectadas. Para obtener más información acerca de las opciones de restauración, vea RESTORE (argumentos, Transact-SQL). Aunque la restauración de los datos resolverá el problema de los datos dañados, es necesario diagnosticar y corregir la causa (por ejemplo, un error del hardware de disco) lo antes posible, para evitar errores continuos.

SQL Server vuelve a intentar cualquier lectura que genere un error con una suma de comprobación, una página rasgada u otros errores de E/S, en cuatro ocasiones. Si la lectura se desarrolla correctamente en uno de los reintentos, se escribe un mensaje en el registro de errores y el comando que ha desencadenado la lectura continúa. Si los reintentos no se realizan correctamente, el comando genera el mensaje de error 824.

Para obtener más información acerca de la suma de comprobación, la página rasgada, los reintentos de lectura, los mensajes de error 823 y 824, y otras características de auditoría de E/S de SQL Server, vea este sitio web de Microsoft .

La configuración actual de esta opción se puede determinar examinando la columna page_verify_option de la vista de catálogo sys.databases o la propiedad IsTornPageDetectionEnabled de la función DATABASEPROPERTYEX.

<target_recovery_time_option> ::=

Especifica la frecuencia de puntos de comprobación indirectos por base de datos. El valor predeterminado es 0, que indica que la base de datos utilizará puntos de comprobación automáticos, cuya frecuencia depende del valor de intervalo de recuperación de la instancia de servidor.

TARGET_RECOVERY_TIME =target_recovery_time { SECONDS | MINUTES }

target_recovery_time

Especifica el límite máximo de tiempo para recuperar la base de datos especificada en caso de bloqueo.

SECONDS

Indica que target_recovery_time se expresa como el número de segundos.

MINUTES

Indica que target_recovery_time se expresa como el número de minutos.

Para obtener más información sobre puntos de comprobación indirectos, vea Puntos de comprobación de base de datos (SQL Server).

<service_broker_option> ::=

Controla las siguientes opciones de Service Broker: habilita o deshabilita la entrega de mensajes, establece un nuevo identificador de Service Broker o establece prioridades de conversación en ON u OFF.

ENABLE_BROKER

Indica que se habilite Service Broker para la base de datos especificada. La entrega de mensajes se inicia y la marca is_broker_enabled se establece en true en la vista de catálogo sys.databases. La base de datos conserva el identificador de Service Broker existente.

Nota

ENABLE_BROKER requiere un bloqueo exclusivo de base de datos. Si otras sesiones tienen recursos bloqueados en la base de datos, ENABLE_BROKER esperará hasta que las demás sesiones liberen sus bloqueos. Para habilitar Service Broker en una base de datos de usuario, asegúrese de que ninguna otra sesión esté utilizando la base de datos antes de ejecutar la instrucción ALTER DATABASE SET ENABLE_BROKER, por ejemplo, colocando la base de datos en modo de usuario único. Para habilitar Service Broker en la base de datos msdb, detenga en primer lugar el Agente SQL Server para que Service Broker pueda obtener el bloqueo necesario.

DISABLE_BROKER

Indica que se deshabilite Service Broker para la base de datos especificada. La entrega de mensajes se detiene y la marca is_broker_enabled se establece en false en la vista de catálogo sys.databases. La base de datos conserva el identificador de Service Broker existente.

NEW_BROKER

Especifica que la base de datos debe recibir un identificador de agente nuevo. Dado que la base de datos se considera como un Service Broker nuevo, todas las conversaciones existentes en la base de datos se quitan inmediatamente sin generar mensajes de finalización de diálogo. Cualquier ruta que haga referencia al identificador de Service Broker anterior se debe volver a crear con el nuevo identificador.

ERROR_BROKER_CONVERSATIONS

Especifica que la entrega de mensajes de Service Broker está habilitada. Esto conserva el identificador de Service Broker existente para la base de datos. Service Broker finaliza todas las conversaciones de la base de datos con un error. Esto permite que las aplicaciones realicen una limpieza regular de las conversaciones existentes.

HONOR_BROKER_PRIORITY {ON | OFF}

ON

Las operaciones de envío tienen en cuenta los niveles de prioridad asignados a las conversaciones. Los mensajes de las conversaciones que tienen niveles de prioridad altos se envían antes que los que tienen asignados niveles de prioridad bajos.

OFF

Las operaciones de envío se ejecutan como si todas las conversaciones tuvieran el nivel de prioridad predeterminado.

Los cambios de la opción HONOR_BROKER_PRIORITY tienen efecto inmediato para los diálogos nuevos o los que no tiene ningún mensaje en espera de ser enviado. Los diálogos que tienen mensajes en espera de ser enviados cuando se ejecuta ALTER DATABASE no adoptarán el nuevo valor hasta que se haya enviado alguno de los mensajes del diálogo. La cantidad de tiempo que transcurre hasta que se inicien todos los diálogos con el nuevo valor puede variar considerablemente.

El valor actual de esta propiedad se notifica en la columna is_broker_priority_honored de la vista de catálogo sys.databases.

<snapshot_option> ::=

Determina el nivel de aislamiento de transacción.

ALLOW_SNAPSHOT_ISOLATION { ON | OFF }

ON

Habilita la opción de instantánea en el nivel de base de datos. Cuando se habilita, las instrucciones DML inician la generación de versiones de fila aunque ninguna transacción utilice el aislamiento de instantánea. Una vez habilitada esta opción, las transacciones pueden especificar el nivel de aislamiento de transacción SNAPSHOT. Si se ejecuta una transacción en el nivel de aislamiento SNAPSHOT, todas las instrucciones verán una instantánea de los datos tal como estaban al inicio de la transacción. Si una transacción ejecutada en el nivel de aislamiento SNAPSHOT tiene acceso a los datos de varias bases de datos, ALLOW_SNAPSHOT_ISOLATION debe establecerse en ON en todas las bases de datos o cada instrucción de la transacción debe utilizar sugerencias de bloqueo en cualquier referencia de una cláusula FROM a una tabla de una base de datos donde ALLOW_SNAPSHOT_ISOLATION sea OFF.

OFF

Desactiva la opción de instantánea en el nivel de base de datos. Las transacciones no pueden especificar el nivel de aislamiento de transacción SNAPSHOT.

Si se establece ALLOW_SNAPSHOT_ISOLATION en un estado nuevo (de ON a OFF o de OFF a ON), ALTER DATABASE no devuelve el control al autor de la llamada hasta confirmar todas las transacciones existentes de la base de datos. Si la base de datos ya se encuentra en el estado especificado en la instrucción ALTER DATABASE, se devuelve de inmediato el control al autor de la llamada. Si la instrucción ALTER DATABASE no se devuelve rápidamente, utilice sys.dm_tran_active_snapshot_database_transactions para determinar si se trata de transacciones de ejecución prolongada. Si se cancela la instrucción ALTER DATABASE, la base de datos permanece en el estado en que estaba al iniciar ALTER DATABASE. La vista de catálogo sys.databases indica el estado de las transacciones de aislamiento de instantáneas en la base de datos. Si snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF se detendrá durante seis segundos y volverá a intentar la operación.

No puede cambiar el estado de ALLOW_SNAPSHOT_ISOLATION si la base de datos está establecida en OFFLINE.

Si establece ALLOW_SNAPSHOT_ISOLATION en una base de datos READ_ONLY, la configuración se mantendrá si la base de datos se establece posteriormente en READ_WRITE.

Puede cambiar la configuración de ALLOW_SNAPSHOT_ISOLATION para las bases de datos master, model, msdb y tempdb. Si cambia la configuración para tempdb, dicha configuración se mantiene cada vez que la instancia del Motor de base de datos se detiene y se reinicia. Si cambia la configuración para model, dicha configuración se convierte en predeterminada para todas las nuevas bases de datos creadas, excepto para tempdb.

La opción es ON de forma predeterminada para las bases de datos master y msdb.

El valor actual de esta opción se puede determinar mediante el examen de la columna snapshot_isolation_state en la vista de catálogo sys.databases.

READ_COMMITTED_SNAPSHOT { ON | OFF }

ON

Habilita la opción de instantánea de lectura confirmada en el nivel de base de datos. Cuando se habilita, las instrucciones DML inician la generación de versiones de fila aunque ninguna transacción utilice el aislamiento de instantánea. Una vez habilitada esta opción, las transacciones que especifican el nivel de aislamiento de lectura confirmada usan versiones de fila en lugar de bloqueos. Si una transacción se ejecuta en el nivel de aislamiento de lectura confirmada, todas las instrucciones ven una instantánea de los datos tal como estaban al inicio de la instrucción.

OFF

Desactiva la opción de instantánea de lectura confirmada en el nivel de base de datos. Las transacciones que especifican el nivel de aislamiento READ COMMITTED utilizan el bloqueo.

Para establecer READ_COMMITTED_SNAPSHOT en ON u OFF, no puede haber ninguna conexión activa a la base de datos, excepto la que ejecuta el comando ALTER DATABASE. Sin embargo, no es necesario que la base de datos esté en modo de usuario único. No puede cambiar el estado de esta opción si la base de datos está establecida en OFFLINE.

Si establece READ_COMMITTED_SNAPSHOT en una base de datos READ_ONLY, la configuración se mantiene si la base de datos se establece después en READ_WRITE.

READ_COMMITTED_SNAPSHOT no se puede establecer en ON para las bases de datos del sistema master, tempdb o msdb. Si cambia la configuración para model, dicha configuración se convierte en predeterminada para todas las nuevas bases de datos creadas, excepto para tempdb.

El valor actual de esta opción se puede determinar mediante el examen de la columna is_read_committed_snapshot_on en la vista de catálogo sys.databases.

<sql_option> ::=

Controla las opciones de cumplimiento con ANSI en el nivel de base de datos.

ANSI_NULL_DEFAULT { ON | OFF }

Determina el valor predeterminado, NULL o NOT NULL, de una columna o del tipo definido por el usuario CLR para los que no se ha definido la nulabilidad explícitamente en las instrucciones CREATE TABLE o ALTER TABLE. Las columnas para las que se hayan definido restricciones siguen las reglas de las restricciones, independientemente de esta configuración.

ON

El valor predeterminado es NULL.

OFF

El valor predeterminado es NOT NULL.

La configuración del nivel de conexión, establecida mediante la instrucción SET, invalida la configuración predeterminada del nivel de base de datos para ANSI_NULL_DEFAULT. De forma predeterminada, los clientes ODBC y OLE DB generan una instrucción SET en el nivel de conexión y establecen ANSI_NULL_DEFAULT en ON para la sesión cuando se realiza la conexión con una instancia de SQL Server. Para obtener más información, vea SET ANSI_NULL_DFLT_ON (Transact-SQL).

Para la compatibilidad con ANSI, si se establece la opción de base de datos ANSI_NULL_DEFAULT en ON, el valor predeterminado cambia a NULL.

El estado de esta opción se puede determinar mediante el examen de la columna is_ansi_null_default_on en la vista de catálogo sys.databases o la propiedad IsAnsiNullDefault de la función DATABASEPROPERTYEX.

ANSI_NULLS { ON | OFF }

ON

Todas las comparaciones con un valor NULL se evalúan como UNKNOWN.

OFF

Las comparaciones de valores no UNICODE con un valor NULL se evalúan como TRUE si ambos valores son NULL.

Importante
En una versión futura de SQL Server, ANSI_NULLS siempre estará ON y cualquier aplicación que establezca explícitamente la opción en OFF producirá un error. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan.

La configuración del nivel de conexión establecida mediante la instrucción SET invalida la configuración predeterminada de la base de datos para ANSI_NULLS. De forma predeterminada, los clientes ODBC y OLE DB generan una instrucción SET en el nivel de conexión y establecen ANSI_NULLS en ON para la sesión al realizar la conexión con una instancia de SQL Server. Para obtener más información, vea SET ANSI_NULLS (Transact-SQL).

El valor de SET ANSI_NULLS también debe estar en ON al crear o realizar cambios en los índices en columnas calculadas o vistas indizadas.

El estado de esta opción se puede determinar mediante el examen de la columna is_ansi_nulls_on en la vista de catálogo sys.databases o la propiedad IsAnsiNullsEnabled de la función DATABASEPROPERTYEX.

ANSI_PADDING { ON | OFF }

ON

Las cadenas se rellenan hasta la misma longitud antes de la conversión o inserción en un tipo de datos varchar o nvarchar.

Los espacios en blanco finales de los valores de caracteres insertados en las columnas varchar o nvarchar y los ceros finales de los valores binarios insertados en las columnas varbinary no se recortan. Los valores no se rellenan hasta completar la longitud de la columna.

OFF

Los espacios en blanco finales para varchar o nvarchar y los ceros para varbinary se recortan.

Si se especifica OFF, esta opción solamente afecta a la definición de las columnas nuevas.

Importante

En una versión futura de SQL Server, ANSI_PADDING siempre estará en ON y cualquier aplicación que establezca explícitamente la opción en OFF producirá un error. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan. Se recomienda establecer siempre ANSI_PADDING en ON. ANSI_PADDING también debe estar en ON al crear o tratar índices en columnas calculadas o vistas indizadas.

Las columnas char(n) y binary(n) que permiten valores NULL se rellenan hasta completar la longitud de la columna si ANSI_PADDING se establece en ON, pero los espacios en blanco y los ceros finales se recortan si ANSI_PADDING es OFF. Las columnas char(n) y binary(n) que no permiten valores NULL siempre se rellenan hasta completar la longitud de la columna.

La configuración del nivel de conexión establecida mediante la instrucción SET invalida la configuración predeterminada del nivel de base de datos para ANSI_PADDING. De forma predeterminada, los clientes ODBC y OLE DB generan una instrucción SET en el nivel de conexión y establecen ANSI_PADDING en ON para la sesión al realizar la conexión con una instancia de SQL Server. Para obtener más información, vea SET ANSI_PADDING (Transact-SQL).

Importante

El estado de esta opción se puede determinar mediante el examen de la columna is_ansi_padding_on en la vista de catálogo sys.databases o la propiedad IsAnsiPaddingEnabled de la función DATABASEPROPERTYEX.

ANSI_WARNINGS { ON | OFF }

ON

Se emiten los mensajes de error o advertencias cuando se producen condiciones como la división por cero o cuando aparecen valores NULL en funciones de agregación.

OFF

No se genera ninguna advertencia ni se devuelven valores NULL si se producen condiciones como la división por cero.

El valor de SET ANSI_WARNINGS debe ser ON al crear o realizar cambios en los índices en columnas calculadas o vistas indizadas.

La configuración del nivel de conexión establecida mediante la instrucción SET invalida la configuración predeterminada de la base de datos para ANSI_WARNINGS. De forma predeterminada, los clientes ODBC y OLE DB generan una instrucción SET en el nivel de conexión y establecen ANSI_WARNINGS en ON para la sesión al realizar la conexión con una instancia de SQL Server. Para obtener más información, vea SET ANSI_WARNINGS (Transact-SQL).

El estado de esta opción se puede determinar mediante el examen de la columna is_ansi_warnings_on en la vista de catálogo sys.databases o la propiedad IsAnsiWarningsEnabled de la función DATABASEPROPERTYEX.

ARITHABORT { ON | OFF }

ON

Se finaliza una consulta cuando se produce un error de desbordamiento o de división por cero durante su ejecución.

OFF

Se muestra un mensaje de advertencia si se produce uno de estos errores, pero la consulta, lote o transacción continúa procesándose como si no se hubiera producido ningún error.

El valor de SET ARITHABORT debe ser ON al crear o realizar cambios en los índices en columnas calculadas o vistas indizadas.

El estado de esta opción se puede determinar mediante el examen de la columna is_arithabort_on en la vista de catálogo sys.databases o la propiedad IsArithmeticAbortEnabled de la función DATABASEPROPERTYEX.

COMPATIBILITY_LEVEL { 90 | 100 | 110 }

Para obtener más información, vea Nivel de compatibilidad de ALTER DATABASE (Transact-SQL).

CONCAT_NULL_YIELDS_NULL { ON | OFF }

ON

El resultado de una operación de concatenación es NULL si alguno de los operandos es NULL. Por ejemplo, la concatenación de la cadena de caracteres "Esto es" y NULL da como resultado el valor NULL, y no el valor "Esto es".

OFF

El valor NULL se trata como una cadena de caracteres vacía.

El valor de CONCAT_NULL_YIELDS_NULL también debe ser ON al crear o realizar cambios en los índices en columnas calculadas o vistas indizadas.

Importante
En una versión futura de SQL Server, CONCAT_NULL_YIELDS_NULL siempre estará en ON y cualquier aplicación que establezca explícitamente la opción en OFF producirá un error. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan.

La configuración del nivel de conexión establecida mediante la instrucción SET invalida la configuración predeterminada de la base de datos para CONCAT_NULL_YIELDS_NULL. De forma predeterminada, los clientes ODBC y OLE DB generan una instrucción SET en el nivel de conexión y establecen CONCAT_NULL_YIELDS_NULL en ON para la sesión al realizar la conexión con una instancia de SQL Server. Para obtener más información, vea SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).

El estado de esta opción se puede determinar mediante el examen de la columna is_concat_null_yields_null_on en la vista de catálogo sys.databases o la propiedad IsNullConcat de la función DATABASEPROPERTYEX.

QUOTED_IDENTIFIER { ON | OFF }

ON

Las comillas dobles se pueden usar para identificadores delimitados.

Todas las cadenas delimitadas por comillas dobles se interpretan como identificadores de objetos. Los identificadores entre comillas no tienen que adaptarse a las reglas de Transact-SQL para identificadores. Pueden ser palabras clave e incluir caracteres que no suelen permitirse en los identificadores de Transact-SQL. Si una comilla simple (') forma parte de la cadena literal, puede representarse mediante comillas dobles (").

OFF

Los identificadores no se pueden incluir entre comillas y deben seguir todas las reglas de Transact-SQL para los identificadores. Los literales se pueden delimitar con comillas simples o dobles.

SQL Server también permite delimitar los identificadores con corchetes ([ ]). Los identificadores entre corchetes pueden usarse siempre, independientemente de la configuración de QUOTED_IDENTIFIER. Para obtener más información, vea Identificadores de base de datos..

Al crear una tabla, la opción QUOTED IDENTIFIER siempre se almacena como ON en los metadatos de la tabla, incluso si la opción está establecida en OFF al crear la tabla.

La configuración en el nivel de conexión establecida mediante la instrucción SET invalida la configuración predeterminada de la base de datos para QUOTED_IDENTIFIER. De forma predeterminada, los clientes ODBC y OLE DB generan una instrucción SET en el nivel de conexión y establecen QUOTED_IDENTIFIER en ON al realizar la conexión con una instancia de SQL Server. Para obtener más información, vea SET QUOTED_IDENTIFIER (Transact-SQL).

El estado de esta opción se puede determinar mediante el examen de la columna is_quoted_identifier_on en la vista de catálogo sys.databases o la propiedad IsQuotedIdentifiersEnabled de la función DATABASEPROPERTYEX.

NUMERIC_ROUNDABORT { ON | OFF }

ON

Se genera un error cuando se produce una pérdida de precisión en una expresión.

OFF

Las pérdidas de precisión no generan mensajes de error y el resultado se redondea con la precisión de la columna o variable que lo almacena.

El valor de NUMERIC_ROUNDABORT debe ser OFF al crear o realizar cambios en índices de columnas calculadas o vistas indizadas.

El estado de esta opción se puede determinar mediante el examen de la columna is_numeric_roundabort_on en la vista de catálogo sys.databases o la propiedad IsNumericRoundAbortEnabled de la función DATABASEPROPERTYEX.

RECURSIVE_TRIGGERS { ON | OFF }

ON

Se permite la activación recursiva de desencadenadores AFTER.

OFF

No se permite únicamente la activación recursiva directa de desencadenadores AFTER. Además, para deshabilitar la recursividad indirecta de desencadenadores AFTER, la opción de servidor de desencadenadores anidados se debe establecer en 0 con sp_configure.

Nota
La recursividad directa solo se evita cuando RECURSIVE_TRIGGERS se establece en OFF. Para deshabilitar la recursividad indirecta, también debe establecerse la opción de servidor nested triggers en 0.

El estado de esta opción se puede determinar mediante el examen de la columna is_recursive_triggers_on en la vista de catálogo sys.databases o la propiedad IsRecursiveTriggersEnabled de la función DATABASEPROPERTYEX.

WITH <termination> ::=

Especifica el momento en que se revierten las transacciones incompletas cuando la base de datos pasa de un estado a otro. Si se omite la cláusula de terminación, la instrucción ALTER DATABASE espera indefinidamente a que se produzca un bloqueo en la base de datos. Solamente se puede especificar una cláusula de terminación y debe seguir a las cláusulas SET.

Nota
No todas las opciones de base de datos utilizan la cláusula de <termination> WITH. Para obtener más información, vea la tabla situada en el apartado "Configurar opciones" en la sección Comentarios.

ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE

Especifica si la operación de reversión se ejecuta transcurrido un número de segundos determinado o de forma inmediata.

NO_WAIT

Especifica que se producirá un error en la solicitud si el cambio solicitado en el estado u opción de la base de datos no se puede completar inmediatamente sin esperar a que las propias transacciones se confirmen o reviertan.

Para recuperar la configuración actual de las opciones de base de datos, utilice la vista de catálogo sys.databases o DATABASEPROPERTYEX.

Una vez configurada una opción de la base de datos, la modificación surte efecto de inmediato.

Para cambiar los valores predeterminados de cualquiera de las opciones de las bases de datos recién creadas, cambie la opción adecuada en la base de datos model.

No todas las opciones de base de datos utilizan la cláusula WITH <termination>, ni se pueden especificar en combinación con otras opciones. En la siguiente tabla se incluyen estas opciones, su estado y el estado de terminación.

La memoria caché del plan para la instancia de SQL Server se borra si se establece alguna de las opciones siguientes:

Al borrar la memoria caché del plan, se provoca una recompilación de todos los planes de ejecución posteriores y puede ocasionar una disminución repentina y temporal del rendimiento de las consultas. Para cada almacén de caché borrado de la memoria caché del plan, el registro de errores de SQL Server contendrá el siguiente mensaje informativo: "SQL Server ha detectado %d instancias de vaciado del almacén de caché '%s' (parte de la memoria caché del plan) debido a determinadas operaciones de mantenimiento de base de datos o reconfiguración". Este mensaje se registra cada cinco minutos siempre que se vacíe la memoria caché dentro de ese intervalo de tiempo.

En el siguiente ejemplo se establece el modelo de recuperación y las opciones de comprobación de páginas de datos para la base de datos de ejemplo AdventureWorks2012 .

USE master;
GO
ALTER DATABASE AdventureWorks2012 
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO

El cambio del estado de una base de datos o un grupo de archivos a READ_ONLY o READ_WRITE requiere el acceso exclusivo a la base de datos. En el siguiente ejemplo la base de datos se establece en el modo SINGLE_USER para obtener acceso exclusivo. A continuación, el ejemplo establece el estado de la base de datos AdventureWorks2012 en READ_ONLY y devuelve el acceso a la base de datos a todos los usuarios.

Nota
En este ejemplo se utiliza la opción de terminación WITH ROLLBACK IMMEDIATE en la primera instrucción ALTER DATABASE. Todas las transacciones incompletas se revierten y las restantes conexiones con la base de datos AdventureWorks2012 se desconectan de inmediato.

USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO

En el siguiente ejemplo se habilita la opción del marco de aislamiento de instantánea para la base de datos AdventureWorks2012 .

USE AdventureWorks2012;
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'AdventureWorks2012';
GO
USE master;
GO
ALTER DATABASE AdventureWorks2012
    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'AdventureWorks2012';
GO

El conjunto de resultados muestra que el marco de aislamiento de instantánea está habilitado.

name                 snapshot_isolation_state  description

-------------------- ------------------------  ----------

AdventureWorks2012 1                         ON

En el ejemplo siguiente se habilita el seguimiento de cambios para la base de datos AdventureWorks2012 y se establece el período de retención en 4 días.

ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP = ON, CHANGE_RETENTION = 2 DAYS);

En el ejemplo siguiente se muestra cómo cambiar el período de retención a 3 días.

ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING (CHANGE_RETENTION = 3 DAYS);

En el ejemplo siguiente se muestra cómo deshabilitar el seguimiento de cambios para la base de datos AdventureWorks2012 .

ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING = OFF;