Administrar el tamaño del archivo de registro de transacciones

  • Artículo

Se aplica a:SQL Server

En este artículo se incluye información sobre cómo supervisar el tamaño de un registro de transacciones de SQL Server, reducir el registro de transacciones, agregar o ampliar un archivo de registro de transacciones, optimizar la tasa de crecimiento del registro de transacciones de tempdb y controlar el crecimiento de un archivo de registro de transacciones.

Este artículo se aplica a SQL Server. Aunque es muy similar, para obtener información sobre cómo administrar el tamaño de los archivos de registro de transacciones en Azure SQL Managed Instance, consulte Administración del espacio de archivos para las bases de datos en Azure SQL Managed Instance. Para obtener más información sobre Azure SQL Database, consulte Administración de espacios de archivos en Azure SQL Database.

Descripción de los tipos de espacio de almacenamiento para una base de datos

Comprender las cantidades de espacio de almacenamiento siguientes es importante para administrar el espacio de archivo de una base de datos.

Cantidad de base de datos Definición Comentarios
Espacio de datos usado La cantidad de espacio usado para almacenar los datos de la base de datos. Por lo general, el espacio usado aumenta (disminuciones) en las inserciones (eliminaciones). En algunos casos, el espacio usado no cambia en las inserciones o eliminaciones, según la cantidad y el patrón de datos implicados en la operación y las posibles fragmentaciones. Por ejemplo, al eliminar una fila de cada página de datos no disminuye necesariamente el espacio usado.
Espacio de datos asignado La cantidad de espacio de archivo de formato disponible para almacenar datos de la base de datos. La cantidad de espacio asignado crece automáticamente, pero nunca disminuye después de las eliminaciones. Este comportamiento garantiza que las futuras inserciones son más rápidas puesto que no es necesario volver a formatear el espacio.
Espacio de datos asignado, pero no usado La diferencia entre la cantidad de espacio de datos asignado y el espacio de datos usado. Esta cantidad representa la cantidad máxima de espacio libre que se puede reclamar mediante la reducción de archivos de datos de base de datos.
Tamaño máximo de datos La cantidad máxima de espacio que se puede usar para almacenar datos de base de datos. La cantidad de espacio de datos asignado no puede crecer por encima del tamaño máximo de datos.

En el siguiente diagrama se ilustra la relación entre los diferentes tipos de espacio de almacenamiento para una base de datos.

Diagram that demonstrates the size of difference database space concepts in the database quantity table.

Consulta de la información de espacio de archivos en una base de datos única

Use la siguiente consulta para devolver la cantidad de espacio de archivos de base de datos asignado y la cantidad de espacio asignado sin usar. Las unidades de resultado de la consulta están en MB.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

Supervisión del uso del espacio del registro

Supervise el uso del espacio del registro mediante sys.dm_db_log_space_usage. Este DMV devuelve información sobre la cantidad de espacio del registro actualmente en uso e indica cuándo es necesario el truncamiento del registro de transacciones.

Para obtener información sobre el tamaño actual del archivo de registro, su tamaño máximo y la opción de crecimiento automático de este archivo, también puede usar las columnas size, max_size y growth de ese archivo de registro en sys.database_files.

Importante

Evite la sobrecarga del disco del registro. Asegúrese de que el almacenamiento del registro puede soportar la IOPS y los requisitos de latencia baja para la carga de transacciones.

Reducción del tamaño del archivo de registro

Para reducir el tamaño físico de un archivo de registro físico devolviendo espacio libre en el archivo al sistema operativo, reduzca el archivo de registro. Una reducción solo marca la diferencia cuando un archivo de registro de transacciones contiene espacio sin usar.

Si el archivo de registro está lleno, probablemente debido a transacciones abiertas, investigue qué impide el truncamiento del registro de transacciones.

Precaución

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. Los comandos de reducción afectan al rendimiento de la base de datos mientras se está ejecutando y, si es posible, se deben ejecutar durante períodos de poco uso. No se recomienda reducir los archivos de datos si la carga de trabajo normal de la aplicación hará que los archivos vuelvan a tener el mismo tamaño asignado.

Debe tener en cuenta el posible impacto negativo en el rendimiento por la reducción de los archivos de base de datos; consulte la sección Mantenimiento de índices después de la reducción.

