ALTER DATABASE (Transact-SQL)

Actualizado: 12 de diciembre de 2006

Modifica una base de datos o los archivos y grupos de archivos asociados a la base de datos. Agrega o quita archivos y grupos de archivos en una base de datos, cambia los atributos de una base de datos o de sus archivos y grupos de archivos, cambia la intercalación de base de datos y establece las opciones de base de datos. Las instantáneas de bases de datos no se pueden modificar. Para modificar las opciones de base de datos asociadas a la réplica, utilice sp_replicationdboption.

Convenciones de sintaxis de Transact-SQL

Sintaxis

ALTER DATABASE database_name 
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
  | <set_database_options>
  | MODIFY NAME = new_database_name 
  | COLLATE collation_name
}
[;]

<add_or_modify_files>::=
{
    ADD FILE <filespec> [ ,...n ] 
        [ TO FILEGROUP { filegroup_name } ]
  | ADD LOG FILE <filespec> [ ,...n ] 
  | REMOVE FILE logical_file_name 
  | MODIFY FILE <filespec>
}

<filespec>::= 
(
    NAME = logical_file_name  
    [ , NEWNAME = new_logical_name ] 
    [ , FILENAME = 'os_file_name' ] 
    [ , SIZE = size [ KB | MB | GB | TB ] ] 
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] 
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] 
    [ , OFFLINE ]
) 

<add_or_modify_filegroups>::=
{
    | ADD FILEGROUP filegroup_name 
    | REMOVE FILEGROUP filegroup_name 
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option> 
        | DEFAULT
        | NAME = new_filegroup_name 
        }
}
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE } 
    | { READ_ONLY | READ_WRITE }
}

<set_database_options>::=
SET 
{
    { <optionspec> [ ,...n ] [ WITH <termination> ] }
}

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

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

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

<db_update_option> ::=
    { READ_ONLY | READ_WRITE }

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

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

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

<sql_option> ::= 
{
    ANSI_NULL_DEFAULT { ON | OFF } 
  | ANSI_NULLS { ON | OFF } 
  | ANSI_PADDING { ON | OFF } 
  | ANSI_WARNINGS { ON | OFF } 
  | ARITHABORT { ON | OFF } 
  | CONCAT_NULL_YIELDS_NULL { ON | OFF } 
  | NUMERIC_ROUNDABORT { ON | OFF } 
  | QUOTED_IDENTIFIER { ON | OFF } 
  | RECURSIVE_TRIGGERS { ON | OFF } 
}

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

<database_mirroring_option> ::= 
{ <partner_option> | <witness_option> }
    <partner_option> ::=
    PARTNER { = 'partner_server' 
            | FAILOVER 
            | FORCE_SERVICE_ALLOW_DATA_LOSS
            | OFF
            | RESUME 
            | SAFETY { FULL | OFF }
            | SUSPEND 
            | TIMEOUT integer
            }
    <witness_option> ::=
    WITNESS { = 'witness_server' 
            | OFF 
            }

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

<date_correlation_optimization_option> ::=
{
    DATE_CORRELATION_OPTIMIZATION { ON | OFF }
}

<parameterization_option> ::=
{
    PARAMETERIZATION { SIMPLE | FORCED }
}

<snapshot_option> ::=
{
    ALLOW_SNAPSHOT_ISOLATION {ON | OFF }
  | READ_COMMITTED_SNAPSHOT {ON | OFF }
}
<termination> ::= 
{
    ROLLBACK AFTER integer [ SECONDS ] 
  | ROLLBACK IMMEDIATE 
  | NO_WAIT
}

Argumentos

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

• MODIFY NAME **=**new_database_name
  Cambia el nombre de la base de datos por el nombre especificado como new_database_name.

• COLLATE collation_name
  Especifica la intercalación para la base de datos. collation_name puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL. Si no se especifica, se asigna a la base de datos la intercalación de la instancia de SQL Server.

  Para obtener más información acerca de los nombres de intercalación de Windows y SQL, vea COLLATE (Transact-SQL).

<add_or_modify_files>::=

Especifica el archivo que se va a agregar, quitar o modificar.

• ADD FILE
  Agrega un archivo a la base de datos.

  • TO FILEGROUP { filegroup_name }
    Especifica el grupo de archivos al que se agrega el archivo especificado. Para mostrar los grupos de archivos actuales y qué grupo de archivos es el predeterminado, utilice la vista de catálogo sys.filegroups.

• ADD LOG FILE
  Agrega un archivo de registro a la base de datos especificada.

• REMOVE FILE logical_file_name
  Quita la descripción del archivo lógico de una instancia de SQL Server y elimina el archivo físico. El archivo no se puede quitar a menos que esté vacío.

  • logical_file_name
    Es el nombre lógico utilizado en SQL Server cuando se hace referencia al archivo.

• MODIFY FILE
  Especifica el archivo que se debe modificar. Sólo se puede cambiar una propiedad <especificaciónDeArchivo> cada vez. NAME se debe especificar siempre en <especificaciónDeArchivo> para identificar el archivo que se va a modificar. Si se especifica SIZE, el nuevo tamaño debe ser mayor que el tamaño actual del archivo.

  Para modificar el nombre lógico de un archivo de datos o de un archivo de registro, especifique el nombre del archivo lógico que se va a cambiar en la cláusula NAME y especifique el nombre lógico nuevo para el archivo en la cláusula NEWNAME. Por ejemplo:

  MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name ) 
  

  Para mover un archivo de datos o un archivo de registro a otra ubicación, especifique el nombre del archivo lógico actual en la cláusula NAME y especifique la ruta y el nombre del archivo del sistema operativo nuevos en la cláusula FILENAME. Por ejemplo:

  MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )
  

  Si mueve un catálogo de texto, especifique la ruta nueva en la cláusula FILENAME. No especifique el nombre del archivo del sistema operativo.

  Para obtener más información, vea Mover archivos de base de datos.

<filespec>::=

Controla las propiedades del archivo.

• NAME logical_file_name
  Especifica el nombre lógico del archivo.

  • logical_file_name
    Es el nombre lógico utilizado en una instancia de SQL Server al hacer referencia al archivo.

• NEWNAME new_logical_file_name
  Especifica un nombre lógico nuevo para el archivo.

  • new_logical_file_name
    Es el nombre que reemplaza el nombre del archivo lógico existente. El nombre debe ser único en la base de datos y debe cumplir las mismas reglas que los identificadores. El nombre puede ser una constante de caracteres o Unicode, un identificador regular o un identificador delimitado. Para obtener más información, vea Usar identificadores como nombres de objeto.

