ALTER DATABASE (Transact-SQL)
Modifica ciertas opciones de configuración de una base de datos.
En este artículo se proporciona la sintaxis, argumentos, comentarios, permisos y ejemplos para cualquier producto SQL que elija.
Para más información sobre las convenciones de sintaxis, vea Convenciones de sintaxis de Transact-SQL.
Selección de un producto
En la fila siguiente, seleccione el nombre del producto que le interese; de esta manera, solo se mostrará la información de ese producto.
* SQL Server *
Introducción: SQL Server
En SQL Server, esta instrucción modifica una base de datos, o los archivos y grupos de archivos asociados a la base de datos. ALTER DATABASE 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 base de datos no se pueden modificar. Para modificar las opciones de base de datos asociadas a la replicación, utilice sp_replicationdboption.
Debido a su longitud, la sintaxis ALTER DATABASE
se divide en varios artículos.
Artículo | Descripción |
---|---|
ALTER DATABASE | En el artículo actual se proporciona la sintaxis e información relacionada para cambiar el nombre y la intercalación de una base de datos. |
Opciones File y Filegroup de ALTER DATABASE | Proporciona la sintaxis e información relacionada para agregar y eliminar archivos y grupos de archivos de una base de datos y para cambiar los atributos de archivos y grupos de archivos. |
Opciones de ALTER DATABASE SET | Proporciona la sintaxis e información relacionada para cambiar los atributos de una base de datos usando las opciones SET de ALTER DATABASE. |
Creación de reflejo de la base de datos de ALTER DATABASE | Proporciona la sintaxis e información relacionada de las opciones SET de ALTER DATABASE relacionadas con la creación de reflejo de la base de datos. |
ALTER DATABASE SET HADR | Proporciona la sintaxis e información relacionada de las opciones Grupos de disponibilidad AlwaysOn de ALTER DATABASE para configurar una base de datos secundaria en una réplica secundaria de un grupo de disponibilidad AlwaysOn. |
Nivel de compatibilidad de ALTER DATABASE | Proporciona la sintaxis e información relacionada de las opciones SET de ALTER DATABASE relacionadas con los niveles de compatibilidad de la base de datos. |
ALTER DATABASE SCOPED CONFIGURATION | Proporciona la sintaxis relacionada con las configuraciones con ámbito de base de datos utilizadas para la configuración del nivel de base de datos individual, como los comportamientos relacionados con la optimización y la ejecución de consultas. |
Sintaxis
-- SQL Server Syntax
ALTER DATABASE { database_name | CURRENT }
{
MODIFY NAME = new_database_name
| COLLATE collation_name
| <file_and_filegroup_options>
| SET <option_spec> [ ,...n ] [ WITH <termination> ]
}
[;]
<file_and_filegroup_options>::=
<add_or_modify_files>::=
<filespec>::=
<add_or_modify_filegroups>::=
<filegroup_updatability_option>::=
<option_spec>::=
{
| <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>
| <delayed_durability_option>
| <external_access_option>
| <FILESTREAM_options>
| <HADR_options>
| <parameterization_option>
| <query_store_options>
| <recovery_option>
| <service_broker_option>
| <snapshot_option>
| <sql_option>
| <termination>
| <temporal_history_retention>
| <data_retention_policy>
| <compatibility_level>
{ 160 | 150 | 140 | 130 | 120 | 110 | 100 | 90 }
}
Argumentos
database_name
Es el nombre de la base de datos que se va a modificar.
Nota:
Esta opción no está disponible en una base de datos independiente.
CURRENT
Válido para : SQL Server 2012 (11.x) y versiones posteriores.
Designa que la base de datos actual en uso se debe modificar.
MODIFY NAME = new_database_name
Reemplaza el nombre de la base de datos por el nombre especificado como new_database_name.
COLLATE collation_name
Especifica la intercalación de 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.
Nota:
La intercalación no se puede cambiar una vez creada la base de datos en Azure SQL Database.
Al crear bases de datos con una intercalación diferente de la predeterminada, los datos de la base de datos siempre respetan la intercalación especificada. Para SQL Server, al crear una base de datos independiente, la información de catálogo interno se mantiene mediante la intercalación predeterminada de SQL Server, Latin1_General_100_CI_AS_WS_KS_SC.
Para más información sobre los nombres de intercalación de Windows y de SQL, consulte COLLATE.
<delayed_durability_option> ::=
Válido para : SQL Server 2014 (12.x) y versiones posteriores.
Para obtener más información, vea Opciones alter DATABASE SET y Controlar la durabilidad de las transacciones.
<file_and_filegroup_options>::=
Para más información, consulte Opciones File y Filegroup de ALTER DATABASE.
Observaciones
Para quitar una base de datos, use DROP DATABASE.
Para reducir el tamaño de una base de datos, use DBCC SHRINKDATABASE.
La ALTER DATABASE
instrucción debe ejecutarse en modo de confirmación automática (el modo de administración de transacciones predeterminado) y no se permite en una transacción explícita o implícita.
El estado de un archivo de base de datos (por ejemplo, en línea o sin conexión) se mantiene con independencia del estado de la base de datos. Para 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 de archivos deben estar en línea. Si un grupo de archivos está sin conexión, cualquier intento de acceder al grupo de archivos mediante una instrucción SQL produce un error. Al generar un plan de consulta para las instrucciones SELECT, el optimizador de consultas evita los índices no clúster 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 clúster de la tabla de destino, las instrucciones SELECT no funcionarán. Además, se produce un error en cualquier INSERT
instrucción , UPDATE
o DELETE
que modifique una tabla con cualquier índice de un grupo de archivos sin conexión.
Cuando una base de datos está en estado RESTORE, se produce un error en la mayoría ALTER DATABASE
de las instrucciones. La excepción es el establecimiento de opciones de creación de reflejo de la base de datos. Una base de datos puede estar en estado RESTORE 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 un archivo de registro debido a un archivo de copia de seguridad dañado.
La memoria caché de planes para la instancia de SQL Server se borra si se establece alguna de las opciones siguientes.
- COLLATE
- MODIFY FILEGROUP DEFAULT
- MODIFY FILEGROUP READ_ONLY
- MODIFY FILEGROUP READ_WRITE
- MODIFY_NAME
- OFFLINE
- ONLINE
- PAGE_VERIFY
- READ_ONLY
- READ_WRITE
Al borrar la memoria caché de planes, 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. Para cada almacén de caché borrado de la caché de planes, el registro de errores de SQL Server contiene el siguiente mensaje informativo: SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations
. Este mensaje se registra cada cinco minutos siempre que se vacíe la memoria caché dentro de ese intervalo de tiempo.
La caché de planes también se vacía en los escenarios siguientes:
- Una base de datos tiene la opción de base de datos
AUTO_CLOSE
establecida en ON. Cuando ninguna conexión de usuario hace referencia a la base de datos ni la usa, la tarea de segundo plano intenta cerrar la base de datos y apagarla de modo automático. - Ejecuta varias consultas con una base de datos que tiene opciones predeterminadas. Después, la base de datos se quita.
- Se quita una instantánea de base de datos para una base de datos de origen.
- Volvió a generar correctamente el registro de transacciones para una base de datos.
- Restaura una copia de seguridad de una base de datos
- Separa una 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:
- Es el único usuario que utiliza actualmente la base de datos.
- Ningún objeto enlazado a un esquema depende de la intercalación de la base de datos.
Si los objetos siguientes, que dependen de la intercalación de la base de datos, existen en la base de datos, se produce un error en la ALTER DATABASE database_name COLLATE
instrucción . SQL Server devuelve un mensaje de error para cada objeto que bloquea la ALTER
acción:
- Vistas y funciones definidas por el usuario creadas con SCHEMABINDING
- Columnas calculadas
- CHECK, restricciones
- Funciones con valores de tabla que devuelven tablas con columnas de caracteres con intercalaciones heredadas de la intercalación predeterminada de la base de datos
La información de dependencia de las entidades no vinculadas a esquemas se actualiza automáticamente si se cambia la intercalación de la base de datos.
Cambiar la intercalación de la base de datos no crea duplicados entre los nombres del sistema para los objetos de base de datos. Si los nombres duplicados resultan de la intercalación modificada, los siguientes espacios de nombres pueden provocar el error de un cambio de intercalación de base de datos:
- Nombres de objetos, como un procedimiento, una tabla, un desencadenador o una vista
- Nombres de esquemas
- Entidades de seguridad, como un grupo, rol o usuario
- Nombres de tipo escalar, como los tipos definidos por el usuario y por el sistema
- Nombres de catálogos de texto completo
- Nombres de columnas o parámetros en un objeto
- Nombres de índices en una tabla
Los nombres duplicados resultantes de la nueva intercalación hacen que se produzca un error en la acción de cambio y SQL Server devuelve un mensaje de error que especifica el espacio de nombres donde se encontró el duplicado.
Visionado de la información de la base de datos
Se pueden utilizar vistas de catálogo, funciones del sistema y procedimientos almacenados del sistema para devolver información sobre bases de datos, archivos y grupos de archivos.
Permisos
Debe tener el permiso ALTER
para la base de datos.
Ejemplos
A. Cambio del nombre de una base de datos
En el ejemplo siguiente se cambia el nombre de la base de datos AdventureWorks2022
a Northwind
.
USE master;
GO
ALTER DATABASE AdventureWorks2022
Modify Name = Northwind ;
GO
B. Cambio de la intercalación de una base de datos
En el siguiente ejemplo se crea una base de datos denominada testdb
con la intercalación SQL_Latin1_General_CP1_CI_AS
, y luego se cambia la intercalación de la base de datos testdb
a COLLATE French_CI_AI
.
Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores.
USE master;
GO
CREATE DATABASE testdb
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO
ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO
Contenido relacionado
* SQL Database *
Introducción: SQL Database
En Azure SQL Database, use esta instrucción para modificar una base de datos. Use esta instrucción para cambiar el nombre de una base de datos, cambiar el objetivo de edición y servicio de la base de datos, unir la base de datos a un grupo elástico o quitarla de uno, establecer las opciones de base de datos, agregar o quitar la base de datos como una base de datos secundaria en una relación de replicación geográfica y establecer el nivel de compatibilidad de base de datos.
Debido a su longitud, la sintaxis ALTER DATABASE
se divide en varios artículos.
ALTER DATABASE
En el artículo actual se proporciona la sintaxis e información relacionada para cambiar el nombre y otros valores de una base de datos.
Opciones SET de ALTER DATABASE
Proporciona la sintaxis e información relacionada para cambiar los atributos de una base de datos usando las opciones SET de ALTER DATABASE.
Nivel de compatibilidad de ALTER DATABASE
Proporciona la sintaxis e información relacionada de las opciones SET de ALTER DATABASE relacionadas con los niveles de compatibilidad de la base de datos.
Sintaxis
-- Azure SQL Database Syntax
ALTER DATABASE { database_name | CURRENT }
{
MODIFY NAME = new_database_name
| MODIFY ( <edition_options> [, ... n] )
| MODIFY BACKUP_STORAGE_REDUNDANCY = { 'LOCAL' | 'ZONE' | 'GEO' }
| SET { <option_spec> [ ,... n ] WITH <termination>}
| ADD SECONDARY ON SERVER <partner_server_name>
[WITH ( <add-secondary-option>::=[, ... n] ) ]
| REMOVE SECONDARY ON SERVER <partner_server_name>
| FAILOVER
| FORCE_FAILOVER_ALLOW_DATA_LOSS
}
[;]
<edition_options> ::=
{
MAXSIZE = { 100 MB | 250 MB | 500 MB | 1 ... 1024 ... 4096 GB }
| EDITION = { 'Basic' | 'Standard' | 'Premium' | 'GeneralPurpose' | 'BusinessCritical' | 'Hyperscale'}
| SERVICE_OBJECTIVE =
{ <service-objective>
| { ELASTIC_POOL (name = <elastic_pool_name>) }
}
}
<add-secondary-option> ::=
{
ALLOW_CONNECTIONS = { ALL | NO }
| BACKUP_STORAGE_REDUNDANCY = { 'LOCAL' | 'ZONE' | 'GEO' }
| SERVICE_OBJECTIVE =
{ <service-objective>
| { ELASTIC_POOL ( name = <elastic_pool_name>) }
| DATABASE_NAME = <target_database_name>
| SECONDARY_TYPE = { GEO | NAMED }
}
}
<service-objective> ::={ 'Basic' |'S0' | 'S1' | 'S2' | 'S3'| 'S4'| 'S6'| 'S7'| 'S9'| 'S12'
| 'P1' | 'P2' | 'P4'| 'P6' | 'P11' | 'P15'
| 'BC_DC_n'
| 'BC_Gen5_n'
| 'BC_M_n'
| 'GP_DC_n'
| 'GP_Fsv2_n'
| 'GP_Gen5_n'
| 'GP_S_Gen5_n'
| 'HS_DC_n'
| 'HS_Gen5_n'
| 'HS_MOPRMS_n'
| 'HS_PRMS_n'
| { ELASTIC_POOL(name = <elastic_pool_name>) }
}
<option_spec> ::=
{
<auto_option>
| <change_tracking_option>
| <cursor_option>
| <db_encryption_option>
| <db_update_option>
| <db_user_access_option>
| <delayed_durability_option>
| <parameterization_option>
| <query_store_options>
| <snapshot_option>
| <sql_option>
| <target_recovery_time_option>
| <termination>
| <temporal_history_retention>
| <compatibility_level>
{ 160 | 150 | 140 | 130 | 120 | 110 | 100 | 90 }
}
Argumentos
database_name
Es el nombre de la base de datos que se va a modificar.
CURRENT
Designa que la base de datos actual en uso se debe modificar.
MODIFY NAME = new_database_name
Reemplaza el nombre de la base de datos por el nombre especificado como new_database_name. En el ejemplo siguiente se cambia el nombre de la base de datos db1
a db2
:
ALTER DATABASE db1
MODIFY Name = db2 ;
MODIFY (EDITION = ['Basic' | 'Standard' | 'Premium' |'GeneralPurpose' | 'BusinessCritical' | 'Hyperscale'])
Cambia el nivel de servicio de la base de datos.
En el ejemplo siguiente se cambia la edición a Premium
:
ALTER DATABASE current
MODIFY (EDITION = 'Premium');
Importante
Se produce un error en el cambio de EDITION si la propiedad MAXSIZE de la base de datos está establecida en un valor fuera del intervalo válido admitido por esa edición.
MODIFY (BACKUP_STORAGE_REDUNDANCY = ['LOCAL' | 'ZONE' | 'GEO'])
Cambia la redundancia de almacenamiento de copias de seguridad de restauración a un momento dado como las copias de seguridad de retención a largo plazo (si se configuran) de la base de datos. Los cambios se aplicarán a todas las copias de seguridad que se realicen en el futuro. Las copias de seguridad existentes seguirán usando la configuración anterior.
Para aplicar la residencia de datos al crear una base de datos mediante T-SQL, use LOCAL
o ZONE
como entrada del parámetro BACKUP_STORAGE_REDUNDANCY.
MODIFY (MAXSIZE = [100 MB | 500 MB | 1 | 1024...4096] GB)
Especifica el tamaño máximo de la base de datos. El tamaño máximo debe cumplir con el conjunto válido de valores de la propiedad EDITION de la base de datos. Cambiar el tamaño máximo de la base de datos puede hacer que se cambie la edición de la base de datos.
Nota:
El argumento MAXSIZE no es aplicable a bases de datos únicas en el nivel de servicio Hyperscale. Las bases de datos de nivel de servicio Hyperscale crecen según sea necesario, hasta 100 TB. El servicio SQL Database agrega almacenamiento automáticamente; no es necesario establecer un tamaño máximo.
Modelo de DTU
MAXSIZE | Basic | S0-S2 | S3-S12 | P1-P6 | P11-P15 |
---|---|---|---|---|---|
100 MB | Sí | Sí | Sí | Sí | Sí |
250 MB | Sí | Sí | Sí | Sí | Sí |
500 MB | Sí | Sí | Sí | Sí | Sí |
1 GB | Sí | Sí | Sí | Sí | Sí |
2 GB | Sí (D) | Sí | Sí | Sí | Sí |
5 GB | N/D | Sí | Sí | Sí | Sí |
10 GB | N/D | Sí | Sí | Sí | Sí |
20 GB | N/D | Sí | Sí | Sí | Sí |
30 GB | N/D | Sí | Sí | Sí | Sí |
40 GB | N/D | Sí | Sí | Sí | Sí |
50 GB | N/D | Sí | Sí | Sí | Sí |
100 GB | N/D | Sí | Sí | Sí | Sí |
150 GB | N/D | Sí | Sí | Sí | Sí |
200 GB | N/D | Sí | Sí | Sí | Sí |
250 GB | N/D | Sí (D) | Sí (D) | Sí | Sí |
300 GB | N/D | Sí | Sí | Sí | Sí |
400 GB | N/D | Sí | Sí | Sí | Sí |
500 GB | N/D | Sí | Sí | Sí (D) | Sí |
750 GB | N/D | Sí | Sí | Sí | Sí |
1024 GB | N/D | Sí | Sí | Sí | Sí (D) |
Desde 1024 GB hasta 4096 GB en incrementos de 256 GB 1 | N/D | N/D | N/D | N/D | Sí |
1 P11 y P15 permiten MAXSIZE hasta 4 TB con 1024 GB siendo el tamaño predeterminado. P11 y P15 pueden usar hasta 4 TB de almacenamiento incluido sin cargos adicionales. En el nivel Premium, un valor de MAXSIZE mayor de 1 TB está actualmente disponible en las regiones siguientes: Este de EE. UU. 2, Oeste de EE. UU., US Gov Virginia, Oeste de Europa, Centro de Alemania, Sudeste de Asia, Este de Japón, Este de Australia, Centro de Canadá y Este de Canadá. Para obtener más información sobre las limitaciones de recursos para el modelo de DTU, vea Límites de recursos de DTU.
El valor MAXSIZE para el modelo de DTU, si se especifica, tiene que ser válido según lo que se indica en la tabla anterior para el nivel de servicio especificado.
Para conocer los límites como el tamaño máximo de los datos y el tamaño tempdb
del modelo de compra de núcleo virtual, consulte los artículos sobre los límites de recursos para bases de datos únicas o los límites de recursos para grupos elásticos.
Si no hay ningún valor MAXSIZE
establecido al utilizar el modelo de núcleo virtual, el valor predeterminado es 32 GB. Para obtener más información sobre las limitaciones de recursos para el modelo de núcleo virtual, vea Límites de recursos del núcleo virtual.
Las reglas siguientes se aplican a los argumentos MAXSIZE y EDITION:
- Si se especifica EDITION pero no se especifica MAXSIZE, se usa el valor predeterminado de la edición. Por ejemplo, es la edición se establece en Estándar y no se especifica MAXSIZE y, a continuación, MAXSIZE se establece automáticamente en 250 MB.
- Si no se especifica MAXSIZE ni EDITION, este último se establece en De uso general y MAXSIZE se establece en 32 GB.
MODIFY (SERVICE_OBJECTIVE = <service-objective>)
Especifica el tamaño de proceso y el objetivo de servicio.
SERVICE_OBJECTIVE
Especifica el tamaño de proceso (también conocido como objetivo de nivel de servicio o SLO).
- Para el modelo de compra de DTU:
S0
,S1
,S2
,S3
,S4
,S6
,S7
,S9
,S12
,P1
,P2
,P4
,P6
,P11
,P15
. Consulte los límites de recursos para bases de datos únicas de DTU o los límites de recursos para grupos elásticos de DTU para encontrar el número de DTU asignado a cada tamaño de proceso. - Para el modelo de compra de núcleos virtuales, elija el nivel y proporcione el número de núcleos virtuales de una lista preestablecida de valores, donde el número de núcleos virtuales es
n
. Consulte los límites de recursos para bases de datos únicas de núcleo virtual o los límites de recursos para grupos elásticos de núcleo virtual.- Por ejemplo:
GP_Gen5_8
para el proceso de la serie estándar (Gen5) de uso general, 8 núcleos virtuales.GP_S_Gen5_8
para el proceso de la serie estándar (Gen5) de uso general sin servidor, 8 núcleos virtuales.HS_Gen5_8
para Hiperescala, proceso aprovisionado, serie estándar (Gen5), 8 núcleos virtuales.
Por ejemplo, a continuación se cambia el objetivo de servicio de una base de datos de nivel Premium en el modelo de compra DTU a P6
:
ALTER DATABASE <database_name>
MODIFY (SERVICE_OBJECTIVE = 'P6');
Por ejemplo, a continuación se cambia el objetivo de servicio de una base de datos de proceso aprovisionada en el modelo de compra de núcleo virtual a GP_Gen5_8
:
ALTER DATABASE <database_name>
MODIFY (SERVICE_OBJECTIVE = 'GP_Gen5_8');
Database_Name
Solo para Hiperescala de Azure SQL Database. Nombre de la base de datos que se creará. Solo lo usan réplicas con nombre de Hiperescala de Azure SQL Database, cuando SECONDARY_TYPE
= NAMED. Para más información, consulte Réplicas secundarias de Hiperescala.
SECONDARY_TYPE
Solo para Hiperescala de Azure SQL Database. GEO especifica una réplica geográfica y NAMED, una réplica con nombre. El valor predeterminado es GEO. Para más información, consulte Réplicas secundarias de Hiperescala.
Para obtener descripciones de objetivos de servicio y más información sobre el tamaño, las ediciones y las combinaciones de objetivos de servicio, consulte Comparación de modelos de compra basados en DTU y núcleo virtual de Azure SQL Database, límites de recursos de DTU y límites de recursos de núcleo virtual. Se ha quitado la compatibilidad para los objetivos de servicio de PRS.
Cuando no se especifica SERVICE_OBJECTIVE, la base de datos secundaria se crea en el mismo nivel de servicio que la base de datos principal. Cuando se especifica SERVICE_OBJECTIVE, la base de datos secundaria se crea en el nivel especificado. El valor SERVICE_OBJECTIVE especificado debe estar en la misma edición que el origen. Por ejemplo, no se puede especificar S0 si la edición es Premium.
MODIFY (SERVICE_OBJECTIVE = ELASTIC_POOL (name = <elastic_pool_name>)
Para agregar una base de datos existente a un grupo elástico, establezca el valor SERVICE_OBJECTIVE de la base de datos en ELASTIC_POOL e indique el nombre del grupo. También se puede usar esta opción para cambiar la base de datos a un grupo elástico diferente dentro del mismo servidor. Para más información, consulte Grupos elásticos que le ayudan a administrar y escalar varias bases de datos en Azure SQL Database. A fin de quitar una base de datos de un grupo elástico, use ALTER DATABASE para establecer el valor SERVICE_OBJECTIVE en un tamaño de proceso de base de datos única (objetivo de servicio).
Nota:
Las bases de datos del nivel de servicio Hiperescala no se pueden agregar a un grupo elástico.
ADD SECONDARY ON SERVER <partner_server_name>
Crea una base de datos de replicación geográfica secundaria con el mismo nombre en un servidor asociado, lo que convierte a la base de datos local en una base de datos principal de replicación geográfica, y comienza a replicar los datos de forma asincrónica desde la base de datos principal a la nueva base de datos secundaria. Si ya existe una base de datos con el mismo nombre en la base de datos secundaria, se produce un error en el comando. El comando se ejecuta en la base de datos master
en el servidor que hospeda la base de datos local que se convierte en la principal.
Importante
De forma predeterminada, la base de datos secundaria se crea con la misma redundancia de almacenamiento de copia de seguridad que la de la base de datos principal o de origen. No se admite el cambio de la redundancia de almacenamiento de copia de seguridad al crear la base de datos secundaria a través de T-SQL.
WITH ALLOW_CONNECTIONS { ALL | NO }
Cuando no se especifica ALLOW_CONNECTIONS, se establece en ALL de forma predeterminada. Si se establece en ALL, es una base de datos de solo lectura que permite la conexión de todos los inicios de sesión con los permisos adecuados.
ELASTIC_POOL (name = <elastic_pool_name>)
Cuando no se especifica ELASTIC_POOL, la base de datos secundaria no se crea en un grupo elástico. Cuando se especifica ELASTIC_POOL, la base de datos secundaria se crea en el grupo especificado.
Importante
El usuario que ejecuta el comando ADD SECONDARY debe ser DBManager en el servidor principal, ser miembro de db_owner en la base de datos local y DBManager en el servidor secundario. La dirección IP del cliente debe agregarse a la lista de permitidas de las reglas del firewall, tanto para el servidor primario como para el secundario. En el caso de que haya diferentes direcciones IP del cliente, también deberá agregar al servidor secundario la misma que se haya agregado al principal. Este paso se tiene que realizar antes de ejecutar el comando ADD SECONDARY para iniciar la replicación geográfica.
REMOVE SECONDARY ON SERVER <partner_server_name>
Quita la base de datos secundaria con replicación geográfica especificada en el servidor especificado. El comando se ejecuta en la base de datos master
en el servidor que hospeda la base de datos principal.
Importante
El usuario que ejecuta el comando REMOVE SECONDARY
debe ser DBManager en el servidor principal.
FAILOVER
Promueve la base de datos secundaria en colaboración de replicación geográfica en la que el comando se ejecuta para convertirla en la principal y degrada la base de datos principal actual para convertirla en la nueva base de datos secundaria. Como parte de este proceso, el modo de replicación geográfica se cambia temporalmente de asincrónico a sincrónico. Durante el proceso de conmutación por error:
- La base de datos principal deja de aceptar nuevas transacciones.
- Todas las transacciones pendientes se vacían en la base de datos secundaria.
- La base de datos secundaria se convierte en la principal y comienza la replicación geográfica asincrónica con la base de datos principal anterior y la base de datos secundaria nueva.
Esta secuencia garantiza que no se produzca pérdida de datos. El período durante el que ambas bases de datos no están disponibles es de entre 0 y 25 segundos mientras se intercambian los roles. La operación total no debería tardar más de un minuto aproximadamente. Si la base de datos principal no está disponible cuando se emite este comando, se produce un error en el comando con un mensaje de error que indica que la base de datos principal no está disponible. Si el proceso de conmutación por error no se completa y aparece bloqueado, se puede usar el comando para forzar la conmutación por error y aceptar la pérdida de datos, y después, si tiene que recuperar los datos perdidos, llama a devops (CSS) para recuperarlos.
Importante
El usuario que ejecuta el comando FAILOVER debe ser DBManager tanto en el servidor principal como en el secundario.
FORCE_FAILOVER_ALLOW_DATA_LOSS
Promueve la base de datos secundaria en colaboración de replicación geográfica en la que el comando se ejecuta para convertirla en la principal y degrada la base de datos principal actual para convertirla en la nueva base de datos secundaria. Use este comando solo cuando la base de datos principal actual ya no esté disponible. Está diseñado solo para la recuperación ante desastres, cuando la restauración de la disponibilidad sea esencial y se acepte cierta pérdida de datos.
Durante una conmutación por error forzada:
- La base de datos secundaria especificada se convierte inmediatamente en la base de datos principal y comienza a aceptar nuevas transacciones.
- Cuando la base de datos principal original se pueda volver a conectar con la nueva base de datos principal, se toma una copia de seguridad incremental en la base de datos principal original y se convierte en una nueva base de datos secundaria.
- Para recuperar datos de esta copia de seguridad incremental en la base de datos principal anterior, el usuario usa devops/CSS.
- Si hay otras bases de datos secundarias, se reconfiguran de forma automática para convertirse en secundarias de la nueva base de datos principal. Este proceso es asincrónico y puede haber un retraso hasta que se complete este proceso. Hasta que se haya completado la reconfiguración, las bases de datos secundarias siguen siendo secundarias de la base de datos principal anterior.
Importante
El usuario que ejecuta el comando FORCE_FAILOVER_ALLOW_DATA_LOSS
debe pertenecer al rol dbmanager
tanto en el servidor principal como en el secundario.
Observaciones
Para quitar una base de datos, use DROP DATABASE. Para reducir el tamaño de una base de datos, use DBCC SHRINKDATABASE.
La ALTER DATABASE
instrucción debe ejecutarse en modo de confirmación automática (el modo de administración de transacciones predeterminado) y no se permite en una transacción explícita o implícita.
Al borrar la memoria caché de planes, 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. Para cada almacén de caché borrado de la caché de planes, el registro de errores de SQL Server contiene el siguiente mensaje informativo: SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations
. Este mensaje se registra cada cinco minutos siempre que se vacíe la memoria caché dentro de ese intervalo de tiempo.
La caché de procedimientos también se vacía en el escenario siguiente: Ejecuta varias consultas con una base de datos que tiene opciones predeterminadas. Después, la base de datos se quita.
Visionado de la información de la base de datos
Se pueden utilizar vistas de catálogo, funciones del sistema y procedimientos almacenados del sistema para devolver información sobre bases de datos, archivos y grupos de archivos.
Permisos
Para modificar una base de datos, un inicio de sesión debe ser el inicio de sesión de administrador del servidor (creado cuando se aprovisionó el servidor lógico de Azure SQL Database), el administrador de Microsoft Entra del servidor, un miembro del rol de base de datos dbmanager en master
, un miembro del rol de base de datos de db_owner en la base de datos actual o dbo
de la base de datos. Microsoft Entra ID es (anteriormente Azure Active Directory).
Para escalar bases de datos a través de T-SQL, se necesitan permisos ALTER DATABASE. Para escalar bases de datos a través de Azure Portal, PowerShell, la CLI de Azure o la API REST, se necesitan permisos de RBAC de Azure, específicamente el Colaborador, el rol Colaborador de base de datos SQL o roles RBAC de Azure Colaborador de SQL Server. Para más información, visite Roles integrados de Azure.
Ejemplos
A. Comprobación y cambio de las opciones de edición
Establece un tamaño máximo y de edición para la base de datos db1
:
SELECT Edition = DATABASEPROPERTYEX('db1', 'EDITION'),
ServiceObjective = DATABASEPROPERTYEX('db1', 'ServiceObjective'),
MaxSizeInBytes = DATABASEPROPERTYEX('db1', 'MaxSizeInBytes');
ALTER DATABASE [db1] MODIFY (EDITION = 'Premium', MAXSIZE = 1024 GB, SERVICE_OBJECTIVE = 'P15');
B. Movimiento de una base de datos a otro grupo elástico
Mueve una base de datos existente a un grupo denominado pool1
:
ALTER DATABASE db1
MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = pool1 ) ) ;
C. Adición de una base de datos secundaria de replicación geográfica
Crea una base de datos secundaria legible db1
en el servidor secondaryserver
de db1
en el servidor local.
ALTER DATABASE db1
ADD SECONDARY ON SERVER secondaryserver
WITH ( ALLOW_CONNECTIONS = ALL )
D. Eliminación de una base de datos secundaria de replicación geográfica
Quita la base de datos secundaria db1
del servidor secondaryserver
.
ALTER DATABASE db1
REMOVE SECONDARY ON SERVER testsecondaryserver
E. Conmutación por error a una base de datos secundaria de replicación geográfica
Promueve una base de datos secundaria db1
en el servidor secondaryserver
para que se convierta en la nueva base de datos principal cuando se ejecute en el servidor secondaryserver
.
ALTER DATABASE db1 FAILOVER
Nota:
Para más información, consulte Guía de recuperación ante desastres: Azure SQL Database y la lista de comprobación de alta disponibilidad y recuperación ante desastres de Azure SQL Database.
F. Forzado de la conmutación por error a una base de datos secundaria de replicación geográfica con pérdida de datos
Obliga a una base de datos secundaria db1
en el servidor secondaryserver
a convertirse en la nueva base de datos principal cuando se ejecute en el servidor secondaryserver
en caso de que el servidor principal ya no esté disponible. Esta opción puede incurrir en pérdida de datos.
ALTER DATABASE db1 FORCE_FAILOVER_ALLOW_DATA_LOSS
G. Actualizar una única base de datos al nivel de servicio S0 (edición Estándar, nivel de rendimiento 0)
Actualiza una base de datos única a la edición Estándar (nivel de servicio) con un tamaño de proceso (objetivo de servicio) de S0 y un tamaño máximo de 250 GB.
ALTER DATABASE [db1] MODIFY (EDITION = 'Standard', MAXSIZE = 250 GB, SERVICE_OBJECTIVE = 'S0');
H. Actualización de la redundancia de almacenamiento de copia de seguridad de una base de datos
Actualiza la redundancia de almacenamiento de copia de seguridad de una base de datos a la redundancia de zona. Todas las copias de seguridad futuras de esta base de datos usan la nueva configuración. Esto incluye las copias de seguridad de restauración a un momento dado y las de retención a largo plazo (si se configuran).
ALTER DATABASE db1 MODIFY BACKUP_STORAGE_REDUNDANCY = 'ZONE'
Contenido relacionado
- CREATE DATABASE - Azure SQL Database
- DATABASEPROPERTYEX
- DROP DATABASE
- SET TRANSACTION ISOLATION LEVEL
- EVENTDATA
- sp_spaceused
- sys.databases
- sys.database_files
- sys.filegroups
- sys.master_files
- Bases de datos del sistema
- Guía de recuperación ante desastres: Azure SQL Database
- Lista de comprobación de alta disponibilidad y recuperación ante desastres de Azure SQL Database
- Límites de recursos de DTU
- Límites de recursos de núcleo virtual para bases de datos únicas
- Límites de recursos de núcleo virtual para grupos elásticos
* SQL Managed Instance *
Introducción: Instancia administrada de Azure SQL
En Instancia administrada de Azure SQL, use esta instrucción para establecer las opciones de base de datos.
Debido a su longitud, la sintaxis ALTER DATABASE
se divide en varios artículos.
Artículo | Descripción |
---|---|
ALTER DATABASE | |
En el artículo actual se proporciona la sintaxis e información relacionada para establecer las opciones File y Filegroup, para establecer las opciones de base de datos y el nivel de compatibilidad de base de datos. | |
Opciones File y Filegroup de ALTER DATABASE | |
Proporciona la sintaxis e información relacionada para agregar y eliminar archivos y grupos de archivos de una base de datos y para cambiar los atributos de archivos y grupos de archivos. | |
Opciones SET de ALTER DATABASE | |
Proporciona la sintaxis e información relacionada para cambiar los atributos de una base de datos usando las opciones SET de ALTER DATABASE. | |
Nivel de compatibilidad de ALTER DATABASE | |
Proporciona la sintaxis e información relacionada de las opciones SET de ALTER DATABASE relacionadas con los niveles de compatibilidad de la base de datos. |
Sintaxis
-- Azure SQL Managed Instance syntax
ALTER DATABASE { database_name | CURRENT }
{
MODIFY NAME = new_database_name
| COLLATE collation_name
| <file_and_filegroup_options>
| SET <option_spec> [ ,...n ]
}
[;]
<file_and_filegroup_options>::=
<add_or_modify_files>::=
<filespec>::=
<add_or_modify_filegroups>::=
<filegroup_updatability_option>::=
<option_spec> ::=
{
<auto_option>
| <change_tracking_option>
| <cursor_option>
| <db_encryption_option>
| <db_update_option>
| <db_user_access_option>
| <delayed_durability_option>
| <parameterization_option>
| <query_store_options>
| <snapshot_option>
| <sql_option>
| <target_recovery_time_option>
| <temporal_history_retention>
| <compatibility_level>
{ 160 | 150 | 140 | 130 | 120 | 110 | 100 | 90 }
}
Argumentos
database_name
Es el nombre de la base de datos que se va a modificar.
CURRENT
Designa que la base de datos actual en uso se debe modificar.
Observaciones
Para quitar una base de datos, use DROP DATABASE.
Para reducir el tamaño de una base de datos, use DBCC SHRINKDATABASE.
La
ALTER DATABASE
instrucción debe ejecutarse en modo de confirmación automática (el modo de administración de transacciones predeterminado) y no se permite en una transacción explícita o implícita.La caché de planes de Azure SQL Managed Instance se borra si se establece una de las opciones siguientes.
COLLATE
MODIFY FILEGROUP DEFAULT
MODIFY FILEGROUP READ_ONLY
MODIFY FILEGROUP READ_WRITE
MODIFY NAME
Al borrar la memoria caché de planes, 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. Para cada almacén de caché borrado de la caché de planes, el registro de errores de SQL Server contiene el siguiente mensaje informativo:
SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations
. Este mensaje se registra cada cinco minutos siempre que se vacíe la memoria caché dentro de ese intervalo de tiempo. También se vacía la caché de planes cuando se ejecutan varias consultas en una base de datos que tiene las opciones predeterminadas. Después, la base de datos se quita.
Para ejecutarse, algunas instrucciones
ALTER DATABASE
necesitan un bloqueo exclusivo en una base de datos. Por este motivo es posible que se produzca un error cuando otro proceso activo mantenga un bloqueo en la base de datos. Un error que se notifica en estos casos esMsg 5061, Level 16, State 1, Line 38
con el mensajeALTER DATABASE failed because a lock could not be placed on database '<database name>'. Try again later
. Suele ser un error transitorio. Para resolverlo, una vez que se hayan liberado todos los bloqueos de la base de datos, vuelva a intentar la instrucciónALTER DATABASE
en la que se ha producido el error. La vista del sistemasys.dm_tran_locks
contiene información sobre los bloqueos activos. Para comprobar si hay bloqueos compartidos o exclusivos en una base de datos, use la consulta siguiente.SELECT resource_type, resource_database_id, request_mode, request_type, request_status, request_session_id FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('testdb');
Visionado de la información de la base de datos
Se pueden utilizar vistas de catálogo, funciones del sistema y procedimientos almacenados del sistema para devolver información sobre bases de datos, archivos y grupos de archivos.
Permisos
Solo el inicio de sesión principal de nivel de servidor (creado por el proceso de aprovisionamiento) o los miembros del rol de base de datos dbcreator
pueden modificar una base de datos.
Importante
El propietario de la base de datos no puede modificar la base de datos a menos que sean miembros del dbcreator
rol.
Ejemplos
En los ejemplos siguientes se muestra cómo establecer el ajuste automático y cómo agregar un archivo en Azure SQL Managed Instance.
ALTER DATABASE WideWorldImporters
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON);
ALTER DATABASE WideWorldImporters
ADD FILE (NAME = 'data_17');
Contenido relacionado
* Azure Synapse
Analytics *
Introducción: Azure Synapse Analytics
En Azure Synapse, ALTER DATABASE
modifica determinadas opciones de configuración de un grupo de SQL dedicado.
Debido a su longitud, la sintaxis ALTER DATABASE
se divide en varios artículos.
Las opciones alter DATABASE SET proporcionan la sintaxis y la información relacionada para cambiar los atributos de una base de datos mediante las opciones SET de ALTER DATABASE
.
Sintaxis
ALTER DATABASE { database_name | CURRENT }
{
MODIFY NAME = new_database_name
| MODIFY ( <edition_option> [, ... n] )
| SET <option_spec> [ ,...n ] [ WITH <termination> ]
}
[;]
<edition_option> ::=
MAXSIZE = {
250 | 500 | 750 | 1024 | 5120 | 10240 | 20480
| 30720 | 40960 | 51200 | 61440 | 71680 | 81920
| 92160 | 102400 | 153600 | 204800 | 245760
} GB
| SERVICE_OBJECTIVE = {
'DW100' | 'DW200' | 'DW300' | 'DW400' | 'DW500'
| 'DW600' | 'DW1000' | 'DW1200' | 'DW1500' | 'DW2000'
| 'DW3000' | 'DW6000' | 'DW500c' | 'DW1000c' | 'DW1500c'
| 'DW2000c' | 'DW2500c' | 'DW3000c' | 'DW5000c' | 'DW6000c'
| 'DW7500c' | 'DW10000c' | 'DW15000c' | 'DW30000c'
}
Argumentos
database_name
Especifica el nombre de la base de datos que se va a modificar.
MODIFY NAME = new_database_name
Reemplaza el nombre de la base de datos por el nombre especificado como new_database_name.
La opción "MODIFY NAME" tiene algunas limitaciones de compatibilidad en Azure Synapse:
- No es compatible con grupos sin servidor de Azure Synapse.
- No es compatible con los grupos de SQL dedicados que se hayan creado en el área de trabajo de Azure Synapse.
- Es compatible con los grupos de SQL dedicados (anteriormente, SQL DW) que se hayan creado a través de Azure Portal, incluidos los que cuentan con un área de trabajo conectada.
MAXSIZE
El valor predeterminado es 245 760 GB (240 TB).
Se aplica a: Optimizado para Compute Gen1
El tamaño máximo permitido para la base de datos. La base de datos no puede crecer más allá de MAXSIZE.
Se aplica a: Optimizado para Compute Gen2
Tamaño máximo permitido para los datos de almacenamiento de filas de la base de datos. Los datos almacenados en tablas de almacén de filas, el almacén delta de un índice de almacén de columnas o un índice no agrupado en un índice de almacén de columnas agrupado no pueden crecer más allá de MAXSIZE. Los datos comprimidos en formato de almacén de columnas no tienen un límite de tamaño y no están restringidos por MAXSIZE.
SERVICE_OBJECTIVE
Especifica el tamaño de proceso (objetivo de servicio). Para más información sobre los objetivos de servicio para Azure Synapse, consulte Unidades de almacenamiento de datos (DWU).
Permisos
Se requieren estos permisos:
- Inicio de sesión principal en el nivel de servidor (creado por el proceso de aprovisionamiento), o
- Miembro del rol de base de datos
dbmanager
.
El propietario de la base de datos no puede modificar la base de datos a menos que el propietario sea miembro del dbmanager
rol.
Comentarios
La base de datos actual debe ser diferente de la base de datos que está modificando, por lo que ALTER debe ejecutarse mientras se está conectado a la base de datos master
.
COMPATIBILITY_LEVEL en SQL Analytics se establece en 130 de forma predeterminada y no se puede cambiar. Para más información, consulte Nivel de compatibilidad ALTER DATABASE.
Nota:
COMPATIBILITY_LEVEL solo se aplica a los recursos aprovisionados (grupos).
Limitaciones
Para ejecutar ALTER DATABASE
, la base de datos debe estar en línea y no puede estar en un estado en pausa.
La instrucción ALTER DATABASE
debe ejecutarse en modo de confirmación automática, que es el modo de administración de transacciones predeterminado. Esto se establece en la configuración de conexión.
La ALTER DATABASE
instrucción no puede formar parte de una transacción definida por el usuario.
No se puede cambiar la intercalación de la base de datos.
Ejemplos
Antes de ejecutar estos ejemplos, asegúrese de que la base de datos que está modificando no es la base de datos actual. La base de datos actual debe ser diferente de la base de datos que está modificando, por lo que ALTER debe ejecutarse mientras se está conectado a la base de datos master
.
A. Cambiar el nombre de la base de datos
ALTER DATABASE AdventureWorks2022
MODIFY NAME = Northwind;
B. Cambiar el tamaño máximo de la base de datos
ALTER DATABASE dw1 MODIFY ( MAXSIZE=10240 GB );
C. Cambiar el tamaño de proceso (objetivo de servicio)
ALTER DATABASE dw1 MODIFY ( SERVICE_OBJECTIVE= 'DW1200' );
D. Cambiar el tamaño máximo y el tamaño de proceso (objetivo de servicio)
ALTER DATABASE dw1 MODIFY ( MAXSIZE=10240 GB, SERVICE_OBJECTIVE= 'DW1200' );
Contenido relacionado
* Analytics
Platform System (PDW) *
Introducción: Sistema de la plataforma de análisis
En el sistema de la plataforma Analytics (PDW), ALTER DATABASE modifica las opciones de tamaño máximo de la base de datos para las tablas replicadas, las tablas distribuidas y el registro de transacciones. Use esta instrucción para administrar las asignaciones de espacio de disco para una base de datos a medida que aumenta o disminuye de tamaño. En este artículo también se describe la sintaxis relacionada con la configuración de las opciones de base de datos en el sistema de la plataforma Analytics (PDW).
Sintaxis
-- Analytics Platform System
ALTER DATABASE database_name
SET ( <set_database_options> | <db_encryption_option> )
[;]
<set_database_options> ::=
{
AUTOGROW = { ON | OFF }
| REPLICATED_SIZE = size [GB]
| DISTRIBUTED_SIZE = size [GB]
| LOG_SIZE = size [GB]
| SET AUTO_CREATE_STATISTICS { ON | OFF }
| SET AUTO_UPDATE_STATISTICS { ON | OFF }
| SET AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}
<db_encryption_option> ::=
ENCRYPTION { ON | OFF }
Argumentos
database_name
Nombre de la base de datos que se va a modificar. Para mostrar una lista de bases de datos en el dispositivo, use sys.databases.
AUTOGROW = { ON | OFF }
Actualiza la opción AUTOGROW. Cuando AUTOGROW es ON, Sistema de la plataforma de análisis (PDW) aumenta automáticamente el espacio asignado para las tablas replicadas, tablas distribuidas y el registro de transacciones según sea necesario para adecuarse al crecimiento de los requisitos de almacenamiento. Cuando AUTOGROW es OFF, Sistema de la plataforma de análisis (PDW) devuelve un error si las tablas replicadas, tablas distribuidas o el registro de transacciones supera la configuración de tamaño máximo.
REPLICATED_SIZE = tamaño [GB]
Especifica el nuevo tamaño máximo en gigabytes por nodo de ejecución para almacenar todas las tablas replicadas en la base de datos que se va a modificar. Si va a planear el espacio de almacenamiento del dispositivo, debe multiplicar REPLICATED_SIZE por el número de nodos de proceso del dispositivo.
DISTRIBUTED_SIZE = tamaño [GB]
Especifica el nuevo tamaño máximo en gigabytes por base de datos para almacenar todas las tablas distribuidas de la base de datos que se va a modificar. El tamaño se distribuye entre todos los nodos de ejecución en el dispositivo.
LOG_SIZE = tamaño [GB]
Especifica el nuevo tamaño máximo en gigabytes por base de datos para almacenar todas los registros de transacciones de la base de datos que se va a modificar. El tamaño se distribuye entre todos los nodos de ejecución en el dispositivo.
ENCRYPTION { ON | OFF }
Establece que se cifre (ON) o no se cifre (OFF) la base de datos. Solo se puede configurar el cifrado para Sistema de la plataforma de análisis (PDW) cuando sp_pdw_database_encryption se haya establecido en 1. Se debe crear una clave de cifrado de base de datos antes de poder configurar el cifrado de datos transparente. Para obtener más información sobre el cifrado de base de datos, consulte Cifrado de datos transparente (TDE).
SET AUTO_CREATE_STATISTICS { ON | OFF }
Cuando está activada la opción automática de creación de estadísticas, AUTO_CREATE_STATISTICS, el optimizador de consultas crea las estadísticas en columnas individuales en el predicado de consulta, según sea necesario, para mejorar las estimaciones de cardinalidad del plan de consulta. Estas estadísticas de columna única se crean en las columnas que aún no tienen un histograma en un objeto de estadísticas existente.
El valor predeterminado es ON para las bases de datos creadas después de actualizar a AU7. El valor predeterminado es OFF para las bases de datos creadas antes de la actualización.
Para obtener más información sobre las estadísticas, vea Estadísticas.
SET AUTO_UPDATE_STATISTICS { ON | OFF }
Cuando está activada la opción automática de actualización de estadísticas, AUTO_UPDATE_STATISTICS, el optimizador de consultas determina cuándo las estadísticas pueden quedar obsoletas y las actualiza cuando una consulta las usa. Las estadísticas se vuelven obsoletas después de que las operaciones de inserción, actualización, eliminación o combinación cambien la distribución de los datos en la tabla o la vista indexada. 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 valor predeterminado es ON para las bases de datos creadas después de actualizar a AU7. El valor predeterminado es OFF para las bases de datos creadas antes de la actualización.
Para obtener más información sobre las estadísticas, vea Estadísticas.
SET AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
La opción de actualización asincrónica de estadísticas AUTO_UPDATE_STATISTICS_ASYNC determina si el Optimizador de consultas utiliza actualizaciones sincrónicas o asincrónicas de las estadísticas. La opción AUTO_UPDATE_STATISTICS_ASYNC se aplica a los objetos de estadísticas creados para índices y columnas únicas de los predicados de consulta, así como a las estadísticas creadas con la instrucción CREATE STATISTICS
.
El valor predeterminado es ON para las bases de datos creadas después de actualizar a AU7. El valor predeterminado es OFF para las bases de datos creadas antes de la actualización.
Para obtener más información sobre las estadísticas, vea Estadísticas.
Permisos
Se necesita el permiso ALTER
en la base de datos.
mensajes de error
Si se deshabilitan las estadísticas automáticas e intenta modificar las estadísticas, PDW genera el error This option isn't supported in PDW
. El administrador del sistema puede habilitar las estadísticas automáticas si habilita el modificador de características AutoStatsEnabled.
Comentarios
Los valores de REPLICATED_SIZE
, DISTRIBUTED_SIZE
y LOG_SIZE
pueden ser mayor que, igual que o menor que los valores actuales de la base de datos.
Limitaciones
Las operaciones de crecimiento y reducción son aproximadas. Los tamaños reales resultantes pueden variar con respecto a los parámetros de tamaño.
Sistema de la plataforma de análisis (PDW) no ejecuta la instrucción ALTER DATABASE
como una operación atómica. Si la instrucción se anula durante la ejecución, se conservarán los cambios que ya se han producido.
La configuración de las estadísticas solo funcionará si el administrador ha habilitado las estadísticas automáticas. Si es un administrador, use el modificador de características AutoStatsEnabled para habilitar o deshabilitar las estadísticas automáticas.
Comportamiento del bloqueo
Toma un bloqueo compartido en el objeto DATABASE. No se puede modificar una base de datos que use otro usuario para leer o escribir. Esto incluye las sesiones que han emitido una instrucción USE en la base de datos.
Rendimiento
Reducir una base de datos puede consumir mucho tiempo y recursos del sistema, en función del tamaño de los datos reales en la base de datos y la cantidad de fragmentación del disco. Por ejemplo, reducir una base de datos puede tardar varias horas o más.
Determinación del progreso del cifrado
Use la consulta siguiente para determinar el progreso del cifrado de datos transparente de base de datos como un porcentaje:
WITH
database_dek AS (
SELECT ISNULL(db_map.database_id, dek.database_id) AS database_id,
dek.encryption_state, dek.percent_complete,
dek.key_algorithm, dek.key_length, dek.encryptor_thumbprint,
type
FROM sys.dm_pdw_nodes_database_encryption_keys AS dek
INNER JOIN sys.pdw_nodes_pdw_physical_databases AS node_db_map
ON dek.database_id = node_db_map.database_id
AND dek.pdw_node_id = node_db_map.pdw_node_id
LEFT JOIN sys.pdw_database_mappings AS db_map
ON node_db_map .physical_name = db_map.physical_name
INNER JOIN sys.dm_pdw_nodes nodes
ON nodes.pdw_node_id = dek.pdw_node_id
WHERE dek.encryptor_thumbprint <> 0x
),
dek_percent_complete AS (
SELECT database_dek.database_id, AVG(database_dek.percent_complete) AS percent_complete
FROM database_dek
WHERE type = 'COMPUTE'
GROUP BY database_dek.database_id
)
SELECT DB_NAME( database_dek.database_id ) AS name,
database_dek.database_id,
ISNULL(
(SELECT TOP 1 dek_encryption_state.encryption_state
FROM database_dek AS dek_encryption_state
WHERE dek_encryption_state.database_id = database_dek.database_id
ORDER BY (CASE encryption_state
WHEN 3 THEN -1
ELSE encryption_state
END) DESC), 0)
AS encryption_state,
dek_percent_complete.percent_complete,
database_dek.key_algorithm, database_dek.key_length, database_dek.encryptor_thumbprint
FROM database_dek
INNER JOIN dek_percent_complete
ON dek_percent_complete.database_id = database_dek.database_id
WHERE type = 'CONTROL';
Para obtener un ejemplo completo que muestre todos los pasos de implementación de TDE, consulte Cifrado de datos transparente (TDE).
Ejemplos: Sistema de la plataforma de análisis (PDW)
A. Modificación de la configuración de AUTOGROW
Establezca AUTOGROW en ON para la base de datos CustomerSales
.
ALTER DATABASE CustomerSales
SET ( AUTOGROW = ON );
B. Modificación del almacenamiento máximo para las tablas replicadas
En el ejemplo siguiente se establece el límite de almacenamiento de tablas replicadas en 1 GB para la base de datos CustomerSales
. Este es el límite de almacenamiento por nodo de ejecución.
ALTER DATABASE CustomerSales
SET ( REPLICATED_SIZE = 1 GB );
C. Modificación del almacenamiento máximo para las tablas distribuidas
En el ejemplo siguiente se establece el límite de almacenamiento de distribuidas replicadas en 1000 GB (un terabyte) para la base de datos CustomerSales
. Este es el límite de almacenamiento combinado en todo el dispositivo para todos los nodos de ejecución, no el límite de almacenamiento por nodo de ejecución.
ALTER DATABASE CustomerSales
SET ( DISTRIBUTED_SIZE = 1000 GB );
D. Modificación del almacenamiento máximo para el registro de transacciones
En el ejemplo siguiente se actualiza la base de datos CustomerSales
para que tenga un tamaño máximo de registro de transacciones de SQL Server de 10 GB para el dispositivo.
ALTER DATABASE CustomerSales
SET ( LOG_SIZE = 10 GB );
E. Comprobación de los valores de estadísticas actuales
La consulta siguiente devuelve los valores actuales de las estadísticas para todas las bases de datos. El valor 1
significa que la característica está activada, y 0
que está desactivada.
SELECT NAME,
is_auto_create_stats_on,
is_auto_update_stats_on,
is_auto_update_stats_async_on
FROM sys.databases;
F. Habilitación de estadísticas de creación y actualización automáticas para una base de datos
Use la instrucción siguiente para habilitar las estadísticas de creación y actualización de manera automática y asincrónica para la base de datos, CustomerSales. De esta forma, se crean y actualizan las estadísticas de columna única según sea necesario para crear planes de consulta de alta calidad.
ALTER DATABASE CustomerSales
SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE CustomerSales
SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE
SET AUTO_UPDATE_STATISTICS_ASYNC ON;
Contenido relacionado
Información general: Microsoft Fabric
Microsoft Fabric
En Microsoft Fabric Warehouse, esta instrucción modifica un almacén.
Debido a su longitud, la sintaxis ALTER DATABASE
se divide en varios artículos.
Artículo | Descripción |
---|---|
ALTER DATABASE | En el artículo actual se proporciona la sintaxis e información relacionada para cambiar el nombre y la intercalación de una base de datos. |
Opciones de ALTER DATABASE SET | Proporciona la sintaxis e información relacionada para cambiar los atributos de una base de datos usando las opciones SET de ALTER DATABASE. |
Comentarios
Actualmente, pausar la publicación de registros de Delta Lake y deshabilitar el comportamiento de pedido V en un almacenamiento son los únicos usos de ALTER DATABASE ... SET
En Microsoft Fabric. Consulte Opciones SET de ALTER DATABASE.