Administrar el tamaño del archivo de registro de transaccionesManage the size of the transaction log file

SE APLICA A: síSQL Server noAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

En este tema se incluye información sobre cómo supervisar el tamaño de un registro de transacciones de SQL ServerSQL Server, reducir el registro de transacciones, agregar o ampliar un archivo de registro de transacciones, optimizar la tasa de crecimiento del registro de transacciones tempdb y controlar el crecimiento de un archivo de registro de transacciones.This topic covers how to monitor SQL ServerSQL Server transaction log size, shrink the transaction log, add to or enlarge a transaction log file, optimize the tempdb transaction log growth rate, and control the growth of a transaction log file.

Supervisión del uso del espacio del registroMonitor log space use

Supervise el uso del espacio del registro mediante sys.dm_db_log_space_usage.Monitor log space use by using 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.This DMV returns information about the amount of log space currently used, and indicates when the transaction log needs truncation.

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.For information about the current log file size, its maximum size, and the autogrow option for the file, you can also use the size, max_size, and growth columns for that log file in sys.database_files.

Importante

Evite la sobrecarga del disco del registro.Avoid overloading the log disk. Asegúrese de que el almacenamiento del registro puede soportar la IOPS y los requisitos de latencia baja para la carga de transacciones.Ensure the log storage can withstand the IOPS and low latency requirements for your transactional load.

Reducir el tamaño del archivo de registroShrink log file size

Para reducir el tamaño físico de un archivo de registro físico, debe reducir el archivo de registro.To reduce the physical size of a physical log file, you must shrink the log file. Esto es útil si sabe que un archivo de registro de transacciones contiene espacio que no se ha utilizado.This is useful when you know that a transaction log file contains unused space. 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.You can shrink a log file only while the database is online, and at least one virtual log file (VLF) is free. En algunos casos, no será posible reducir el registro hasta el siguiente truncamiento del registro.In some cases, shrinking the log may not be possible until after the next log truncation.

Nota

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.Factors such as a long-running transaction, that keep VLFs active for an extended period, can restrict log shrinkage or even prevent the log from shrinking at all. Para obtener información, vea Factores que pueden ralentizar el truncamiento del registro.For information, see Factors that can delay log truncation.

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).Shrinking a log file removes one or more VLFs that hold no part of the logical log (that is, inactive VLFs). 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.When a transaction log file is shrunk, inactive VLFs are removed from the end of the log file to reduce the log to approximately the target size.

Importante

Antes de reducir el registro de transacciones, tenga en cuenta los Factores que pueden ralentizar el truncamiento del registro.Before shrinking the transaction log, keep in mind Factors that can delay log truncation. 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.If the storage space is required again after a log shrink, the transaction log will grow again and by doing that, introduce performance overhead during log growth operations. Para obtener más información, vea Recomendaciones en este tema.For more information, see the Recommendations in this topic.

Reducir un archivo de registro (sin reducir los archivos de base de datos)Shrink a log file (without shrinking database files)

Supervisar los eventos de reducción de un archivo de registroMonitor log-file shrink events

Supervisar el espacio del registroMonitor log space

Agregar o ampliar un archivo de registroAdd or enlarge a log file

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.You can gain space by enlarging the existing log file (if disk space permits) or by adding a log file to the database, typically on a different disk. 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.One transaction log file is sufficient unless log space is running out, and disk space is also running out on the volume that holds the log file.

  • Para agregar un archivo de registro a la base de datos, use la cláusula ADD LOG FILE de la instrucción ALTER DATABASE.To add a log file to the database, use the ADD LOG FILE clause of the ALTER DATABASE statement. El hecho de agregar un archivo de registro permite que crezca el existente.Adding a log file allows the log to grow.
  • 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.To enlarge the log file, use the MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE syntax. Para obtener más información, vea Opciones File y Filegroup de ALTER DATABASE (Transact-SQL).For more information, see ALTER DATABASE (Transact-SQL) File and Filegroup options.

Para obtener más información, vea Recomendaciones en este tema.For more information, see the Recommendations in this topic.

Optimizar el tamaño del registro de transacciones tempdbOptimize tempdb transaction log size

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.Restarting a server instance resizes the transaction log of the tempdb database to its original, pre-autogrow size. Esto puede reducir el rendimiento del registro de transacciones de tempdb .This can reduce the performance of the tempdb transaction log.

Para evitar esta sobrecarga, aumente el tamaño del registro de transacciones de tempdb después de iniciar o reiniciar la instancia de servidor.You can avoid this overhead by increasing the size of the tempdb transaction log after starting or restarting the server instance. Para obtener más información, consulte tempdb Database.For more information, see tempdb Database.

Controlar el crecimiento de un archivo de registro de transaccionesControl transaction log file growth

Use la instrucción Opciones File y Filegroup de ALTER DATABASE (Transact-SQL) para administrar el crecimiento de un archivo de registro de transacciones.Use the ALTER DATABASE (Transact-SQL) File and Filegroup options statement to manage the growth of a transaction log file. Observe lo siguiente:Note the following:

  • Para cambiar el tamaño del archivo actual en unidades de KB, MB, GB y TB, use la opción SIZE.To change the current file size in KB, MB, GB, and TB units, use the SIZE option.
  • Para cambiar el incremento de crecimiento, use la opción FILEGROWTH.To change the growth increment, use the FILEGROWTH option. El valor 0 indica que el aumento automático se establece en OFF y no se permite ningún espacio adicional.A value of 0 indicates that automatic growth is set to off and no additional space is permitted.
  • 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.To control the maximum the size of a log file in KB, MB, GB, and TB units or to set growth to UNLIMITED, use the MAXSIZE option.