• FILENAME 'os_file_name'
  Especifica un nombre de archivo (físico) del sistema operativo.

  • ' os_file_name '
    Es la ruta de acceso y el nombre de archivo que el sistema operativo utiliza al crear el archivo. El archivo debe residir en el servidor donde esté instalado SQL Server. La ruta especificada debe existir antes de ejecutar la instrucción ALTER DATABASE.

    Los parámetros SIZE, MAXSIZE y FILEGROWTH no se pueden establecer si se ha especificado una ruta UNC para el archivo.

    Los archivos de datos no se pueden utilizar en los sistemas de archivos comprimidos a menos que sean archivos secundarios de sólo lectura o si la base de datos es de sólo lectura. Los archivos de registro no se pueden utilizar en sistemas de archivos comprimidos. Para obtener más información, vea Grupos de archivos de sólo lectura y compresión.

    Si el archivo se encuentra en una partición sin procesar, os_file_name sólo debe indicar la letra de la unidad de una partición sin procesar existente. En cada partición sin formato sólo se puede utilizar un archivo.

• SIZE size
  Especifica el tamaño del archivo.

  • size
    Es el tamaño del archivo.

    Cuando se especifica con ADD FILE, size es el tamaño inicial del archivo. Si se especifica con MODIFY FILE, size es el nuevo tamaño de archivo y debe ser mayor que el tamaño de archivo actual.

    Cuando no se suministra size para el archivo principal, el SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) utiliza el tamaño del archivo principal de la base de datos model. Cuando se especifica un archivo de datos secundario o un archivo de registro, pero no se especifica el argumento size para ese archivo, el Database Engine (Motor de base de datos) hace que el tamaño del archivo sea de 1 MB.

    Se pueden utilizar los sufijos KB, MB, GB y TB para especificar kilobytes, megabytes, gigabytes o terabytes. El valor predeterminado es MB. Especifique un número entero y no incluya decimales. Para especificar una fracción de un megabyte, convierta el valor a kilobytes; para ello, multiplique el número por 1024. Por ejemplo, especifique 1536 KB en vez de 1,5 MB (1,5 x 1024 = 1536).

• MAXSIZE { max_size| UNLIMITED }
  Especifica el tamaño máximo que puede alcanzar el archivo.

  • max_size
    Es el tamaño máximo del archivo. Se pueden utilizar los sufijos KB, MB, GB y TB para especificar kilobytes, megabytes, gigabytes o terabytes. El valor predeterminado es MB. Especifique un número entero y no incluya decimales. Si no se especifica max_size, el tamaño de archivo aumenta hasta que el disco esté lleno.
  • UNLIMITED
    Especifica que el tamaño del archivo aumenta hasta que el disco esté lleno. En SQL Server 2005, un archivo de registro especificado con un aumento ilimitado tiene un tamaño máximo de 2 TB y un archivo de datos tiene un tamaño máximo de 16 TB.

• FILEGROWTH growth_increment
  Especifica el aumento automático del archivo. El valor FILEGROWTH de un archivo no puede superar el valor MAXSIZE.

  • growth_increment
    Es la cantidad de espacio que se agrega al archivo cada vez que se necesita más espacio.

    El valor se puede especificar en MB, KB, GB, TB o bien como un porcentaje (%). Si se especifica un número sin los sufijos MB, KB o %, el valor predeterminado es MB. Cuando se especifica %, el incremento de tamaño es el porcentaje especificado del tamaño del archivo en el momento en que tiene lugar el incremento. El tamaño especificado se redondea al múltiplo de 64 KB más cercano.

    El valor 0 indica que el aumento automático se establece en OFF y no se permite ningún espacio adicional.

    Si no se especifica FILEGROWTH, el valor predeterminado es 1 MB para los archivos de datos y 10% para los archivos de registro, y el valor mínimo es 64 KB.

    [!NOTA] En SQL Server 2005, el incremento de tamaño predeterminado de los archivos de datos ha cambiado de 10% a 1 MB. El valor predeterminado del 10% del archivo de registro no varía.

• OFFLINE
  Establece el archivo sin conexión e impide el acceso a todos los objetos del grupo de archivos.

  Advertencia:
  Utilice esta opción sólo si el archivo está dañado y se puede restaurar. Un archivo establecido en OFFLINE sólo se puede establecer con conexión mediante la restauración del archivo a partir de una copia de seguridad. Para obtener más información acerca de cómo restaurar un solo archivo, vea RESTORE (Transact-SQL).

<add_or_modify_filegroups>::=

Agrega, modifica o quita un grupo de archivos de la base de datos.

• ADD FILEGROUP filegroup_name
  Agrega un grupo de archivos a la base de datos.

• REMOVE FILEGROUP filegroup_name
  Quita un grupo de archivos de la base de datos. El grupo de archivos no se puede quitar a menos que esté vacío. Quita todos los archivos del primer grupo de archivos. Para obtener más información, vea "REMOVE FILE logical_file_name", anteriormente en este tema.

• MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME **=**new_filegroup_name }
  Modifica el grupo de archivos al establecer el estado en READ_ONLY o READ_WRITE, lo que hace que el grupo de archivos se convierta en predeterminado para la base de datos o que se cambie el nombre del grupo de archivos.

  • <filegroup_updatability_option>
    Establece la propiedad de sólo lectura o sólo lectura y escritura para el grupo de archivos.
  • DEFAULT
    Cambia el grupo de archivos predeterminado de la base de datos a filegroup_name. Sólo un grupo de archivos de la base de datos puede ser el grupo de archivos predeterminado. Para obtener más información, vea Descripción de archivos y grupos de archivos.
  • NAME = new_filegroup_name
    Cambia el nombre del grupo de archivos a new_filegroup_name.

<filegroup_updatability_option>::=

Establece la propiedad de sólo lectura o sólo lectura y escritura para el grupo de archivos.

• READ_ONLY | READONLY
  Especifica que el grupo de archivos es de sólo lectura. No se permite la actualización de los objetos del mismo. El grupo de archivos principal no puede ser de sólo lectura. 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.

  Una base de datos de sólo lectura no permite realizar modificaciones en los datos:

  • Se omite la recuperación automática cuando se inicia el sistema.
  • No es posible reducir la base de datos.
  • No se produce ningún bloqueo en las bases de datos de sólo lectura. Esto puede acelerar el rendimiento de las consultas.

  [!NOTA] La palabra clave READONLY se quitará en una versión futura de Microsoft SQL Server. Evite el uso de READONLY en los nuevos trabajos de desarrollo y piense en modificar las aplicaciones que utilizan READONLY actualmente. Utilice READ_ONLY en su lugar.

