Compartir a través de

DBCC SHRINKDATABASE (Transact-SQL)

  • Artículo

Se aplica a: SQL Server Base de datos de Azure SQL Azure SQL Managed Instance Azure Synapse Analytics

Reduce el tamaño de los archivos de datos y de registro de la base de datos especificada.

Nota:

Las operaciones de reducción no deben considerarse una operación de mantenimiento normal. Los archivos de datos y de registro que crecen debido a operaciones empresariales periódicas y repetitivas no requieren operaciones de reducción.

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis para SQL Server:

DBCC SHRINKDATABASE   
( database_name | database_id | 0   
     [ , target_percent ]   
     [ , { NOTRUNCATE | TRUNCATEONLY } ]   
)  
[ WITH 

    {     
         [ WAIT_AT_LOW_PRIORITY 
            [ ( 
                  <wait_at_low_priority_option_list>
             )] 
         ] 
         
         [ , NO_INFOMSGS]
    }
]

< wait_at_low_priority_option_list > ::=  
    <wait_at_low_priority_option>
    | <wait_at_low_priority_option_list> , <wait_at_low_priority_option>

< wait_at_low_priority_option > ::=
  ABORT_AFTER_WAIT = { SELF | BLOCKERS }

Sintaxis para Azure Synapse Analytics:

DBCC SHRINKDATABASE
( database_name
     [ , target_percent ]
)
[ WITH NO_INFOMSGS ]

Argumentos

database_name | database_id | 0

El nombre o id. de la base de datos que se va a reducir. 0 especifica que se usa la base de datos actual.

target_percent

El porcentaje de espacio disponible que se desea dejar en el archivo de la base de datos después de reducir la base de datos.

NOTRUNCATE

Desplaza las páginas asignadas desde el final del archivo a páginas no asignadas en el principio del archivo. Esta acción compacta los datos dentro del archivo. target_percent es opcional. Azure Synapse Analytics no es compatible con esta opción.

El espacio disponible al final del archivo no se devuelve al sistema operativo, y el tamaño físico del archivo no cambia. Por tanto, parece que la base de datos no se reduce cuando se especifica NOTRUNCATE.

NOTRUNCATE solo es aplicable a archivos de datos. NOTRUNCATE no afecta al archivo de registro.

TRUNCATEONLY

Libera todo el espacio libre al final del archivo en el sistema operativo. Dentro del archivo no se mueve ninguna página. El archivo de datos se reduce solo a la última extensión asignada. Omite target_percent si se especifica con TRUNCATEONLY. Azure Synapse Analytics no es compatible con esta opción.

DBCC SHRINKDATABASE con la opción TRUNCATEONLY afecta solo al archivo de registro de transacciones de la base de datos. Para truncar el archivo de datos, use DBCC SHRINKFILE en su lugar. Para obtener más información, consulte DBCC SHRINKFILE.

WITH NO_INFOMSGS

Suprime todos los mensajes informativos con niveles de gravedad entre 0 y 10.

WAIT_AT_LOW_PRIORITY con operaciones de reducción

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores, Azure SQL Database, Azure SQL Managed Instance

La característica de espera con prioridad baja reduce la contención de bloqueo. Para obtener más información, consulte Descripción de los problemas de simultaneidad con DBCC SHRINKDATABASE.

Esta característica es similar a WAIT_AT_LOW_PRIORITY con operaciones de índice en línea, con algunas diferencias.

  • No se puede especificar la opción ABORT_AFTER_WAIT como NONE.

WAIT_AT_LOW_PRIORITY

Cuando se ejecuta un comando de reducción en modo WAIT_AT_LOW_PRIORITY, la operación de reducción en espera no bloquea las nuevas consultas que requieran bloqueos de estabilidad del esquema (Sch-S) hasta que la operación de reducción deje de esperar y comience a ejecutarse. La operación de reducción se ejecutará cuando pueda obtener un bloqueo de modificación de esquema (Sch-M). Si una nueva operación de reducción en modo WAIT_AT_LOW_PRIORITY no puede obtener un bloqueo debido a una consulta de larga duración, dicha operación acabará por agotar el tiempo de espera transcurrido un minuto de manera predeterminada y se cerrará sin errores.

Si una nueva operación de reducción en modo WAIT_AT_LOW_PRIORITY no puede obtener un bloqueo debido a una consulta de larga duración, dicha operación acabará por agotar el tiempo de espera transcurrido un minuto de manera predeterminada y se cerrará sin errores. Esto ocurrirá si la operación de reducción no puede obtener el bloqueo Sch-M debido a la existencia de consultas simultáneas que contienen bloqueos Sch-S. Cuando se agote el tiempo de espera, se enviará un mensaje de error 49516 al registro de errores de SQL Server; por ejemplo, Msg 49516, Level 16, State 1, Line 134 Shrink timeout waiting to acquire schema modify lock in WLP mode to process IAM pageID 1:2865 on database ID 5. En este momento, puede limitarse a reintentar la operación de reducción en modo WAIT_AT_LOW_PRIORITY con la seguridad de que no habrá ningún impacto en la aplicación.

