Opciones de ALTER DATABASE SET (Transact-SQL)

En este tema se describe la sintaxis de ALTER DATABASE, relacionada con la configuración de las opciones de las bases de datos. Para obtener información acerca de otras sintaxis de ALTER DATABASE, vea ALTER DATABASE (Transact-SQL). La creación de reflejo de la base de datos y los niveles de compatibilidad son opciones de SET, pero se describen en otros temas debido a su extensión. Para obtener más información, vea Creación de reflejo de la base de datos ALTER DATABASE (Transact-SQL) y Nivel de compatibilidad de ALTER DATABASE (Transact-SQL).

Convenciones de sintaxis de Transact-SQL

Sintaxis

ALTER DATABASE database_name 
SET 
{
    { <optionspec> [ ,...n ] [ WITH <termination> ] }
}

<optionspec>::= 
{
    <auto_option> 
  | <change_tracking_option> 
  | <cursor_option> 
  | <database_mirroring_option>
  | <date_correlation_optimization_option>
  | <db_encryption_option>
  | <db_state_option>
  | <db_update_option> 
  | <db_user_access_option>
  | <external_access_option>
  | <parameterization_option>
  | <recovery_option> 
  | <service_broker_option>
  | <snapshot_option>
  | <sql_option> 
}

<auto_option> ::= 

{
    AUTO_CLOSE { ON | OFF } 
  | AUTO_CREATE_STATISTICS { ON | OFF } 
  | AUTO_SHRINK { ON | OFF } 
  | AUTO_UPDATE_STATISTICS { ON | OFF } 
  | AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}

<change_tracking_option> ::=
{
  CHANGE_TRACKING { 
        = ON [ <change_tracking_option_list > ] |
    <change_tracking_option_list> |
        = OFF
  }
}

<change_tracking_option_list> ::=
{
    ( <change_tracking_option> | <change_tracking_option_list> , 
    <change_tracking_option> )
}
  
<change_tracking_option> ::=
{
  AUTO_CLEANUP = { ON | OFF } 
  | CHANGE_RETENTION = { retention_period { DAYS | HOURS | MINUTES } ]
}

<cursor_option> ::= 
{
    CURSOR_CLOSE_ON_COMMIT { ON | OFF } 
  | CURSOR_DEFAULT { LOCAL | GLOBAL } 
}

<database_mirroring_option>ALTER DATABASE Database Mirroring<date_correlation_optimization_option> ::=
{
    DATE_CORRELATION_OPTIMIZATION { ON | OFF }
}

<db_encryption_option> ::=
    ENCRYPTION { ON | OFF }

<db_state_option> ::=
    { ONLINE | OFFLINE | EMERGENCY }

<db_update_option> ::=
    { READ_ONLY | READ_WRITE }

<db_user_access_option> ::=
    { SINGLE_USER | RESTRICTED_USER | MULTI_USER }

<external_access_option> ::=
{
    DB_CHAINING { ON | OFF }

  | TRUSTWORTHY { ON | OFF }
}
<parameterization_option> ::=
{
    PARAMETERIZATION { SIMPLE | FORCED }
}

<recovery_option> ::= 
{
    RECOVERY { FULL | BULK_LOGGED | SIMPLE } 
  | TORN_PAGE_DETECTION { ON | OFF }
  | PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
}

<service_broker_option> ::=
{
    ENABLE_BROKER
  | DISABLE_BROKER
  | NEW_BROKER
  | ERROR_BROKER_CONVERSATIONS
  | HONOR_BROKER_PRIORITY { ON | OFF}
}

<snapshot_option> ::=
{
    ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
  | READ_COMMITTED_SNAPSHOT {ON | OFF }
}
<sql_option> ::= 
{
    ANSI_NULL_DEFAULT { ON | OFF } 
  | ANSI_NULLS { ON | OFF } 
  | ANSI_PADDING { ON | OFF } 
  | ANSI_WARNINGS { ON | OFF } 
  | ARITHABORT { ON | OFF } 
  | COMPATIBILITY_LEVEL = { 80 | 90 | 100 }
  | CONCAT_NULL_YIELDS_NULL { ON | OFF } 
  | NUMERIC_ROUNDABORT { ON | OFF } 
  | QUOTED_IDENTIFIER { ON | OFF } 
  | RECURSIVE_TRIGGERS { ON | OFF } 
}