• READ_WRITE | READWRITE
  Especifica que el grupo es READ_WRITE. Pueden realizarse actualizaciones en los objetos del grupo de archivos. 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.

  [!NOTA] La palabra clave READWRITE se quitará en una versión futura de Microsoft SQL Server. Evite el uso de READWRITE en los nuevos trabajos de desarrollo y piense en modificar las aplicaciones que utilizan READWRITE actualmente. Utilice READ_WRITE en su lugar.

El estado de estas opciones se puede determinar mediante el examen de la columna is_read_only de la vista de catálogo sys.databases o la propiedad Updateability de la función DATABASEPROPERTYEX.

<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 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 con el estado EMERGENCY. Esto puede habilitar el acceso de sólo lectura del administrador del sistema a la base de datos. Sólo 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 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. Una base de datos puede encontrarse 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_user_access_option> ::=

Controla el acceso del usuario a la base de datos.

• SINGLE_USER
  Especifica que sólo 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 desconectan de la base de datos especificada. Para omitir este comportamiento, vea la cláusula WITH <termination>.

  La base de datos permanece en modo SINGLE_USER incluso si cierra la sesión el usuario que estableció la opción. En este punto, un usuario distinto, pero sólo 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 toma 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.
  3. Si hay trabajos activos, permita que se completen o termínelos manualmente con KILL STATS JOB.

• RESTRICTED_USER
  RESTRICTED_USER permite conectarse a la base de datos sólo a los miembros de la función fija de base de datos db_owner y a los de las funciones fijas de servidor dbcreator y sysadmin, pero 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 la propiedad UserAccess de la función DATABASEPROPERTYEX.

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

<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 reconoce esta configuración si la opción del servidor cross db ownership chaining es 0 (OFF). Si cross db ownership chaining es 1 (ON), todas las bases de datos de usuario pueden participar en los encadenamientos de propiedad entre bases de datos con independencia del valor de esta opción. Esta opción se configura con sp_configure.

  Para establecer esta opción, debe pertenecer a la función fija de servidor sysadmin. 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 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 configurar esta opción, se requiere la pertenencia a la función fija de servidor sysadmin.

  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.

<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; al revertir una transacción 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, anula 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 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 proceso por lotes, procedimiento almacenado o desencadenador en el que se creó el cursor. El nombre del cursor sólo es válido dentro de este ámbito. Es posible hacer referencia al cursor mediante variables de cursor locales del proceso por lotes, procedimiento almacenado, desencadenador o parámetro OUTPUT del procedimiento almacenado. La asignación del cursor se cancela implícitamente cuando el proceso por lotes, el procedimiento almacenado o el desencadenador terminan, a menos que haya sido devuelto en un parámetro OUTPUT. Si el cursor se devuelve en un parámetro OUTPUT, se cancela la asignación del cursor 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 proceso por lotes que se ejecute durante la conexión.

  La asignación del cursor se cancela implícitamente sólo 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 la propiedad IsLocalCursorsDefault de la función DATABASEPROPERTYEX.

<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 vuelve a abrir hasta que un usuario intenta utilizar la base de datos la próxima vez que se reinicia 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 si tiene acceso a la base de datos una aplicación que establece e interrumpe repetidamente la conexión al Database Engine (Motor de base de datos). En SQL Server 2005, el proceso AUTO_CLOSE es asincrónico; la apertura y cierre repetidos de la base de datos 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 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 caché del plan para la instancia de SQL Server. Al borrar la 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 el Service Pack 2 de SQL Server 2005, 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ía la caché dentro de ese intervalo de tiempo.

• AUTO_CREATE_STATISTICS { ON | OFF }

  • ON
    Las estadísticas que falten en una consulta para su optimización se generan automáticamente durante la optimización de las consultas.

    Agregar estadísticas mejora el rendimiento de las consultas, ya que el optimizador de consultas de SQL Server puede determinar mejor la forma de evaluar una consulta. Si no se utilizan estadísticas, el Database Engine (Motor de base de datos) las elimina automáticamente. Cuando el valor es OFF, las estadísticas no se crean automáticamente; en su lugar, se pueden crear manualmente. Para obtener más información, vea Estadísticas de índice.

  • OFF
    Las estadísticas se deben crear manualmente.

  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 la propiedad IsAutoUpdateStatistics de la función DATABASEPROPERTYEX.

  [!NOTA] El optimizador de consultas trata todas las tablas del sistema internas como si el valor de AUTO_CREATE_STATISTICS fuera ON con independencia del valor actual. Estas tablas incluyen tablas base del sistema, índices XML, índices de texto, tablas de cola de Service Broker y tablas de notificaciones de consulta.

• 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 sólo si se establece en SIMPLE el modelo de recuperación de la base de datos 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 automáticamente durante las comprobaciones periódicas de 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 del 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 de 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 la propiedad IsAutoShrink de la función DATABASEPROPERTYEX. .

• AUTO_UPDATE_STATISTICS { ON | OFF }

  • ON
    Las estadísticas obsoletas requeridas por una consulta para su optimización se actualizan automáticamente durante la optimización de las consultas.
  • OFF
    Las estadísticas se deben actualizar manualmente.

  [!NOTA] La instrucción UPDATE STATISTICS vuelve a habilitar la actualización estadística automática en la tabla o la vista de destino a menos que se especifique la cláusula NORECOMPUTE.

  [!NOTA] El optimizador de consultas trata todas las tablas del sistema internas como si el valor de AUTO_UPDATE_STATISTICS fuera ON con independencia del valor actual. Estas tablas incluyen tablas base del sistema, índices XML, índices de texto, tablas de cola de Service Broker y tablas de notificaciones de consulta.

  Para obtener más información, vea Estadísticas de índice.

• AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

  • ON
    Las consultas que inician una actualización automática de las estadísticas obsoletas no esperan a que las estadísticas se actualicen antes de la compilación. Las consultas posteriores utilizan las estadísticas actualizadas si están disponibles.
  • OFF
    Las consultas que inician una actualización automática de las estadísticas obsoletas esperan hasta que las estadísticas actualizadas se puedan utilizar en el plan de optimización de consultas.

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

  Para obtener más información, vea Estadísticas de índice.