ABORT_AFTER_WAIT = [ SELF | BLOCKERS ]

  • SELF

    SELF es la opción predeterminada. Cierra la operación de reducción de base de datos que se está ejecutando actualmente sin realizar ninguna acción.

  • BLOCKERS

    Elimina todas las transacciones de usuario que bloquean la operación de reducción de base de datos, de forma que dicha operación pueda continuar. La opción BLOCKERS requiere que el inicio de sesión tenga el permiso ALTER ANY CONNECTION.

Conjunto de resultados

En la tabla siguiente se describen las columnas del conjunto de resultados.

Nombre de la columna Descripción
DbId Número de identificación de la base de datos del archivo que el Motor de base de datos intentó reducir.
FileId Número de identificación del archivo que el Motor de base de datos intentó reducir.
CurrentSize El número de páginas de 8 KB que el archivo ocupa actualmente.
MinimumSize El número de páginas de 8 KB que el archivo podría ocupar, como mínimo. Este valor se corresponde al tamaño mínimo o tamaño de creación original de un archivo.
UsedPages El número de páginas de 8 KB que utiliza actualmente el archivo.
EstimatedPages El número de páginas de 8 KB al que el Motor de base de datos estima que se puede reducir el archivo.

Nota

El Motor de base de datos no presenta filas para los archivos que no se reducen.

Comentarios

Para reducir todos los archivos de datos y de registro de una base de datos específica, ejecute el comando DBCC SHRINKDATABASE. Para reducir un archivo de datos o de registro de cada vez para una base de datos específica, ejecute el comando DBCC SHRINKFILE.

Para ver la cantidad actual de espacio disponible (sin asignar) en la base de datos, ejecute sp_spaceused.

Las operaciones DBCC SHRINKDATABASE se pueden detener en cualquier momento del proceso y se conserva el trabajo completado hasta ese momento.

El tamaño de la base de datos no puede ser menor que el tamaño mínimo configurado de la base de datos. El tamaño mínimo se especifica cuando se crea originalmente la base de datos. O bien, el tamaño mínimo puede ser el último tamaño establecido de forma explícita mediante un operación de cambio de tamaño de archivo. Las operaciones como DBCC SHRINKFILE o ALTER DATABASE son ejemplos de operaciones de cambio de tamaño de archivo.

Considere que una base de datos se crea originalmente con un tamaño de 10 MB. Después, aumenta a 100 MB. El tamaño mínimo al que se puede reducir la base de datos es de 10 MB, incluso si se han eliminado todos los datos de la base de datos.

Especifique la opción NOTRUNCATE o TRUNCATEONLY cuando ejecute DBCC SHRINKDATABASE. Si no lo hace, el resultado será el mismo que si ejecuta una operación DBCC SHRINKDATABASE con NOTRUNCATE seguido de ejecutar una operación DBCC SHRINKDATABASE con TRUNCATEONLY.

No es necesario que la base de datos reducida esté en modo de usuario único. Otros usuarios pueden estar trabajando en la base de datos cuando se reduce, incluidas las bases de datos del sistema.

No se puede reducir una base de datos mientras se está realizando una copia de seguridad de la misma. Por el contrario, no se puede realizar una copia de seguridad de una base de datos mientras se está realizando una operación de reducción en ella.

Cuando se especifica con WAIT_AT_LOW_PRIORITY, la solicitud de bloqueo Sch-M de la operación de reducción esperará con prioridad baja al ejecutar el comando durante 1 minuto. Si se bloquea la operación durante ese tiempo, se ejecutará la acción ABORT_AFTER_WAIT especificada.

En los grupos de SQL de Azure Synapse, no se recomienda ejecutar un comando shrink, ya que se trata de una operación intensiva de E/S y puede desconectar el grupo de SQL dedicado (anteriormente SQL DW). Además, después de ejecutar este comando habrá implicaciones económicas para sus instantáneas del almacén de datos.

Las operaciones de reducción de bases de datos y archivos se encuentran actualmente en versión preliminar para Azure SQL Database Hyperscale. Para obtener más información sobre la versión preliminar, consulte Reducción para Azure SQL Database Hyperscale.

Problemas conocidos

Se aplica a: SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics (grupo de SQL dedicado)

  • Actualmente, las columnas que usan tipos de datos LOB (varbinary(max), varchar(max)y nvarchar(max)) en segmentos de almacén de columnas comprimidos no se ven afectados por DBCC SHRINKDATABASE y DBCC SHRINKFILE.