Para obtener más información, vea Recomendaciones en este tema.For more information, see the Recommendations in this topic.

RecomendacionesRecommendations

Estas son algunas recomendaciones generales referentes a los archivos de registro de transacciones:Following are some general recommendations when you are working with transaction log files:

  • 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.The automatic growth (autogrow) increment of the transaction log, as set by the FILEGROWTH option, must be large enough to stay ahead of the needs of the workload transactions. El incremento del crecimiento de un archivo de registro debe ser lo suficientemente grande para evitar una expansión frecuente.The file growth increment on a log file should be sufficiently large to avoid frequent expansion. Un buen punto de referencia para ajustar correctamente el tamaño de un registro de transacciones es supervisar la cantidad de registro ocupada durante:A good pointer to properly size a transaction log is monitoring the amount of log occupied during:

    • El tiempo necesario para ejecutar una copia de seguridad completa, porque no se pueden realizar copias de seguridad del registro hasta que termine.The time required to execute a full backup, because log backups cannot occur until it finishes.
    • El tiempo necesario para las operaciones de mantenimiento de índice más grandes.The time required for the largest index maintenance operations.
    • El tiempo necesario para ejecutar el lote más grande de una base de datos.The time required to execute the largest batch in a database.
  • 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.When setting autogrow for data and log files using the FILEGROWTH option, it might be preferred to set it in size instead of percentage, to allow better control on the growth ratio, as percentage is an ever-growing amount.

    • Tenga en cuenta que los registros de transacciones no pueden aprovechar la inicialización instantánea de archivos, por lo que los tiempos de crecimiento de registro extendido son especialmente importantes.Keep in mind that transaction logs cannot leverage Instant File Initialization, so extended log growth times are especially critical.
    • Como práctica recomendada, no establezca el valor de la opción FILEGROWTH por encima de 1024 MB para registros de transacciones.As a best practice, do not set the FILEGROWTH option value above 1,024 MB for transaction logs. Los valores predeterminados de la opción FILEGROWTH son los siguientes:The default values for FILEGROWTH option are:
    VersiónVersion Valores predeterminadosDefault values
    A partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x)Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) Datos: 64 MB.Data 64 MB. Archivos de registro: 64 MB.Log files 64 MB.
    A partir de SQL Server 2005 (9.x)SQL Server 2005 (9.x)Starting with SQL Server 2005 (9.x)SQL Server 2005 (9.x) Datos: 1 MB.Data 1 MB. Archivos de registro: 10 %.Log files 10%.
    Antes de SQL Server 2005 (9.x)SQL Server 2005 (9.x)Prior to SQL Server 2005 (9.x)SQL Server 2005 (9.x) Datos: 10 %.Data 10%. Archivos de registro: 10 %.Log files 10%.
  • Un aumento de crecimiento pequeño puede generar demasiados VLF pequeños y puede reducir el rendimiento.A small growth increment can generate too many small VLFs and can reduce performance. 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.To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this script.

  • Un aumento de crecimiento grande puede generar demasiados VLF pequeños y grandes, y también puede afectar al rendimiento.A large growth increment can generate too few and large VLFs and can also affect performance. 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.To determine the optimal VLF distribution for the current transaction log size of all databases in a given instance, and the required growth increments to achieve the required size, see this script.

  • 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.Even with autogrow enabled, you can receive a message that the transaction log is full, if it cannot grow fast enough to satisfy the needs of your query. Para obtener más información sobre cómo cambiar el aumento del crecimiento, vea Opciones File y Filegroup de ALTER DATABASE (Transact-SQL).For more information on changing the growth increment, see ALTER DATABASE (Transact-SQL) File and Filegroup options

  • 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.Having multiple log files in a database does not enhance performance in any way, because the transaction log files do not use proportional fill like data files in a same filegroup.

  • Puede configurar los archivos de registro para que se reduzcan automáticamente.Log files can be set to shrink automatically. Esto no se recomienda y la propiedad de base de datos auto_shrink está establecida en FALSE de manera predeterminada.However this is not recommended, and the auto_shrink database property is set to FALSE by default. 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.If auto_shrink is set to TRUE, automatic shrinking reduces the size of a file only when more than 25 percent of its space is unused.

Vea tambiénSee also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
Solucionar problemas de un registro de transacciones lleno (Error 9002 de SQL Server) Troubleshoot a Full Transaction Log (SQL Server Error 9002)
Copias de seguridad de registros de transacciones en Guía de arquitectura y administración de registros de transacciones de SQL Server Transaction Log Backups in the SQL Server Transaction Log Architecture and Management Guide
Copias de seguridad del registro de transacciones (SQL Server) Transaction Log Backups (SQL Server)
Opciones File y Filegroup de ALTER DATABASE (Transact-SQL)ALTER DATABASE (Transact-SQL) File and Filegroup options