<sql_option>::=

Controla las opciones de compatibilidad 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, el tipo de datos de alias o el tipo definido por el usuario CLR para los que la capacidad de aceptar valores NULL no se ha definido 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, anula 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 al establecer 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 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.

  La configuración del nivel de conexión, establecida mediante la instrucción SET, anula 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 establecer 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 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_ansi_nulls_on de 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 sólo afecta a la definición de las columnas nuevas.

  char(n Las columnas ) 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, anula 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 establecer 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 ser ON al crear o manipular í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 la propiedad IsAnsiPaddingEnabled de la función DATABASEPROPERTYEX.

• ANSI_WARNINGS { ON | OFF }

  • ON
    Se emiten mensajes de error o advertencias cuando tienen lugar 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, anula 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 establecer 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 la propiedad IsAnsiWarningsEnabled de la función DATABASEPROPERTYEX.

• ARITHABORT { ON | OFF }

  • ON
    Cancela una consulta cuando se produce un error de desbordamiento o 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 la propiedad IsArithmeticAbortEnabled de la función DATABASEPROPERTYEX.

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

  La configuración del nivel de conexión, establecida mediante la instrucción SET, anula 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 establecer 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 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, anula 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 establecer 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 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 también 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 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 los desencadenadores AFTER, la opción de servidor de desencadenadores anidados se debe establecer en 0 con sp_configure.

  [!NOTA] Cuando RECURSIVE_TRIGGERS es OFF sólo se impide la recursividad directa. Para deshabilitar la recursividad 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 la propiedad IsRecursiveTriggersEnabled de la función DATABASEPROPERTYEX.

<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 después de un error del medio con 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 Copia de seguridad en el modelo de recuperación completa.

• BULK_LOGGED
  Proporciona una restauració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 registradas masivamente, vea Operaciones 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 por medio de registros de operaciones masivas.

• SIMPLE
  Se proporciona una estrategia de copia de seguridad sencilla que utiliza un espacio de registro mínimo. Es posible volver a utilizar el espacio de registro automáticamente cuando ya no se necesite para recuperar 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 pérdida de datos si se daña un archivo de datos. Se pierden todos los cambios desde la copia de seguridad de base de datos más reciente o la copia de seguridad diferencial de la base de datos y se deben volver a incluir manualmente.

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 la propiedad Recovery de la función DATABASEPROPERTYEX.

• TORN_PAGE_DETECTION { ON | OFF }

  • ON
    Las páginas incompletas se pueden detectar mediante el Database Engine (Motor de base de datos).
  • OFF
    Las páginas incompletas no se pueden detectar mediante el 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 Microsoft SQL Server. Evite utilizar esta estructura de sintaxis en nuevos trabajos de desarrollo y tenga previsto modificar las aplicaciones que la utilizan actualmente. 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 fallos del hardware del disco producidos 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 un mensaje de error 824 (indica un error de la suma de comprobación) tanto para el registro de errores de SQL Server 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, se deben 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 un bit específico por cada sector de 512 bytes en la página de base de datos de 8 kilobytes (KB) y lo 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 incompletos 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 sólo 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 sucesos 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 base datos no generan un valor CHECKSUM o TORN_PAGE_DETECTION. SQL Server no comprueba ninguna suma de comprobación o página rasgada durante una lectura incluso si hay un valor CHECKSUM o TORN_PAGE_DETECTION en el encabezado de página.

  Tenga en cuenta los siguientes puntos importantes al utilizar la opción PAGE_VERIFY:

  • En SQL Server 2005, 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, se conserva el valor de PAGE_VERIFY (NONE o TORN_PAGE_DETECTION). Se recomienda utilizar CHECKSUM.
  • TORN_PAGE_DETECTION puede utilizar menos recursos, pero proporciona un subconjunto mínimo de la protección de CHECKSUM.
  • PAGE_VERIFY se puede configurar sin desconectar la base de datos, 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 página rasgada o suma de comprobación, puede realizar una recuperación mediante la restauración de los datos o una nueva generación del índice si el error se limita sólo a las páginas de índices. Si detecta un error de suma de comprobación, para determinar el tipo de página o páginas de base de datos afectadas, ejecute DBCC CHECKDB. 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 resuelve 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 reintenta 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 fallan, 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 la propiedad IsTornPageDetectionEnabled de la función DATABASEPROPERTYEX.

<database_mirroring_option>::=

Controla la creación de reflejo de la base de datos. Los valores especificados con las opciones de creación de reflejo de la base de datos se aplican a ambas copias de la base de datos y a la sesión de creación de reflejo de la base de datos en su conjunto. Sólo se permite una opción <database_mirroring_option> por cada instrucción ALTER DATABASE: { SET PARTNER <partner_option> | SET WITNESS <witness_option>}.

Importante:
Un comando SET PARTNER o SET WITNESS puede completarse correctamente cuando se escribe y provocar un error más adelante.

[!NOTA] Es recomendable configurar la creación de reflejo de la base de datos durante las horas de menor actividad, ya que la configuración puede afectar al rendimiento.

Para obtener información acerca de la creación de reflejo de la base de datos, vea Creación de reflejo de la base de datos.