Cómo funciona DBCC SHRINKDATABASE

DBCC SHRINKDATABASE reduce los archivos de datos de uno en uno, pero reduce los archivos de registro como si todos estuvieran en una agrupación de registros contiguos. Los archivos se reducen siempre desde el final.

Suponga que tiene un par de archivos de registro, un archivo de datos y una base de datos denominada mydb. Los archivos de datos y de registro tienen 10 MB cada uno, y el archivo de datos contiene 6 MB de datos. El Motor de base de datos calcula un tamaño de destino para cada archivo. Este valor es el tamaño al que se va a reducir el archivo. Cuando DBCC SHRINKDATABASE se especifica con target_percent, el Motor de base de datos calcula el tamaño final para que quede target_percent de espacio disponible en el archivo tras la reducción.

Por ejemplo, si establece el valor de target_percent en 25 para reducir mydb, el Motor de base de datos calcula que el tamaño de destino del archivo de datos será de 8 MB (6 MB de datos más 2 MB de espacio disponible). Por tanto, el Motor de base de datos pasa los datos de los últimos 2 MB del archivo de datos al espacio disponible de los primeros 8 MB del archivo de datos y, después, reduce el archivo.

Suponga que el archivo de datos de mydb contiene 7 MB de datos. Si se especifica un target_percent de 30, este archivo de datos se puede reducir al porcentaje libre de 30. Sin embargo, especificar un target_percent de 40 no reduce el archivo de datos porque no se puede crear suficiente espacio libre en el tamaño total actual del archivo de datos.

O lo que es lo mismo: 40 % de espacio disponible deseado + 70 % de datos en el archivo (7 MB de 10 MB) es mayor que 100 %. Cualquier valor de target_percent mayor que 30 no reducirá el archivo de datos. No se reducirá porque el porcentaje de espacio disponible que quiere y el porcentaje actual que ocupa el archivo de datos es más del 100 %.

En los archivos de registro, el Motor de base de datos usa target_percent para calcular el tamaño de destino completo del registro. Por ese motivo, target_percent es la cantidad de espacio libre en el registro después de la operación de reducción. El tamaño final del registro entero se traduce al tamaño final de cada archivo de registro.

DBCC SHRINKDATABASE intenta reducir cualquier archivo de registro físico a su tamaño final de forma inmediata. Supongamos que ningún elemento del registro lógico permanece en los registros virtuales más allá del tamaño de destino del archivo de registro. Después, el archivo se trunca correctamente y DBCC SHRINKDATABASE finaliza sin mensajes. Pero si parte del registro lógico permanece en los registros virtuales más allá del tamaño final, el Motor de base de datos libera tanto espacio como sea posible y después emite un mensaje informativo. El mensaje indica las acciones que se deben llevar a cabo para mover el registro lógico de los registros virtuales al final del archivo. Una vez se han ejecutado estas acciones, se puede usar DBCC SHRINKDATABASE para liberar el espacio restante.

Un archivo de registro solo se puede reducir a un límite de archivo de registro virtual. Por ese motivo, puede que no sea posible reducir un archivo de registro a un tamaño menor que el de un archivo de registro virtual. Puede que no sea posible incluso si no se está usando. El Motor de base de datos elige dinámicamente el tamaño del archivo de registro virtual cuando se crean o se extienden archivos de registro.

Descripción de los problemas de simultaneidad con DBCC SHRINKDATABASE

Los comandos para reducir bases de datos y reducir archivos pueden provocar problemas de simultaneidad, sobre todo con el mantenimiento activo, como la recompilación de índices, o en entornos OLTP ocupados. Cuando la aplicación ejecuta consultas en tablas de base de datos, estas consultas adquirirán y mantendrán un bloqueo de estabilidad de esquema (Sch-S) hasta que las consultas completen sus operaciones. Cuando intentan reclamar espacio durante el uso normal, las operaciones para reducir bases de datos y archivos requieren actualmente un bloqueo de modificación de esquema (Sch-M) al mover o eliminar páginas del Mapa de asignación de índices (IAM), lo que impide los bloqueos Sch-S que requieren las consultas de usuario. Como resultado, las consultas de larga duración bloquearán una operación de reducción hasta que se completen las consultas. Esto significa que las nuevas consultas que requieran bloqueos Sch-S también se pondrán en cola detrás de la operación de reducción en espera y también se bloquearán, lo que agrava aún más este problema de simultaneidad. Esto puede afectar significativamente al rendimiento de las consultas de la aplicación y también causará dificultades para completar el mantenimiento necesario para reducir los archivos de base de datos. Introducida en SQL Server 2022 (16.x), la característica de espera de reducción con prioridad baja (WLP) adopta un bloqueo de modificación de esquema en modo WAIT_AT_LOW_PRIORITY para abordar este problema. Para obtener más información, consulte WAIT_AT_LOW_PRIORITY con operaciones de reducción.

