DBCC SHRINKFILE (Transact-SQL)

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Reduce el tamaño especificado de los datos o del archivo de registro de la base de datos actual. Puede usarlo para mover datos de un archivo a otros del mismo grupo, lo que vacía el archivo y permite la eliminación de su base de datos. Puede reducir un archivo a menos de su tamaño de creación y restablecer el tamaño mínimo de archivo al nuevo valor.

Convenciones de sintaxis de Transact-SQL

Sintaxis

DBCC SHRINKFILE   
(  
    { file_name | file_id }   
    { [ , EMPTYFILE ]   
    | [ [ , target_size ] [ , { 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 }

Nota:

Para ver la sintaxis de Transact-SQL para SQL Server 2014 y versiones anteriores, consulte Versiones anteriores de la documentación.

Argumentos

file_name

Nombre lógico del archivo que se va a reducir.

file_id

Número de identificación (Id.) del archivo que se va a reducir. Para obtener un identificador de archivo, use la función del sistema FILE_IDEX o consulte la vista de catálogo sys.database_files en la base de datos actual.

target_size

Entero que representa el nuevo tamaño del archivo en megabytes. Si no se especifica o es 0, DBCC SHRINKFILE se reduce hasta el tamaño de creación del archivo.

Puede reducir el tamaño predeterminado de un archivo vacío mediante DBCC SHRINKFILE <target_size>. Por ejemplo, si crea un archivo de 5 MB y, a continuación, reduce el archivo a 3 MB mientras el archivo todavía está vacío, el tamaño de archivo predeterminado se establece en 3 MB. Esto solo se aplica para vaciar archivos que nunca han contenido datos.

Esta opción no se admite para los contenedores del grupo de archivos FILESTREAM.

Si se especifica, DBCC SHRINKFILE intenta reducir el archivo a target_size. Las páginas usadas en el área del archivo que se va a liberar se mueven al espacio disponible en las áreas conservadas del archivo. Por ejemplo, con un archivo de datos de 10 MB, una operación DBCC SHRINKFILE con un valor de target_size de 8 mueve todas las páginas usadas en los 2 últimos MB del archivo a cualquier página sin asignar de los primeros 8 MB del archivo. DBCC SHRINKFILE no reduce un archivo más allá del tamaño necesario de los datos almacenados. Por ejemplo, si se usan 7 MB de un archivo de datos de 10 MB, una instrucción DBCC SHRINKFILE con un parámetro target_size de 6 reduce el archivo a 7 MB, no 6 MB.

EMPTYFILE

Migra todos los datos del archivo especificado a otros archivos del mismo grupo de archivos. Es decir, EMPTYFILE migra los datos de un archivo especificado a otros archivos del mismo grupo de archivos. EMPTYFILE garantiza que no se agregan nuevos datos al archivo, a pesar de que este archivo no sea de solo lectura. Puede usar la instrucción ALTER DATABASE para quitar un archivo. Si usa la instrucción ALTER DATABASE para cambiar el tamaño del archivo, la marca de solo lectura se restablece y se pueden agregar datos.

Para los contenedores de grupo de archivos de FILESTREAM, no puede usar ALTER DATABASE para quitar un archivo hasta que el recolector de elementos no utilizados de FILESTREAM haya ejecutado y eliminado todos los grupos de archivos innecesarios que EMPTYFILE ha copiado a otro contenedor. Para más información, consulte sp_filestream_force_garbage_collection. Para más información sobre cómo quitar un contenedor de FILESTREAM, vea la sección correspondiente en Opciones File y Filegroup de ALTER DATABASE (Transact-SQL)

NOTRUNCATE

Mueve las páginas asignadas desde el final de un archivo de datos hasta las páginas sin asignar de la parte delantera del archivo con o sin la especificación de target_percent. 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, si se especifica NOTRUNCATE, parecerá que el archivo no se reduce.

NOTRUNCATE solo es aplicable a archivos de datos. No afecta a los archivos de registro.

Esta opción no se admite para los contenedores del grupo de archivos FILESTREAM.

TRUNCATEONLY

Libera al sistema operativo todo el espacio disponible al final del archivo, pero no realiza ningún movimiento de página dentro del archivo. El archivo de datos solo se reduce hasta el último tamaño asignado.

target_size se omite si se especifica con TRUNCATEONLY.

La opción TRUNCATEONLY no mueve la información en el registro, pero quita VLF inactivos del final del archivo de registro. Esta opción no se admite para los contenedores del grupo de archivos FILESTREAM.

WITH NO_INFOMSGS

Suprime todos los mensajes de información.

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

Se aplica a: SQL Server (SQL Server 2022 [16.x] y versiones posteriores) y Azure SQL Database.

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á de manera silenciosa.

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á de manera silenciosa. 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 punto, 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 ]

Se aplica a: SQL Server (SQL Server 2022 [16.x] y versiones posteriores) y Azure SQL Database.

  • SELF

    Cierra la operación de reducción de archivo 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 archivo, de forma que dicha operación pueda continuar. Para la opción BLOCKERS, es necesario que el inicio de sesión tenga el permiso ALTER ANY CONNECTION.

Conjuntos de resultados

En la siguiente tabla se describen las columnas de 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 número 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.

Comentarios

DBCC SHRINKFILE se aplica a los archivos de la base de datos actual. Para más información sobre cómo cambiar la base de datos actual, vea USE (Transact-SQL).

Puede detener las operaciones DBCC SHRINKFILE en cualquier momento sin que por ello se pierda el trabajo ya completado. Si usa el parámetro EMPTYFILE y cancela la operación, el archivo no se marca para evitar que se agreguen datos adicionales.

Cuando una operación DBCC SHRINKFILE no es correcta, se genera un error.

Otros usuarios pueden trabajar en la base de datos durante la reducción de archivos; la base de datos no tiene que estar en modo de usuario único. No es necesario ejecutar la instancia de SQL Server en modo de usuario único para reducir las bases de datos del sistema.

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.

Descripción de los problemas de simultaneidad con DBCC SHRINKFILE

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

Reducción de un archivo de registro

En los archivos de registro, Motor de base de datos usa target_size para calcular el tamaño de destino completo del registro. Por lo tanto, target_size es el espacio disponible del registro después de la operación de reducción. El tamaño de destino completo del registro se convierte en el tamaño de destino de cada archivo de registro. DBCC SHRINKFILE intenta reducir cualquier archivo de registro físico a su tamaño final de forma inmediata. Sin embargo, si parte del registro lógico está en los registros virtuales más allá del tamaño final, el Motor de base de datos libera tanto espacio como sea posible y a continuación 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 realizadas estas acciones, se puede usar DBCC SHRINKFILE para liberar el espacio restante.

Como un archivo de registro solo puede reducirse hasta el límite de un archivo de registro virtual, puede que no sea posible reducirlo a un tamaño menor que el de un archivo de registro virtual, aunque no esté siendo utilizado. Motor de base de datos elige dinámicamente el tamaño del registro de archivo virtual cuando se crean o extienden los archivos de registro.

Procedimientos recomendados

Tenga en cuenta la siguiente información cuando vaya a reducir un archivo:

  • Una reducción es más efectiva después de una operación con la que se crea mucho espacio no utilizado, 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 el archivo 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. Esta fragmentación es otra razón para no reducir la base de datos reiteradamente.

  • Reduzca varios archivos en la misma base de datos de forma secuencial en lugar de simultáneamente. La contención en las tablas del sistema puede provocar bloqueo y conducir a retrasos.

Solución de problemas

En esta sección se describe el modo de diagnosticar y corregir los problemas que pueden ocurrir al ejecutar el comando DBCC SHRINKFILE.

El archivo no se reduce.

Si el tamaño del archivo no cambia después de una operación de reducción sin errores, pruebe lo siguiente para comprobar que el archivo tiene suficiente espacio libre:

  • Ejecute la siguiente consulta.
SELECT name
    , size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS AvailableSpaceInMB
FROM sys.database_files;
  • Ejecute el comando DBCC SQLPERF para devolver el espacio ocupado en el registro de transacciones.

Si no hay suficiente espacio disponible, la operación de reducción no puede reducir más el tamaño del archivo.

Normalmente es el archivo de registro el que parece no reducirse. Esta falta de reducción suele ser el resultado de un archivo de registro que no se ha truncado. Para truncar el registro, puede establecer el modelo de recuperación de la base de datos en SIMPLE o realizar una copia de seguridad del registro y ejecutar luego de nuevo la operación DBCC SHRINKFILE.

La operación de reducción está bloqueada

Una transacción que se ejecuta con un nivel de aislamiento basado en las versiones de fila puede bloquear las operaciones de reducción. Por ejemplo, si se está ejecutando una operación de eliminación grande con un nivel de aislamiento basado en versiones de fila cuando se ejecuta una operación DBCC SHRINKDATABASE, la operación de reducción espera a que la operación de eliminación finalice antes de continuar. Cuando ocurre este bloqueo, las operaciones DBCC SHRINKFILE y DBCC SHRINKDATABASE imprimen un mensaje informativo (5202 para SHRINKDATABASE y 5203 para SHRINKFILE) en el registro de errores de SQL Server. Este mensaje se registra cada cinco minutos durante la primera hora y luego cada hora. Por ejemplo, si el registro de errores contiene el siguiente mensaje de error, se producirá el siguiente error:

DBCC SHRINKFILE for file ID 1 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 mensaje significa que las transacciones de instantáneas con marcas de tiempo anteriores a 109 (la última transacción que realizó la operación de reducción) están bloqueando 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 contienen el valor 15. Si las columnas de vista transaction_sequence_num o first_snapshot_sequence_num contienen un número inferior al de la última transacción realizada mediante una operación de reducción (109), la operación de reducción espera 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 Si finaliza la operación de reducción, se mantiene 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 un archivo de datos a un tamaño final especificado

En el siguiente ejemplo se reduce el tamaño de un archivo de datos denominado DataFile1 de la base de datos de usuario UserDB a 7 MB.

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

B. Reducir un archivo de registro a un tamaño final especificado

En el ejemplo siguiente se reduce el archivo de registro de la base de datos AdventureWorks2022 a 1 MB. Para permitir que el comando DBCC SHRINKFILE reduzca el archivo, primero hay que truncarlo estableciendo el modelo de recuperación de la base de datos en SIMPLE.

USE AdventureWorks2022;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2022
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2022_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2022
SET RECOVERY FULL;
GO

C. Truncar un archivo de datos

En el ejemplo siguiente se trunca el archivo de datos principal en la base de datos AdventureWorks2022. Se consulta la vista de catálogo sys.database_files para obtener el valor de file_id del archivo de datos.

USE AdventureWorks2022;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);

D. Vaciado de un archivo

En el ejemplo siguiente se ilustra cómo vaciar un archivo de forma que se pueda quitar de la base de datos. Para los fines de este ejemplo, primero se crea un archivo de datos que contiene datos.

USE AdventureWorks2022;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2022
ADD FILE (
    NAME = Test1data,
    FILENAME = 'C:\t1data.ndf',
    SIZE = 5MB
    );
GO
-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2022
REMOVE FILE Test1data;
GO

E. Reducir un archivo de base de datos con WAIT_AT_LOW_PRIORITY

En el siguiente ejemplo se intenta reducir el tamaño de un archivo de datos en la base de datos de usuario actual a 1 MB. Se consulta la vista de catálogo sys.database_files para obtener el valor de file_id del archivo de datos, que en este ejemplo es file_id 5. Si no se puede obtener un bloqueo en un minuto, se anulará la operación de reducción.

USE AdventureWorks2022;
GO

SELECT file_id, name
FROM sys.database_files;
GO

DBCC SHRINKFILE (5, 1) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

Consulte también

Pasos siguientes