Antes de reducir el registro de transacciones, tenga en cuenta los Factores que pueden ralentizar el truncamiento del registro. Si se requiere el espacio de almacenamiento de nuevo después de reducir un registro, el registro de transacciones volverá a crecer y esto implicará una sobrecarga de rendimiento durante las operaciones de ampliación de registro. Para más información, vea las Recomendaciones.

Puede reducir un archivo de registro siempre que la base de datos esté en línea y haya al menos un archivo de registro virtual (VLF) libre. En algunos casos, no será posible reducir el registro hasta el siguiente truncamiento del registro.

Los factores que mantienen activos los VLF por un periodo prolongado de tiempo, como puede ser una transacción de ejecución prolongada, pueden restringir la reducción del registro o incluso impedirla completamente. Para obtener información, vea Factores que pueden ralentizar el truncamiento del registro.

Con la reducción de un archivo de registro se quitan uno o varios VLF que no contienen ninguna parte del registro lógico (es decir, los VLF inactivos). Cuando se reduce un archivo de registro de transacciones, se quitan VLF inactivos del final del archivo de registro para reducirlo aproximadamente al tamaño de destino.

Para obtener más información sobre operaciones de reducción, revise los vínculos siguientes:

Reducir un archivo de registro (sin reducir los archivos de base de datos)

Supervisar los eventos de reducción de un archivo de registro

Supervisar el espacio del registro

Mantenimiento de índices después de la reducción

Una vez completada una operación de reducción en los archivos de datos, es posible que los índices se fragmenten. Esto reduce la eficacia de optimización del rendimiento para determinadas cargas de trabajo, como consultas que usan exploraciones grandes. Si se produce una degradación del rendimiento una vez completada la operación de reducción, considere la posibilidad de realizar el mantenimiento de índices para volver a generar los índices. Tenga en cuenta que las recompilaciones de índice requieren espacio libre en la base de datos y, por lo tanto, pueden hacer que el espacio asignado aumente, lo que contrarresta el efecto de la reducción.

Para obtener más información sobre el mantenimiento de índices, consulte Optimización del mantenimiento de índices para mejorar el rendimiento de las consultas y reducir el consumo de recursos.

Agregar o ampliar un archivo de registro

Puede obtener espacio al ampliar el archivo de registro existente (si el espacio en disco lo permite) o al agregar un archivo de registro a la base de datos, normalmente en otro disco. Un archivo de registro de transacciones es suficiente, a menos que se esté agotando el espacio del registro y que el espacio en disco también se esté agotando en el volumen que contiene el archivo de registro.

  • Para agregar un archivo de registro a la base de datos, use la cláusula ADD LOG FILE de la instrucción ALTER DATABASE. El hecho de agregar un archivo de registro permite que crezca el existente.
  • Para aumentar el archivo de registro, use la cláusula MODIFY FILE de la instrucción ALTER DATABASE, especificando la sintaxis de SIZE y MAXSIZE. Para obtener más información, consulte Opciones File y Filegroup de ALTER DATABASE (Transact-SQL).

Para más información, vea las Recomendaciones.

Optimizar el tamaño del registro de transacciones tempdb

Al reiniciar una instancia del servidor se devuelve el tamaño del registro de transacciones de la base de datos tempdb a su tamaño original, antes del crecimiento automático. Esto puede reducir el rendimiento del registro de transacciones de tempdb.

Para evitar esta sobrecarga, aumente el tamaño del registro de transacciones de tempdb después de iniciar o reiniciar la instancia de servidor. Para obtener más información, consulte tempdb Database.

Controlar el crecimiento de un archivo de registro de transacciones

Use las opciones File y Filegroup de la instrucción ALTER DATABASE (Transact-SQL) para administrar el crecimiento de un archivo de registro de transacciones. Tener en cuenta lo siguiente:

  • Para cambiar el tamaño del archivo actual en unidades de KB, MB, GB y TB, use la opción SIZE.
  • Para cambiar el incremento de crecimiento, use la opción FILEGROWTH. El valor 0 indica que el aumento automático se establece en OFF y no se permite ningún espacio adicional.
  • Para controlar el máximo el tamaño de un archivo de registro en unidades de KB, MB, GB y TB, o establecer el crecimiento en UNLIMITED, use la opción MAXSIZE.

Para más información, vea las Recomendaciones.

Recomendaciones