Para obtener más información sobre los bloqueos Sch-S y Sch-M, consulte la Guía de versiones de fila y bloqueo de transacciones.

Procedimientos recomendados

Tenga en cuenta la siguiente información cuando vaya a reducir una base de datos:

  • Una reducción es más efectiva después de una operación que cree espacio sin usar, como por ejemplo una operación para truncar o eliminar tablas.
  • La mayoría de las bases de datos requieren que haya espacio disponible para realizar las operaciones diarias normales. Si reduce un archivo de base de datos de forma reiterada y advierte que su tamaño vuelve a aumentar, esto es señal de que el espacio disponible es necesario para las operaciones normales. En estos casos, reducir el archivo de base de datos reiteradamente no sirve para nada. Los eventos de crecimiento automático necesarios para aumentar los archivos de base de datos perjudican al rendimiento.
  • La reducción no mantiene el estado de fragmentación de los índices de la base de datos y generalmente aumenta la fragmentación hasta cierto punto. Este resultado es otra razón para no reducir la base de datos de forma repetida.
  • A menos que tenga un requisito específico, no establezca la opción de base de datos AUTO_SHRINK en ON.

Solución de problemas

Es posible bloquear las operaciones de reducción mediante una transacción que se ejecuta con un nivel de aislamiento basado en versiones de fila. Por ejemplo, una operación de eliminación grande que se ejecuta con un nivel de aislamiento basado en versiones de fila está en curso cuando se ejecuta una operación DBCC SHRINKDATABASE. Cuando se produce esta situación, la operación de reducción esperará a que finalice la operación de eliminación antes de continuar. Cuando la operación de reducción está en espera, las operaciones DBCC SHRINKFILE y DBCC SHRINKDATABASE imprimen un mensaje informativo (5202 para SHRINKDATABASE y 5203 para SHRINKFILE). Este mensaje se imprime en el registro de errores de SQL Server cada cinco minutos durante la primera hora y, después, cada hora posterior. Por ejemplo, si el registro de errores contiene el siguiente mensaje de error:

DBCC SHRINKDATABASE for database ID 9 is waiting for the snapshot
transaction with timestamp 15 and other snapshot transactions linked to
timestamp 15 or with timestamps older than 109 to finish.

Este error significa que las transacciones de instantánea que tienen marcas de tiempo anteriores a 109 bloquearán la operación de reducción. Esa transacción es la última que ha completado la operación de reducción. También indica que las columnas transaction_sequence_num o first_snapshot_sequence_num de la vista de administración dinámica sys.dm_tran_active_snapshot_database_transactions (Transact-SQL) contienen un valor de 15. Es posible que las columnas transaction_sequence_num o first_snapshot_sequence_num de la vista contengan un número inferior al de la última transacción completada mediante una operación de reducción (109). En ese caso, la operación de reducción esperará a que esas transacciones finalicen.

Para resolver el problema, puede llevar a cabo una de las tareas siguientes:

  • Finalizar la transacción que bloquea la operación de reducción
  • Finalizar la operación de reducción Se conserva todo el trabajo completado.
  • No hacer nada y permitir que la operación de reducción espere a que finalice la transacción que la está bloqueando.

Permisos

Debe pertenecer al rol fijo de servidor sysadmin o al rol fijo de base de datos db_owner .

Ejemplos

A. Reducir una base de datos y especificar un porcentaje de espacio disponible

En el ejemplo siguiente se reduce el tamaño de los archivos de datos y de registro de la base de datos de usuario UserDB para dejar un 10 % de espacio disponible en la base de datos.

DBCC SHRINKDATABASE (UserDB, 10);
GO

B. Truncar una base de datos

En el ejemplo siguiente se reducen los archivos de datos y de registro de la base de datos de ejemplo AdventureWorks2022 al último tamaño asignado.

DBCC SHRINKDATABASE (AdventureWorks2022, TRUNCATEONLY);

C. Reducir una base de datos de Azure Synapse Analytics

DBCC SHRINKDATABASE (database_A);
DBCC SHRINKDATABASE (database_B, 10);

D. Reducir una base de datos con WAIT_AT_LOW_PRIORITY

En el ejemplo siguiente se intenta reducir el tamaño de los archivos de datos y de registro de la base de datos AdventureWorks2022 para dejar un 20 % de espacio disponible en la base de datos. Si no se puede obtener un bloqueo en un minuto, se anulará la operación de reducción.

DBCC SHRINKDATABASE ([AdventureWorks2022], 20) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

Contenido relacionado