Compartir a través de

Administrar el tamaño del archivo de registro de transacciones

Se aplica a:SQL Server

En este artículo se describe cómo supervisar el tamaño del 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 tempdb registro de transacciones y controlar el crecimiento de un archivo de registro de transacciones.

Este artículo se aplica a SQL Server. Aunque el proceso es similar, para la administración del espacio de archivos en Azure SQL, consulte:

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 en las inserciones y disminuye en las 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 porque 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 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 diagrama siguiente se muestran las relaciones entre los distintos tipos de espacio de almacenamiento de una base de datos.

Diagrama que muestra las relaciones entre los distintos tipos de espacio de almacenamiento para una 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 del 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.

Reducir un 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 lo que 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 normales 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 de aplicación normal hará que los archivos aumenten de nuevo al 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 factores que pueden retrasar 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 obtener más información, consulte Recomendaciones.

Solo puede reducir un archivo de registro mientras la base de datos está en línea y al menos un archivo de registro virtual (VLF) es gratuito. En algunos casos, es posible reducir el registro solo después del siguiente truncamiento del registro.

Algunos factores, como una transacción de larga duración, pueden mantener las VLF activas durante un período prolongado, pueden restringir la reducción del registro o incluso impedir que el registro se reduzca en absoluto. Para más 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 más información sobre las operaciones de reducción, revise los siguientes recursos:

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 del índice después de una operación de 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. Esta fragmentación 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, podrían aumentar el espacio asignado, lo que contrarresta el efecto de la operación de reducción.

Para más información, 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. Esta acción permite que el registro crezca.
  • 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, vea OPCIONES de archivo y grupo de archivos ALTER DATABASE (Transact-SQL).

Para obtener más información, consulte Recomendaciones.

Optimizar el tamaño del registro de transacciones tempdb

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

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 Base de datos Tempdb.

Controlar el crecimiento de un archivo de registro de transacciones

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

  • Use la SIZE opción 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 MAXSIZE opción 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 obtener más información, consulte Recomendaciones.

Recomendaciones

A continuación se muestran algunas recomendaciones generales que se deben tener en cuenta al trabajar 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. Una buena sugerencia para cambiar el tamaño correcto de un registro de transacciones es supervisar la cantidad de registro ocupado durante:

    • El tiempo necesario para ejecutar una copia de seguridad completa, ya que 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.
    • Tiempo necesario para ejecutar el lote más grande en una base de datos.

  • Al establecer el crecimiento automático de los archivos de datos y de registro mediante la FILEGROWTH opción , puede ser mejor 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 automático 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:

    • Puede hacer que la base de datos se detenga mientras se asigna el nuevo espacio, lo que puede provocar tiempos de espera de consulta.
    • Puede generar demasiados VLF 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 obtener más información sobre cómo cambiar el incremento de crecimiento, vea Opciones de archivo y grupo de archivos 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, no se recomienda esta configuración y la AUTO_SHRINK propiedad de base de datos se establece en FALSE de forma predeterminada. Si AUTO_SHRINK se establece en TRUE, la reducción automática reduce el tamaño de un archivo solo cuando no se usa más del 25 por ciento de su espacio.

Contenido relacionado

  • Last updated on