<termination>::= 
{
    ROLLBACK AFTER integer [ SECONDS ] 
  | ROLLBACK IMMEDIATE 
  | NO_WAIT
}

Argumentos

<auto_option>::=

Controla las opciones automáticas.

• database_name
  Es el nombre de la base de datos que se va a modificar.

• 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 Database Engine (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.

  [!NOTA]

  En versiones anteriores de SQL Server, AUTO_CLOSE es un proceso sincrónico que puede reducir el rendimiento cuando una aplicación, que establece e interrumpe repetidamente la conexión a Database Engine (Motor de base de datos), tiene acceso a la base de datos. En SQL Server 2005, el proceso AUTO_CLOSE es asincrónico. La apertura y cierre repetidos de la base de datos ya no reduce el rendimiento.

  El estado de esta opción se puede determinar mediante el examen de la columna is_auto_close_on de la vista de catálogo sys.databases o el examen de 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 devolverán 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 produce 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. En SQL Server 2005 Service Pack 2, para cada almacén de memoria caché borrado de la memoria caché del plan, el registro de errores de SQL Server contendrá un mensaje informativo similar al siguiente: "SQL Server ha detectado %d instancias de vaciado del almacén de memoria 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 de la vista de catálogo sys.databases o el examen de 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 Utilizar las estadísticas para mejorar el rendimiento de las consultas.

• 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 solamente 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 sólo 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 de la vista de catálogo sys.databases o el examen de la propiedad IsAutoShrink de la función DATABASEPROPERTYEX.

• AUTO_UPDATE_STATISTICS { ON | OFF }

  • ON
    Especifica que el optimizador de consultas actualiza las estadísticas cuando son usadas por una consulta y parece que puedan 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é, Database Engine (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 de la vista de catálogo sys.databases o el examen de 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 Utilizar las estadísticas para mejorar el rendimiento de las consultas.

• 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 de la vista de catálogo sys.databases.

  Para obtener más información que describa cuándo se han de 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 Utilizar las estadísticas para mejorar el rendimiento de las consultas.

<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 sólo 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.

<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 de la vista de catálogo sys.databases o el examen de la propiedad IsCloseCursorsOnCommitEnabled de la función DATABASEPROPERTYEX.

• CURSOR_DEFAULT { LOCAL | GLOBAL }
  Controla si el ámbito del cursor utiliza LOCAL o GLOBAL.

  • LOCAL
    Si 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 solo 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 ejecute 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 de la vista de catálogo sys.databases o el examen de la propiedad IsLocalCursorsDefault de la función DATABASEPROPERTYEX.

<database_mirroring>

Para obtener descripciones de los argumentos, vea Creación de 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. Para obtener más información, vea Optimizar consultas con acceso a columnas datetime correlativas.

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

  La configuración actual de esta opción se puede determinar mediante el examen de la columna is_date_correlation_on de 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 Descripción del 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 hay grupos de archivos en la base de datos 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á sin conexión.

• ONLINE
  La base de datos está abierta y disponible para usarse.

• EMERGENCY
  La base de datos está marcada como READ_ONLY, el registro está deshabilitado y el acceso está limitado a los miembros de la función fija 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 de la función fija de servidor sysadmin pueden establecer una base de datos en el estado EMERGENCY.

El estado de esta opción se puede determinar mediante el examen de las columnas state y state_desc de la vista de catálogo sys.databases o el examen de 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. Para obtener más información, vea Responder a errores de restauración de SQL Server provocados por copias de seguridad dañadas.

<db_update_option>::=

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

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

• READ_WRITE
  La base de datos está disponible para las 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 solamente 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 de 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 permite que solamente se conecten a la base de datos los miembros de la función fija de base de datos db_owner y de las funciones fijas de servidor dbcreator y sysadmin, aunque no limita su número. 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 de la vista de catálogo sys.databases o el examen de 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 las cadenas 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 las cadenas de propiedad entre bases de datos, independientemente del valor de esta opción. Esta opción se establece utilizando sp_configure.

  Para establecer esta opción, se requiere el permiso 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 de la vista de catálogo sys.databases.

  Para obtener más información, vea Cadenas de propiedad.

• 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 cada vez que se adjunta la base de datos.

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

  Para establecer esta opción, se requiere el permiso 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 de la vista de catálogo sys.databases.

<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. Para obtener más información, vea Parametrización simple.

  • FORCED
    SQL Server incluye parámetros para todas las consultas de la base de datos. Para obtener más información, vea Parametrizaciones forzadas.

  La configuración actual de esta opción se puede determinar mediante el examen de la columna is_parameterization_forced de 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 de medios, utilizando copias de seguridad de registros 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 Copia de seguridad en el modelo de recuperación completa.

• BULK_LOGGED
  Proporciona una recuperación tras un error de medios 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 registradas masivamente, vea Operaciones que pueden ser registradas mínimamente. En el modelo de recuperación BULK_LOGGED, el registro de estas operaciones es mínimo. Para obtener más información, vea Copias de seguridad con el modelo de recuperación optimizado para cargas masivas de registros.

• 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 Crear copias de seguridad en el modelo de recuperación simple.

  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 Elegir el modelo de recuperación de una base de datos.

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

• TORN_PAGE_DETECTION { ON | OFF }

  • ON
    Las páginas incompletas se pueden detectar mediante Database Engine (Motor de base de datos).

  • OFF
    Las páginas incompletas no se pueden detectar mediante Database Engine (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ágina 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, ni siquiera 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:

  • En SQL Server 2005 y SQL Server 2008, el valor predeterminado es CHECKSUM. En SQL Server 2000, TORN_PAGE_DETECTION es el valor predeterminado.

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

    [!NOTA]

    En 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, 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 poner la base de datos sin conexión, bloquearla o 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 mediante el examen de la columna page_verify_option de la vista de catálogo sys.databases o el examen de la propiedad IsTornPageDetectionEnabled de la función DATABASEPROPERTYEX.

<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 las prioridades de conversación en ON u OFF. Para obtener más información acerca de la entrega de mensajes y los identificadores de Service Broker, vea Administrar identidades de Service Broker. Para obtener más información acerca de los niveles de prioridad de las conversaciones, vea Prioridades de conversación. Para obtener ejemplos que muestran cómo utilizar la opción HONOR_BROKER_PRIORITY, vea Administrar las prioridades de la conversación.

• ENABLE_BROKER
  Indica que se habilite Service Broker para la base de datos especificada. Se inicia la entrega de mensajes y el marcador 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é utilizándola 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. Se detiene la entrega de mensajes y el marcador 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 termina 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 utilizando la nueva configuración puede variar considerablemente.

  La configuración 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 la 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 empiezan a generar versiones de filas aun en el caso de que ninguna transacción utilice el aislamiento de instantánea. Una vez habilitada esta opción, las transacciones pueden especificar el nivel de aislamiento de la 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 la 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 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 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 de tempdb, dicha configuración se mantiene cada vez que la instancia del Database Engine (Motor de base de datos) se detiene y se reinicia. Si cambia la configuración de 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.

  La configuración actual de esta opción se puede determinar mediante el examen de la columna snapshot_isolation_state de 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 empiezan a generar versiones de filas aun en el caso de que 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 filas 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 cambiar a 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.

  La configuración actual de esta opción se puede determinar mediante el examen de la columna is_read_committed_snapshot_on de 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, tipo de datos del alias o tipo definido por el usuario CLR para los que no se ha definido explícitamente la nulabilidad 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 de la vista de catálogo sys.databases o el examen de 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 de la vista de catálogo sys.databases o el examen de 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.

  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
  Se recomienda que ANSI_PADDING siempre sea ON. ANSI_PADDING también debe estar en ON al crear o tratar índices en columnas calculadas o vistas indizadas.

  El estado de esta opción se puede determinar mediante el examen de la columna is_ansi_padding_on de la vista de catálogo sys.databases o el examen de 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 entre 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 entre 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 de la vista de catálogo sys.databases o el examen de 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 de la vista de catálogo sys.databases o el examen de la propiedad IsArithmeticAbortEnabled de la función DATABASEPROPERTYEX.

• COMPATIBILITY_LEVEL { 80 | 90 | 100 }
  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 de la vista de catálogo sys.databases o el examen de 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 delimitados (motor 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 de la vista de catálogo sys.databases o el examen de 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 de la vista de catálogo sys.databases o el examen de 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 recursión indirecta de desencadenadores AFTER, la opción de servidor de desencadenadores anidados se debe establecer en 0 con sp_configure.

  [!NOTA]

  Cuando RECURSIVE_TRIGGERS se establece en OFF solamente se impide la recursión directa. Para deshabilitar la recursión indirecta, también debe establecer 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 de la vista de catálogo sys.databases o el examen de 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 Notas.

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

Notas

Configurar opciones

Para recuperar la configuración actual de las opciones de base de datos, utilice la vista de catálogo sys.databases o DATABASEPROPERTYEX. Para obtener una lista de los valores predeterminados asignados a la base de datos durante su creación, vea Configurar las opciones de la base de datos.

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

Categoría de opciones	Se puede especificar con otras opciones	Puede utilizar la cláusula WITH <termination>
<db_state_option>	Sí	Sí
<db_user_access_option>	Sí	Sí
<db_update_option>	Sí	Sí
<external_access_option>	Sí	No
<cursor_option>	Sí	No
<auto_option>	Sí	No
<sql_option>	Sí	No
<recovery_option>	Sí	No
<database_mirroring_option>	No	No
ALLOW_SNAPSHOT_ISOLATION	No	No
READ_COMMITTED_SNAPSHOT	No	Sí
<service_broker_option>	Sí	No
DATE_CORRELATION_OPTIMIZATION	Sí	Sí
<parameterization_option>	Sí	Sí
<change_tracking_option>	Sí	Sí
<db_encryption>	Sí	No

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

OFFLINE	READ_WRITE
ONLINE	MODIFY FILEGROUP DEFAULT
MODIFY_NAME	MODIFY FILEGROUP READ_WRITE
COLLATE	MODIFY FILEGROUP READ_ONLY
READ_ONLY

Al borrar la memoria caché del plan, se produce 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 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 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. 

Ejemplos

A. Configurar opciones en una base de datos

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

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

B. Establecer la base de datos en READ_ONLY

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 AdventureWorks 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 el resto de las conexiones a la base de datos de ejemplo de AdventureWorks se desconectan de inmediato.

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

C. Habilitar el aislamiento de instantánea en una base de datos

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

USE AdventureWorks;
GO
-- Check the state of the snapshot_isolation_framework
-- in the database.
SELECT name, snapshot_isolation_state,
     snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks';
GO
USE master;
GO
ALTER DATABASE AdventureWorks
    SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Check again.
SELECT name, snapshot_isolation_state,
     snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks';
GO

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

name            snapshot_isolation_state  description
--------------- ------------------------  -----------
AdventureWorks  1                         ON

D. Habilitar, modificar y deshabilitar el seguimiento de cambios

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

ALTER DATABASE AdventureWorks
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 AdventureWorks
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 AdventureWorks.

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = OFF;

Historial de cambios para el documento

Contenido actualizado
Se han revisado las descripciones de AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS y AUTO_UPDATE_STATISTICS_ASYNC para mejorar la exactitud.

Vea también

Referencia

CREATE DATABASE (Transact-SQL)

DATABASEPROPERTYEX (Transact-SQL)

DROP DATABASE (Transact-SQL)

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

sp_configure (Transact-SQL)

sys.databases (Transact-SQL)

sys.data_spaces (Transact-SQL)

Conceptos

Utilizar las estadísticas para mejorar el rendimiento de las consultas

Configuración y administración del seguimiento de cambios

Habilitar niveles de aislamiento basado en el control de versiones de filas