Compartir a través de

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 similar, para obtener información sobre cómo administrar el tamaño de los archivos de registro de transacciones en Azure SQL Instancia administrada, consulte Administración del espacio de archivos para las bases de datos en Azure SQL Instancia administrada. 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 siguientes cantidades de espacio de almacenamiento 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 Espacio usado para almacenar datos de 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 en función de la cantidad y el patrón de datos implicados en la operación y cualquier fragmentación. Por ejemplo, al eliminar una fila de cada página de datos no disminuye necesariamente el espacio usado.
Espacio de datos asignado Espacio de archivo con formato disponible para almacenar datos de base de datos. La cantidad de espacio asignado crece automáticamente, pero nunca disminuye después de las eliminaciones. Este comportamiento garantiza que las inserciones futuras sean más rápidas, ya que no es necesario volver a formatear el espacio.
Espacio de datos asignado, pero no usado Diferencia entre la cantidad asignada y el espacio de datos utilizado. Esta cantidad representa el espacio libre máximo que pueden reclamar los archivos de datos de base de datos.
Tamaño máximo de datos Cantidad máxima de espacio para almacenar los datos de la 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.

Diagrama que muestra el tamaño de los conceptos de espacio de base de datos de diferencia en la tabla de cantidad de base de datos.

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 para el archivo, también puede usar las sizecolumnas , max_sizey growth para 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

Reduzca el archivo de registro para reducir su tamaño físico devolviendo espacio libre al sistema operativo. 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. Reducir los comandos afecta al rendimiento de la base de datos mientras se ejecuta; deben ejecutarse durante períodos de uso bajo. No se recomienda reducir los archivos de datos si una carga de trabajo normal de la aplicación hará que los archivos vuelvan a tener el mismo tamaño asignado.

Tenga en cuenta el posible impacto negativo en el rendimiento de la reducción de los archivos de base de datos; consulte Mantenimiento del índice 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 vuelve a requerir espacio de almacenamiento después de una reducción del registro, el registro de transacciones volverá a crecer, introduciendo una sobrecarga de rendimiento durante las operaciones de crecimiento del 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, es posible reducir el registro solo después del 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

Es posible que los índices se fragmentan después de que se complete una operación de reducción en los archivos de datos. Esto reduce su eficacia para la optimización del rendimiento de determinadas cargas de trabajo, como las consultas que usan exámenes 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 índices requieren espacio libre en la base de datos y, por tanto, pueden aumentar el espacio asignado, 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 agregar un archivo de registro a la base de datos, normalmente en un disco diferente. Un archivo de registro de transacciones es suficiente a menos que se agote el espacio de registro y el espacio en disco también se agote 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. Esto permite que el registro crezca.

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.

Puede evitar esta sobrecarga aumentando el tamaño del tempdb registro de transacciones después de iniciar o reiniciar la instancia del 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. Tenga en cuenta lo siguiente:

Use la opción 'SIZE' para cambiar el tamaño de archivo actual en unidades KB, MB, GB y TB.

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

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

Recomendaciones

A continuación se muestran algunas recomendaciones generales cuando se trabaja con archivos de registro de transacciones:

  • El incremento de crecimiento automático (crecimiento automático) del registro de transacciones, tal como se establece en la FILEGROWTH opción, debe ser lo suficientemente grande como para mantenerse al día de las necesidades de las transacciones de 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 las copias de seguridad de registros no se pueden producir hasta que finalice.
    • 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 el crecimiento automático de los archivos de datos y de registro mediante la FILEGROWTH opción , es posible que se prefiera establecerlo en tamaño en lugar de porcentaje para permitir un mejor control de la relación de crecimiento, ya que un porcentaje es una cantidad cada vez mayor.

    • 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 los tiempos de crecimiento extendidos del registro son especialmente críticos.

    • 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 nuevas bases de datos 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 procedimiento recomendado, no establezca el FILEGROWTH valor de opción por encima de 1024 MB para los registros de transacciones. Los valores predeterminados de la FILEGROWTH opción son:

      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 pequeño incremento de crecimiento puede generar demasiados VLF pequeños y puede reducir el rendimiento. Para determinar la distribución óptima de VLF para el tamaño actual del registro de transacciones de todas las bases de datos de una instancia determinada y los incrementos de crecimiento necesarios para lograr el tamaño necesario, consulte este script para analizar y corregir VLF, proporcionados por el equipo de SQL Tiger.

  • 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 incremento 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 actual del registro de transacciones de todas las bases de datos de una instancia determinada y los incrementos de crecimiento necesarios para lograr el tamaño necesario, consulte este script para analizar y corregir VLF, proporcionados por el equipo de SQL Tiger.

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

  • Tener varios archivos de registro en una base de datos no mejora el rendimiento de ninguna manera, ya que los archivos de registro de transacciones no usan relleno proporcional como archivos de datos en un mismo grupo de archivos.

Puede configurar los archivos de registro para que se reduzcan automáticamente. Sin embargo, esto no se recomienda y la propiedad de base de datos auto_shrink se establece en FALSE de forma 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. - El archivo se reduce al tamaño en el que solo el 25 por ciento del archivo no se usa o al tamaño original del archivo, lo que sea mayor. - Para obtener información sobre cómo cambiar la configuración de la propiedad auto_shrink, vea Ver o cambiar las propiedades de una base de datos y opciones alter DATABASE SET (Transact-SQL).

Contenido relacionado