• PARTNER <partner_option>
  Controla las propiedades de la base de datos que definen los asociados de conmutación por error de una sesión de creación de reflejo de la base de datos y su comportamiento. Algunas opciones SET PARTNER se pueden establecer en cualquier asociado; otras opciones se limitan al servidor principal o al servidor reflejado. Para obtener más información, vea cada opción PARTNER especificada a continuación. Una cláusula SET PARTNER afecta a ambas copias de la base de datos con independencia del asociado en que se especifica.

  Para ejecutar una instrucción SET PARTNER, el valor de STATE de los extremos de ambos asociados debe ser STARTED. Además, debe tener en cuenta que el valor de ROLE del extremo de la creación de reflejo de la base de datos de cada instancia del servidor asociado se debe establecer en PARTNER o ALL. Para obtener información acerca de cómo especificar un extremo, vea Cómo crear un extremo de reflejo para la autenticación de Windows (Transact-SQL). Para conocer la función y el estado del extremo de la creación de reflejo de la base de datos de una instancia del servidor, debe utilizar la siguiente instrucción Transact-SQL en dicha instancia:

  SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints
  

  <partner_option> ::=

  [!NOTA] Sólo se permite una opción <partner_option> por cada cláusula SET PARTNER.

  • 'partner_server'
    Especifica la dirección de red del servidor de una instancia de SQL Server para actuar como un asociado de conmutación por error en la nueva sesión de creación de reflejo de la base de datos. Cada sesión requiere dos asociados: un asociado empieza como servidor principal y el otro como servidor reflejado. Se recomienda que estos asociados residan en equipos distintos.

    Esta opción se especifica una vez por sesión en cada asociado. El inicio de la creación de reflejo de la base de datos requiere dos instrucciones ALTER DATABASE database SET PARTNER ='partner_server'. El orden es relevante. En primer lugar, conéctese al servidor reflejado y especifique la instancia del servidor principal como partner_server (SET PARTNER ='principal_server'). A continuación, establezca la conexión con el servidor principal y especifique la instancia del servidor reflejado como partner_server (SET PARTNER ='mirror_server'); de este modo se inicia una sesión de creación de reflejo de la base de datos entre estos dos asociados. Para obtener más información, vea Configurar la creación de reflejo de la base de datos.

    El valor de partner_server es una dirección de red de servidor. Tiene la siguiente sintaxis:

    TCP**://<direcciónDeSistema>:**<puerto>

    donde

    • <direcciónDeSistema> es una cadena, como un nombre de sistema, un nombre de dominio completo o una dirección IP, que identifica sin ambigüedad el sistema de destino.
    • <puerto> es un número de puerto que está asociado al extremo de la creación de reflejo de la instancia del servidor asociado.

    Para obtener más información, vea Especificar una dirección de red de servidor (creación de reflejo de la base de datos).

    En el siguiente ejemplo se muestra la cláusula SET PARTNER ='partner_server':

    SET PARTNER = 'TCP://MYSERVER.mydomain.Adventure-Works.com:7777'
    

    Importante:

    Si se configura una sesión con la instrucción ALTER DATABASE en lugar de con SQL Server Management Studio, la sesión se configura con la seguridad de transacciones completa de forma predeterminada (SAFETY se establece en FULL) y se ejecuta en el modo de alta seguridad con conmutación por error automática. Para permitir la conmutación por error automática, se debe configurar un testigo; para ejecutar en modo de alto rendimiento, se debe desactivar la seguridad de transacciones (SAFETY OFF).

  • FAILOVER
    Conmuta manualmente el servidor principal al servidor reflejado. Sólo puede especificar FAILOVER en el servidor principal. Esta opción es válida sólo si la configuración de SAFETY es FULL (valor predeterminado).

    La opción FAILOVER requiere master como contexto de base de datos.

    Para obtener más información, vea Conmutación por error manual.

  • FORCE_SERVICE_ALLOW_DATA_LOSS
    Fuerza la aplicación del servicio de base de datos a la base de datos reflejada si se produce un error en el servidor principal con la base de datos en un estado no sincronizado o en un estado sincronizado cuando no se produce la conmutación por error automática.

    Se recomienda encarecidamente que el servicio sólo se fuerce si el servidor principal no se está ejecutando. En caso contrario, algunos clientes pueden seguir teniendo acceso a la base de datos principal original en lugar de la base de datos principal nueva.

    FORCE_SERVICE_ALLOW_DATA_LOSS está disponible sólo en el servidor reflejado y si se cumplen todas las condiciones siguientes:

    • El servidor principal está inactivo.
    • WITNESS se establece en OFF o el testigo está conectado al servidor reflejado.

    Fuerce el servicio sólo si es aceptable el riesgo de perder datos para restaurar el servicio en la base de datos inmediatamente. Para obtener información acerca de cómo forzar el servicio, vea Operación asincrónica de creación de reflejo de la base de datos (Modo de alto rendimiento).

    Al forzar el servicio se suspende la sesión, lo que conserva temporalmente todos los datos de la base de datos principal original. Una vez que el servidor principal original esté en servicio y pueda comunicarse con el nuevo servidor principal, el administrador de la base de datos podrá reanudar el servicio. Cuando se reanuda la sesión, se pierden los registros no enviados y las actualizaciones correspondientes.

    Para obtener más información sobre los riesgos de forzar la aplicación del servicio, vea Servicio forzado (con posible pérdida de datos).

  • OFF
    Quita una sesión de creación de reflejo de la base de datos así como la creación de reflejos de la base de datos. Puede especificar OFF en cualquier asociado. Para obtener más información acerca del impacto de quitar la creación de reflejos de la base de datos, vea Quitar la creación de reflejo de la base de datos.
  • RESUME
    Reanuda la sesión de creación de reflejo de la base de datos suspendida. Sólo puede especificar RESUME en el servidor principal.

  • SAFETY { FULL | OFF }
    Establece el nivel de seguridad de las transacciones. Sólo puede especificar SAFETY en el servidor principal.

    El valor predeterminado es FULL. Si se utiliza seguridad total, la sesión de creación de reflejo de la base de datos se ejecuta de forma sincrónica (en modo de alta seguridad). Si se establece SAFETY en OFF, la sesión de creación de reflejos de la base de datos se ejecuta de forma asincrónica (en modo de alto rendimiento).

    El comportamiento de modo de alta seguridad depende en parte del testigo, como se indica a continuación:

    • Si la seguridad se establece en FULL y se establece el testigo para la sesión, dicha sesión se ejecuta en el modo de alta seguridad con conmutación por error automática. Si se interrumpe la conexión con el servidor principal, se produce automáticamente la conmutación por error de la sesión si la base de datos está sincronizada y las instancias del servidor reflejado y el servidor testigo siguen conectadas entre sí (es decir, tienen quórum). Para obtener más información, vea Quórum: cómo un testigo afecta a la disponibilidad de la base de datos.
      Si se establece un testigo para la sesión, pero está desconectado actualmente, la pérdida del servidor reflejado causa el bloqueo del servidor principal.
    • Si la seguridad se establece en FULL y el testigo en OFF, dicha sesión se ejecuta en el modo de alta seguridad sin conmutación por error automática. Si la instancia del servidor reflejado se bloquea, la instancia del servidor principal no se ve afectada. Si la instancia del servidor principal se bloquea, se puede forzar el servicio (con una posible pérdida de datos) en la instancia del servidor reflejado.

    Si SAFETY se establece en OFF, la sesión se ejecuta en el modo de alto rendimiento y no se admite la conmutación por error automática o manual. Sin embargo, los problemas del servidor reflejado no afectan al servidor principal y, si la instancia de éste se bloquea, puede forzar el servicio si es necesario (con una posible pérdida de datos) a la instancia del servidor reflejado, siempre que WITNESS esté establecido en OFF o el testigo esté conectado actualmente al reflejo. Para obtener más información sobre cómo forzar la aplicación del servicio, vea "FORCE_SERVICE_ALLOW_DATA_LOSS" en un apartado anterior de esta sección.

    Importante:
    El modo de alto rendimiento no se ha diseñado para que utilice un testigo. Sin embargo, siempre que se establece SAFETY en OFF, es muy recomendable asegurarse de que WITNESS está establecido en OFF.

    Para obtener más información, vea Configuración de Transact-SQL y modos de funcionamiento de la creación de reflejo de la base de datos.

  • SUSPEND
    Suspende la sesión de creación de reflejo de la base de datos.

    Puede especificar SUSPEND en cualquier asociado.

  • TIMEOUT integer
    Especifica el período de espera en segundos. El período de espera es el tiempo máximo durante el que una instancia de servidor espera hasta recibir un mensaje PING de otra instancia en la sesión de creación de reflejo de la base de datos antes de considerar que la otra instancia está desconectada.

    Sólo puede especificar la opción TIMEOUT en el servidor principal. Si no especifica esta opción, el período de tiempo equivale a 10 segundos de forma predeterminada. Si especifica 5 o superior, el tiempo de espera se establece en el número de segundos especificado. Si especifica un tiempo de espera de 0 a 4 segundos, dicho tiempo de espera se establece automáticamente en 5 segundos.

    Importante:
    Es recomendable que mantenga el período de espera en 10 segundos o más. Si establece el valor en menos de 10 segundos, existe la posibilidad de que un sistema sobrecargado no reciba los PING y declare un error falso.

    Para obtener más información, vea Posibles errores durante la creación de reflejo de la base de datos.