Estas son algunas recomendaciones generales referentes a los archivos de registro de transacciones:

  • El incremento de crecimiento automático del registro de transacciones, según lo establecido por la opción FILEGROWTH, debe ser lo suficientemente grande como para anticiparse a las necesidades de las transacciones de la carga de trabajo. El incremento del crecimiento de un archivo de registro debe ser lo suficientemente grande para evitar una expansión frecuente. Un buen punto de referencia para ajustar correctamente el tamaño de un registro de transacciones es supervisar la cantidad de registro ocupada durante:

    • El tiempo necesario para ejecutar una copia de seguridad completa, porque no se pueden realizar copias de seguridad del registro hasta que termine.
    • El tiempo necesario para las operaciones de mantenimiento de índice más grandes.
    • El tiempo necesario para ejecutar el lote más grande de una base de datos.

  • Al establecer crecimiento automático para archivos de datos y de registro mediante la opción FILEGROWTH, es recomendable establecerlo en tamaño en lugar de en porcentaje. De esta forma, se permite un mejor control en la proporción de crecimiento, ya que los porcentajes son una unidad de medida sin límite de crecimiento.

    • En versiones anteriores a SQL Server 2022 (16.x), los registros de transacciones no pueden usar la inicialización instantánea de archivos, por lo que el tiempo de crecimiento prolongado de los registros es especialmente crítico.

    • A partir de SQL Server 2022 (16.x) (todas las ediciones) y en Azure SQL Database, la inicialización instantánea de archivos puede beneficiar a los eventos de crecimiento del registro de transacciones de hasta 64 MB. El incremento de tamaño de crecimiento automático predeterminado para las bases de datos nuevas es de 64 MB. Los eventos de crecimiento automático del archivo de registro de transacciones mayores de 64 MB no pueden beneficiarse de la inicialización instantánea de archivos.

    • Como práctica recomendada, no establezca el valor de la opción FILEGROWTH por encima de 1024 MB para registros de transacciones. Los valores predeterminados de la opción FILEGROWTH son los siguientes:

      Versión Valores predeterminados
      A partir de SQL Server 2016 (13.x) Datos: 64 MB. Archivos de registro: 64 MB.
      A partir de SQL Server 2005 (9.x) Datos: 1 MB. Archivos de registro: 10 %.
      Antes de SQL Server 2005 (9.x) Datos: 10 %. Archivos de registro: 10 %.

  • Un aumento de crecimiento automático pequeño puede generar demasiados VLF pequeños y puede reducir el rendimiento. Para determinar la distribución óptima de VLF para el tamaño de registro de transacciones actual de todas las bases de datos en una instancia determinada, así como los incrementos de tamaño necesarios para conseguir el tamaño requerido, consulte este script para analizar y corregir VLF proporcionado por SQL Tiger Team.

  • Un incremento de crecimiento automático grande puede causar dos problemas:

    • Un incremento de crecimiento automático grande puede hacer que la base de datos se detenga mientras se asigna el nuevo espacio, lo que puede provocar tiempos de espera de consulta.
    • Un aumento de crecimiento automático grande puede generar demasiados VLF pequeños y grandes, y también puede afectar al rendimiento. Para determinar la distribución óptima de VLF para el tamaño de registro de transacciones actual de todas las bases de datos en una instancia determinada, así como los incrementos de tamaño necesarios para conseguir el tamaño requerido, consulte este script para analizar y corregir VLF proporcionado por SQL Tiger Team.

  • Incluso con el crecimiento automático habilitado, puede recibir un mensaje de que el registro de transacciones está lleno, si no puede crecer lo suficientemente rápido para satisfacer las necesidades de la consulta. Para obtener más información sobre cómo cambiar el aumento de crecimiento, consulte Opciones File y Filegroup de ALTER DATABASE (Transact-SQL).

  • Aunque tenga varios archivos de registro en una base de datos, el rendimiento no mejorará, ya que los archivos de registro de transacciones no usan el relleno proporcional, como los archivos de datos de un mismo grupo de archivos.

  • Puede configurar los archivos de registro para que se reduzcan automáticamente. Esto no se recomienda y la propiedad de base de datos auto_shrink está establecida en FALSE de manera predeterminada. Si auto_shrink está establecida en TRUE, el proceso de reducción automática solo reduce el tamaño de un archivo cuando más del 25 % de su espacio está sin usar.

Pasos siguientes