• WITNESS <witness_option>
  Controla las propiedades de base de datos que definen un testigo de creación de reflejo de la base de datos. La cláusula SET WITNESS afecta a ambas copias de la base de datos, pero sólo puede especificar SET WITNESS en el servidor principal. Si se establece un testigo para una sesión, se requiere quórum para servir a la base de datos, independientemente de la configuración de SAFETY; para obtener más información, vea Quórum: cómo un testigo afecta a la disponibilidad de la base de datos.

  Se recomienda que el testigo y los asociados de conmutación por error residan en equipos distintos. Para obtener información sobre el testigo, vea Testigo de creación de reflejo de la base de datos. Para obtener información sobre la conmutación por error automática, vea Conmutación por error automática.

  Para ejecutar una instrucción SET WITNESS, el valor de STATE de los extremos de las instancias del servidor principal y el servidor testigo se debe establecer en STARTED. Además, debe tener en cuenta que el valor de ROLE del extremo de la creación de reflejo de la base de datos de una instancia del servidor testigo se debe establecer en WITNESS o ALL. Para obtener información acerca de cómo especificar un extremo, vea Extremo de creación de reflejo de base de datos.

  Para conocer la función y el estado del extremo de la creación de reflejo de la base de datos de una instancia del servidor, debe utilizar la siguiente instrucción Transact-SQL en dicha instancia:

  SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints
  

  [!NOTA] Las propiedades de base de datos no se pueden establecer en el testigo.

  <witness_option> ::=

  [!NOTA] Sólo se permite una opción <witness_option> por cada cláusula SET WITNESS.

  • 'witness_server'
    Especifica una instancia del Database Engine (Motor de base de datos) para que actúe como el servidor testigo para una sesión de creación de reflejo de la base de datos. Sólo puede especificar instrucciones SET WITNESS en el servidor principal.

    En una instrucción SET WITNESS ='witness_server', la sintaxis de witness_server equivale a la sintaxis de partner_server.

  • OFF
    Quita el testigo de la sesión de creación de reflejo de la base de datos. Si se establece el testigo en OFF, se deshabilita la conmutación por error automática. Si la base de datos se establece en FULL SAFETY y el testigo se establece en OFF, un error del servidor reflejado hace que el servidor principal anule la disponibilidad de la base de datos.

<service_broker_option>::=

Controla las opciones de Service Broker.

• ENABLE_BROKER
  Indica que se habilite Service Broker para la base de datos especificada. El indicador is_broker_enabled se establece en true en la vista de catálogo sys.databases y se inicia la entrega de mensajes.

  [!NOTA] La habilitación de SQL Server Service Broker en una base de datos requiere un bloqueo de base de datos. Para habilitar Service Broker en la base de datos msdb, detenga en primer lugar el Agente SQL Server, para que Service Broker pueda conseguir el bloqueo necesario.

• DISABLE_BROKER
  Indica que se deshabilite Service Broker para la base de datos especificada. El indicador is_broker_enabled se establece en false en la vista de catálogo sys.databases y se interrumpe la entrega de mensajes.

• NEW_BROKER
  Especifica que la base de datos debe recibir un identificador de broker nuevo. Dado que la base de datos se considera 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.

• ERROR_BROKER_CONVERSATIONS
  Especifica que las conversaciones de la base de datos deben recibir un mensaje de error cuando se adjunta la base de datos. Esto permite que las aplicaciones realicen una limpieza regular de las conversaciones existentes.

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

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

<snapshot_option>::=

Determina el nivel de aislamiento de las transacciones.

• ALLOW_SNAPSHOT_ISOLATION { ON | OFF }

  • ON
    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 pueden ver una instantánea de los datos al salir al comienzo 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 se debe establecer 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
    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 el control al autor de la llamada de inmediato. 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 mantiene si la base de datos se establece después 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 Database Engine (Motor de base de datos) se detiene y se reinicia. Si cambia la configuración para el modelo, dicha configuración se convierte en la predeterminada para todas las bases de datos nuevas que se crean 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
    Las transacciones que especifican el nivel de aislamiento de lectura confirmada utilizan el control de versiones de fila en lugar del bloqueo. 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
    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 conexión 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 master, tempdb o msdb. Si cambia la configuración para model, dicha configuración se convierte en predeterminada para todas las 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.

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. Sólo 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 <terminación> WITH. Para obtener más información, vea la tabla de "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 inmediatamente.

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

Notas

Para quitar una base de datos, utilice DROP DATABASE.

Para cambiar el nombre de una base de datos, utilice la opción MODIFY NAME = new_database_name con ALTER DATABASE.

Para reducir el tamaño de una base de datos, utilice DBCC SHRINKDATABASE.

No se puede agregar o quitar un archivo mientras se está ejecutando una instrucción BACKUP.

Para cada base de datos se puede especificar un máximo de 32.767 archivos y 32.767 grupos de archivos.

La instrucción ALTER DATABASE se debe ejecutar en el modo de confirmación automática (modo de administración de transacciones predeterminado) y no se permite en una transacción explícita o implícita. Para obtener más información, vea Transacciones de confirmación automática.

En SQL Server 2005, el estado de un archivo de base de datos (por ejemplo, con conexión o sin conexión) se mantiene con independencia del estado de la base de datos. Para obtener más información, vea Estados de los archivos. El estado de los archivos de un grupo de archivos determina la disponibilidad de todo el grupo de archivos. Para que un grupo de archivos esté disponible, todos los archivos del grupo deben tener conexión. Si un grupo de archivos no tiene conexión, todos los intentos de acceso al grupo de archivos por parte de una instrucción SQL generan un error. Al generar un plan de consultas para las instrucciones SELECT, el optimizador de consultas evita los índices no agrupados y las vistas indizadas que residen en los grupos de archivos sin conexión. Esto permite que las instrucciones se ejecuten correctamente. No obstante, si el grupo de archivos sin conexión contiene el montón o el índice agrupado de la tabla de destino, las instrucciones SELECT generan un error. Además, todas las instrucciones INSERT, UPDATE o DELETE que modifican una tabla con un índice en un grupo de archivos sin conexión generan un error.

Si una base de datos se encuentra en estado RESTORING, se producirán errores en la mayoría de las instrucciones ALTER DATABASE. La excepción es el establecimiento de opciones de creación de reflejo de base de datos. Una base de datos puede encontrarse 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.

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 las bases de datos que se acaban de crear, 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> o 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í

La 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 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 el Service Pack 2 de SQL Server 2005, 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ía la caché dentro de ese intervalo de tiempo.

Mover archivos

En SQL Server 2005, puede mover los archivos de sistema, de datos definidos por el usuario o de registro si especifica la ubicación nueva en FILENAME. Esto puede resultar útil en los siguientes escenarios:

• Recuperación de errores. Por ejemplo, la base de datos está en modo de sospecha o cerrada debido a un error del hardware.
• Reubicación planeada.
• Reubicación para mantenimiento de disco programado.

Para obtener más información, vea Mover archivos de base de datos.

Inicializar archivos

De forma predeterminada, los archivos de datos y registro se inicializan mediante el relleno de los archivos con ceros al realizar una de las siguientes operaciones:

• Crear una base de datos
• Agregar archivos a una base de datos existente
• Aumentar el tamaño de un archivo existente
• Restaurar una base de datos o un grupo de archivos

En SQL Server 2005, los archivos de datos se pueden inicializar de forma instantánea. Esto permite la ejecución rápida de estas operaciones con los archivos. Para obtener más información, vea Inicialización de archivos de base de datos.

Cambiar la intercalación de la base de datos

Antes de aplicar otra intercalación a una base de datos, asegúrese de que se cumplen las siguientes condiciones:

1. Es el único usuario que utiliza actualmente la base de datos.
2. Ningún objeto enlazado a un esquema depende de la intercalación de la base de datos.
   Si los siguientes objetos, los cuales dependen de la intercalación de la base de datos, existen en la base de datos, la instrucción ALTER DATABASE database_name COLLATE genera un error. SQL Server devuelve un mensaje de error para cada objeto que bloquee la acción ALTER:
   • Vistas y funciones definidas por el usuario creadas con SCHEMABINDING
   • Columnas calculadas
   • Restricciones CHECK
   • Funciones de valores de tabla que devuelven tablas con columnas de caracteres con intercalaciones heredadas de la intercalación predeterminada de la base de datos
3. Cambiar la intercalación de la base de datos no crea duplicados entre los nombres del sistema para los objetos de base de datos.
   Los siguientes espacios de nombres pueden provocar errores en el cambio de la intercalación de la base de datos si se producen nombres duplicados en la intercalación cambiada:
   • Nombres de objetos, como un procedimiento, tabla, desencadenador o vista
   • Nombres de esquemas
   • Entidades de seguridad, como un grupo, función o usuario
   • Nombres de tipo escalar, como los tipos definidos por el usuario y por el sistema
   • Nombres de catálogos de texto
   • Nombres de columnas o parámetros en un objeto
   • Nombres de índices en una tabla
     Los nombres duplicados resultantes de la nueva intercalación provocarán que la acción de cambio no se ejecute correctamente y SQL Server devolverá un mensaje de error que especifica el espacio de nombres donde se ha encontrado el duplicado.

Ver información de base de datos

Puede utilizar vistas de catálogo, funciones del sistema y procedimientos almacenados del sistema para devolver información sobre las bases de datos, los archivos y los grupos de archivos. Para obtener más información, vea Ver los metadatos de una base de datos.

Permisos

Requiere el permiso ALTER en la base de datos.

Ejemplos

A. Agregar un archivo a una base de datos

En el siguiente ejemplo se agrega un archivo de datos de 5 MB a la base de datos AdventureWorks.

USE master;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks 
ADD FILE 
(
    NAME = Test1dat2,
    FILENAME = '''+ @data_path + 't1dat2.ndf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)'
);
GO

B. Agregar a una base de datos un grupo de archivos con dos archivos

En el siguiente ejemplo se crea el grupo de archivos Test1FG1 en la base de datos AdventureWorks y se agregan dos archivos de 5 MB al grupo de archivos.

USE master
GO
ALTER DATABASE AdventureWorks
ADD FILEGROUP Test1FG1;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks 
ADD FILE 
(
    NAME = test1dat3,
    FILENAME = '''+ @data_path + 't1dat3.ndf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = '''+ @data_path + 't1dat4.ndf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1'
);
GO

C. Agregar dos archivos de registro a una base de datos

En el siguiente ejemplo se agregan dos archivos de registro de 5 MB a la base de datos AdventureWorks.

USE master;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks 
ADD LOG FILE 
(
    NAME = test1log2,
    FILENAME = '''+ @data_path + 'test2log.ldf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1log3,
    FILENAME = '''+ @data_path + 'test3log.ldf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)'
);
GO

D. Quitar un archivo de una base de datos

En el siguiente ejemplo se quita uno de los archivos agregados en el ejemplo B.

USE master;
GO
ALTER DATABASE AdventureWorks
REMOVE FILE test1dat4;
GO

E. Modificar un archivo

En el siguiente ejemplo aumenta el tamaño de uno de los archivos agregados en el ejemplo B.

USE master;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILE
    (NAME = test1dat3,
    SIZE = 20MB);
GO

F. Mover un archivo a otra ubicación

En el siguiente ejemplo se mueve el archivo Test1dat2 creado en el ejemplo A a otro directorio.

[!NOTA] Debe mover físicamente el archivo al directorio nuevo antes de ejecutar este ejemplo. A continuación, detenga e inicie la instancia de SQL Server o establezca la base de datos AdventureWorks en OFFLINE y después en ONLINE para implementar el cambio.

USE master;
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(
    NAME = Test1dat2,
    FILENAME = N'c:\t1dat2.ndf'
);
GO

G. Mover tempdb a otra ubicación

En el siguiente ejemplo se mueve tempdb de su ubicación actual en el disco a otra ubicación del disco. Puesto que tempdb se vuelve a crear cada vez que se inicia el servicio MSSQLSERVER, no es necesario mover físicamente los archivos de datos y de registro. Los archivos se crean cuando se reinicia el servicio en el paso 3. Hasta que se reinicia, tempdb seguirá funcionando en su ubicación existente.

1. Determine los nombres de los archivos lógicos de la base de datos tempdb y su ubicación actual en el disco.

   SELECT name, physical_name
   FROM sys.master_files
   WHERE database_id = DB_ID('tempdb');
   GO
   

2. Cambie la ubicación de cada archivo con ALTER DATABASE.

   USE master;
   GO
   ALTER DATABASE tempdb 
   MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
   GO
   ALTER DATABASE  tempdb 
   MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
   GO
   

3. Detenga y reinicie la instancia de SQL Server.

4. Compruebe el cambio de los archivos.

   SELECT name, physical_name
   FROM sys.master_files
   WHERE database_id = DB_ID('tempdb');
   

5. Elimine los archivos tempdb.mdf y templog.ldf de su ubicación original.

H. Establecer un grupo de archivos como predeterminado

En el siguiente ejemplo, el grupo de archivos Test1FG1 creado en el ejemplo B se establece como predeterminado. A continuación, el grupo de archivos predeterminado se restablece al grupo de archivos PRIMARY. Tenga en cuenta que PRIMARY se debe delimitar con corchetes o comillas.

USE master;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

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

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

J. Establecer la base de datos en READ_ONLY

El cambio 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 tener 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 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

K. Habilitar el asilamiento 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

L. Crear una sesión de creación de reflejo de la base de datos con un testigo

La configuración de la creación de reflejo de la base de datos con un testigo requiere configurar la seguridad y preparar la base de datos reflejada además de utilizar ALTER DATABASE para configurar los asociados. Para obtener un ejemplo del proceso de configuración completo, vea Configurar la creación de reflejo de la base de datos.

M. Conmutación por error manual en una sesión de creación de reflejo de la base de datos

La conmutación por error manual se puede iniciar desde cualquier asociado de creación de reflejo de la base de datos. Antes de llevar a cabo la conmutación por error, debe comprobar si el servidor principal actual es realmente el servidor principal. Por ejemplo, para la base de datos AdventureWorks, ejecute la siguiente consulta en la instancia del servidor que crea que es el servidor principal actual:

SELECT db.name, m.mirroring_role_desc 
FROM sys.database_mirroring m 
JOIN sys.databases db
ON db.database_id = m.database_id
WHERE db.name = N'AdventureWorks' 
GO

Si la instancia del servidor es la entidad de seguridad, el valor de mirroring_role_desc es Principal. Si esta instancia del servidor se corresponde con el servidor reflejado, la instrucción SELECT debe devolver Mirror.

En el siguiente ejemplo se da por supuesto que el servidor es la entidad de seguridad actual.

1. Realice una conmutación por error manual al asociado de creación de reflejo de la base de datos:

   ALTER DATABASE AdventureWorks SET PARTNER FAILOVER;
   GO
   

2. Para comprobar los resultados de la conmutación por error en el reflejo nuevo, ejecute la siguiente consulta:

   SELECT name, mirroring_role_desc 
   FROM sys.databases WHERE name = N'AdventureWorks';
   GO
   

   El valor actual de mirroring_role_desc es ahora Mirror.

Vea también

Referencia

CREATE DATABASE (Transact-SQL)
DATABASEPROPERTYEX (Transact-SQL)
DROP DATABASE (Transact-SQL)
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
EVENTDATA (Transact-SQL)
sp_configure (Transact-SQL)
sp_spaceused (Transact-SQL)
sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)
sys.database_mirroring_witnesses (Transact-SQL)
sys.data_spaces (Transact-SQL)
sys.filegroups (Transact-SQL)
sys.master_files (Transact-SQL)

Otros recursos

Habilitar niveles de aislamiento basado en el control de versiones de filas
Bases de datos del sistema

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios

Versión	Historial
12 de diciembre de 2006	Contenido nuevo: Se ha agregado información acerca de las opciones que borran la caché del plan a la sección "Configurar opciones" en Notas y la definición de AUTO_CLOSE. Se ha agregado información acerca de cómo usar la opción AUTO_UPDATE_STATISTICS_ASYNC en modo de usuario único en la definición de SINGLE_USER.
14 de abril de 2006	Contenido modificado: Se ha actualizado la descripción de la opción FAILOVER para indicar que requiere master como contexto de base de datos. Se ha agregado una nota importante a la introducción de la sección "<database_mirroring_option>". En el argumento ALLOW_SNAPSHOT_ISOLATION, se ha actualizado la información sobre cómo determinar el estado de las transacciones de aislamiento de instantáneas en la base de datos y cómo afecta ese estado al comportamiento de SQL Server cuando se modifica esta opción. Se ha corregido la definición de DATE_CORRELATION_OPTIMIZATION.
5 de diciembre de 2005	Contenido nuevo: Se ha agregado una nota a la definición de ENABLE_BROKER. Se ha agregado una recomendación para modificar la opción PAGE_VERIFY en bases de datos actualizadas. Contenido modificado: Se ha quitado la opción SUPPLEMENTAL_LOGGING. Se ha corregido el ejemplo G. Se ha corregido la información sobre la opción TRUSTWORTHY en bases de datos del sistema. Se ha actualizado la definición de READ_COMMITTED_SNAPSHOT para indicar que no es necesario el modo de usuario único. Se ha actualizado la definición de <db_state_option> para indicar que no se pueden establecer las opciones OFFLINE, ONLINE y EMERGENCY cuando la base de datos se encuentra en el estado RESTORING.