Solución de problemas de un registro de transacciones lleno (Error 9002 de SQL Server)

Artículo
04/24/2024

Se aplica a: SQL Server

Este artículo se aplica a las instancias de SQL Server.

Nota:

Este artículo se centra en SQL Server. Para obtener información más específica sobre este error en las plataformas de Azure SQL, consulte Solución de problemas de errores del registro de transacciones con Azure SQL Database y Solución de problemas de errores del registro de transacciones con Azure SQL Managed Instance. Azure SQL Database y Azure SQL Managed Instance se basan en la versión estable más reciente del motor de base de datos de Microsoft SQL Server, por lo que gran parte del contenido es similar, aunque las herramientas y las opciones de solución de problemas pueden diferir.

Opción 1: Ejecute los pasos directamente en un cuaderno ejecutable a través de Azure Data Studio

Nota

Antes de intentar abrir este cuaderno, compruebe que Azure Data Studio está instalado en la máquina local. Para instalarlo, vaya a Aprender a instalar Azure Data Studio.

Apertura de un cuaderno en Azure Data Studio

Opción 2: Seguir el paso manualmente

En este artículo se tratan las posibles respuestas a un registro de transacciones lleno y se sugiere cómo evitar esta situación en el futuro.

Cuando el registro de transacciones se llena, el motor de base de datos de SQL Server genera un error 9002. El registro se puede llenar cuando la base de datos está en línea o en recuperación. Si el registro se llena mientras la base de datos está en línea, la base de datos permanece en línea, pero solo se puede leer, no se puede actualizar. Si el registro se llena durante la recuperación, el motor de base de datos marca la base de datos como RESOURCE PENDING. En ambos casos, es necesaria la intervención del usuario para proporcionar espacio de registro.

Causas comunes de un registro de transacciones lleno

La respuesta apropiada a un registro de transacciones lleno depende de las condiciones que han causado que el registro se llene. Las causas más comunes son:

El registro no se trunca.
El volumen del disco está lleno
El tamaño del registro tiene establecido un valor máximo fijo o la opción de crecimiento automático está deshabilitada.
No se puede completar la sincronización del grupo de disponibilidad ni la replicación.

Resolución de un registro de transacciones lleno

Estos pasos específicos le ayudan a descubrir la causa por la cual un registro de transacciones está lleno y aprenderá a resolver este problema.

1. Truncamiento de un registro

Una solución muy común a este problema es asegurarse de que se realizan copias de seguridad del registro de transacciones para la base de datos, lo que garantiza que el registro se trunque. Si no se indica ningún historial de registros de transacciones reciente para la base de datos con un registro de transacciones completo, la solución al problema es sencilla: reanudar las copias de seguridad del registro de transacciones habituales de la base de datos.

Para obtener más información, consulte Administración del tamaño del registro y Reducción de un archivo.

Explicación del truncamiento del registro

Hay una diferencia entre truncar un registro de transacciones y reducirlo. Por lo general, el truncamiento de un registro se produce durante una copia de seguridad del registro de transacciones y es una operación lógica que quita los registros confirmados que están dentro del registro, mientras que la reducción de un registro reduce el tamaño del archivo para recuperar espacio físico en el sistema de archivos. El truncamiento de un registro se produce en un límite de archivo de registro virtual (VLF) y un archivo de registro puede contener varios VLF. Solo se puede reducir un archivo de registro si contiene espacio vacío que se puede recuperar. La reducción de un archivo de registro por sí solo no puede resolver el problema de un archivo de registro completo. En su lugar, debe detectar por qué el archivo de registro está lleno y no se puede truncar.

Advertencia

Los datos que se mueven para reducir un archivo se pueden dispersar en cualquier ubicación disponible en el archivo. Esto genera la fragmentación del índice y puede ralentizar el rendimiento de las consultas que buscan un intervalo del índice. Para eliminar la fragmentación, considere la posibilidad de volver a generar los índices en el archivo después de la reducción. Para más información, consulte Shrink a Database.

¿Qué impide el truncamiento del registro?

Para detectar qué impide el truncamiento del registro en un caso determinado, use las columnas log_reuse_wait y log_reuse_wait_desc de la vista de catálogo sys.databases. Para obtener más información, vea sys.databases (Transact-SQL). Para obtener la descripción de los factores que pueden retrasar el truncamiento del registro, consulte Registro de transacciones (SQL Server).

El conjunto siguiente de comandos de T-SQL le ayuda a identificar si un registro de transacciones de la base de datos no está truncado y el motivo de esto. El script siguiente también recomienda los pasos para resolver el problema:

SET NOCOUNT ON
DECLARE @SQL VARCHAR (8000), @log_reuse_wait tinyint, @log_reuse_wait_desc nvarchar(120), @dbname sysname, @database_id int, @recovery_model_desc varchar (24)

IF ( OBJECT_id (N'tempdb..#CannotTruncateLog_Db') is not null)
BEGIN
    DROP TABLE #CannotTruncateLog_Db
END

--get info about transaction logs in each database.

IF ( OBJECT_id (N'tempdb..#dm_db_log_space_usage') is not null)
BEGIN
    DROP TABLE #dm_db_log_space_usage
END
SELECT * INTO #dm_db_log_space_usage FROM sys.dm_db_log_space_usage where 1=0

DECLARE log_space CURSOR FOR SELECT NAME FROM sys.databases
OPEN log_space

FETCH NEXT FROM log_space into @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

    set @SQL = '
    insert into #dm_db_log_space_usage (
    database_id,
    total_log_size_in_bytes,
    used_log_space_in_bytes,
    used_log_space_in_percent,
    log_space_in_bytes_since_last_backup
    )
    select
    database_id,
    total_log_size_in_bytes,
    used_log_space_in_bytes,
    used_log_space_in_percent,
    log_space_in_bytes_since_last_backup
    from ' + @dbname +'.sys.dm_db_log_space_usage'

    BEGIN TRY
        exec (@SQL)
    END TRY

    BEGIN CATCH
        SELECT ERROR_MESSAGE() AS ErrorMessage;
    END CATCH;

    FETCH NEXT FROM log_space into @dbname
END

CLOSE log_space
DEALLOCATE log_space

--select the affected databases
SELECT
    sdb.name as DbName,
    sdb.log_reuse_wait, sdb.log_reuse_wait_desc,
    log_reuse_wait_explanation = CASE

        WHEN log_reuse_wait = 1 THEN 'No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond'
        WHEN log_reuse_wait = 2 THEN 'A log backup is required before the transaction log can be truncated.'
        WHEN log_reuse_wait = 3 THEN 'A data backup or a restore is in progress (all recovery models). Please wait or cancel backup'
        WHEN log_reuse_wait = 4 THEN 'A long-running active transaction or a defferred transaction is keeping log from being truncated. You can attempt a log backup to free space or complete/rollback long transaction'
        WHEN log_reuse_wait = 5 THEN 'Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database. (Full recovery model only)'
        WHEN log_reuse_wait = 6 THEN 'During transactional replication, transactions relevant to the publications are still undelivered to the distribution database. Investigate the status of agents involved in replication or Changed Data Capture (CDC). (Full recovery model only.)'
        WHEN log_reuse_wait = 7 THEN 'A database snapshot is being created. This is a routine, and typically brief, cause of delayed log truncation.'
        WHEN log_reuse_wait = 8 THEN 'A transaction log scan is occurring. This is a routine, and typically a brief cause of delayed log truncation.'
        WHEN log_reuse_wait = 9 THEN 'A secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. (Full recovery model only.)'
        WHEN log_reuse_wait = 13 THEN 'If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN).'
        WHEN log_reuse_wait = 16 THEN 'An In-Memory OLTP checkpoint has not occurred since the last log truncation, or the head of the log has not yet moved beyond a VLF.'
    ELSE 'None' END,

    sdb.database_id,
    sdb.recovery_model_desc,
    lsu.used_log_space_in_bytes / 1024 as Used_log_size_MB,
    lsu.total_log_size_in_bytes / 1024 as Total_log_size_MB,
    100 - lsu.used_log_space_in_percent as Percent_Free_Space
INTO #CannotTruncateLog_Db
FROM sys.databases AS sdb INNER JOIN #dm_db_log_space_usage lsu ON sdb.database_id = lsu.database_id
WHERE log_reuse_wait > 0

SELECT * FROM #CannotTruncateLog_Db

DECLARE no_truncate_db CURSOR FOR
    SELECT log_reuse_wait, log_reuse_wait_desc, DbName, database_id, recovery_model_desc FROM #CannotTruncateLog_Db;

OPEN no_truncate_db

FETCH NEXT FROM no_truncate_db into @log_reuse_wait, @log_reuse_wait_desc, @dbname, @database_id, @recovery_model_desc

WHILE @@FETCH_STATUS = 0
BEGIN
    if (@log_reuse_wait > 0)
        select '-- ''' + @dbname +  ''' database has log_reuse_wait = ' + @log_reuse_wait_desc + ' --'  as 'Individual Database Report'

    if (@log_reuse_wait = 1)
    BEGIN
        select 'Consider running the checkpoint command to attempt resolving this issue or further t-shooting may be required on the checkpoint process. Also, examine the log for active VLFs at the end of file' as Recommendation
        select 'USE ''' + @dbname+ '''; CHECKPOINT' as CheckpointCommand
        select 'select * from sys.dm_db_log_info(' + CONVERT(varchar,@database_id)+ ')' as VLF_LogInfo
    END
    else if (@log_reuse_wait = 2)
    BEGIN
        select 'Is '+ @recovery_model_desc +' recovery model the intended choice for ''' + @dbname+ ''' database? Review recovery models and determine if you need to change it. https://learn.microsoft.com/sql/relational-databases/backup-restore/recovery-models-sql-server' as RecoveryModelChoice
        select 'To truncate the log consider performing a transaction log backup on database ''' + @dbname+ ''' which is in ' + @recovery_model_desc +' recovery model. Be mindful of any existing log backup chains that could be broken' as Recommendation
        select 'BACKUP LOG [' + @dbname + '] TO DISK = ''some_volume:\some_folder\' + @dbname + '_LOG.trn ''' as BackupLogCommand
    END
    else if (@log_reuse_wait = 3)
    BEGIN
        select 'Either wait for or cancel any active backups currently running for database ''' +@dbname+ '''. To check for backups, run this command:' as Recommendation
        select 'select * from sys.dm_exec_requests where command like ''backup%'' or command like ''restore%''' as FindBackupOrRestore
    END
    else if (@log_reuse_wait = 4)
    BEGIN
        select 'Active transactions currently running  for database ''' +@dbname+ '''. To check for active transactions, run these commands:' as Recommendation
        select 'DBCC OPENTRAN (''' +@dbname+ ''')' as FindOpenTran
        select 'select database_id, db_name(database_id) dbname, database_transaction_begin_time, database_transaction_state, database_transaction_log_record_count, database_transaction_log_bytes_used, database_transaction_begin_lsn, stran.session_id from sys.dm_tran_database_transactions dbtran left outer join sys.dm_tran_session_transactions stran on dbtran.transaction_id = stran.transaction_id where database_id = ' + CONVERT(varchar, @database_id) as FindOpenTransAndSession
    END

    else if (@log_reuse_wait = 5)
    BEGIN
        select 'Database Mirroring for database ''' +@dbname+ ''' is behind on synchronization. To check the state of DBM, run the commands below:' as Recommendation
        select 'select db_name(database_id), mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc from sys.database_mirroring where mirroring_guid is not null and mirroring_state <> 4 and database_id = ' + convert(sysname, @database_id)  as CheckMirroringStatus

        select 'Database Mirroring for database ''' +@dbname+ ''' may be behind: check unsent_log, send_rate, unrestored_log, recovery_rate, average_delay in this output' as Recommendation
        select 'exec msdb.sys.sp_dbmmonitoraddmonitoring 1; exec msdb.sys.sp_dbmmonitorresults ''' + @dbname+ ''', 5, 0; waitfor delay ''00:01:01''; exec msdb.sys.sp_dbmmonitorresults ''' + @dbname+ '''; exec msdb.sys.sp_dbmmonitordropmonitoring'   as CheckMirroringStatusAnd
    END

    else if (@log_reuse_wait = 6)
    BEGIN
        select 'Replication transactions still undelivered from publisher database ''' +@dbname+ ''' to Distribution database. Check the oldest non-distributed replication transaction. Also check if the Log Reader Agent is running and if it has encoutered any errors' as Recommendation
        select 'DBCC OPENTRAN  (''' + @dbname + ''')' as CheckOldestNonDistributedTran
        select 'select top 5 * from distribution..MSlogreader_history where runstatus in (6, 5) or error_id <> 0 and agent_id = find_in_mslogreader_agents_table  order by time desc ' as LogReaderAgentState
    END

    else if (@log_reuse_wait = 9)
    BEGIN
        select 'Always On transactions still undelivered from primary database ''' +@dbname+ ''' to Secondary replicas. Check the Health of AG nodes and if there is latency is Log block movement to Secondaries' as Recommendation
        select 'select availability_group=cast(ag.name as varchar(30)), primary_replica=cast(ags.primary_replica as varchar(30)),primary_recovery_health_desc=cast(ags.primary_recovery_health_desc as varchar(30)), synchronization_health_desc=cast(ags.synchronization_health_desc as varchar(30)),ag.failure_condition_level, ag.health_check_timeout, automated_backup_preference_desc=cast(ag.automated_backup_preference_desc as varchar(10))  from sys.availability_groups ag join sys.dm_hadr_availability_group_states ags on ag.group_id=ags.group_id' as CheckAGHealth
        select 'SELECT  group_name=cast(arc.group_name as varchar(30)), replica_server_name=cast(arc.replica_server_name as varchar(30)), node_name=cast(arc.node_name as varchar(30)),role_desc=cast(ars.role_desc as varchar(30)), ar.availability_mode_Desc, operational_state_desc=cast(ars.operational_state_desc as varchar(30)), connected_state_desc=cast(ars.connected_state_desc as varchar(30)), recovery_health_desc=cast(ars.recovery_health_desc as varchar(30)), synchronization_health_desc=cast(ars.synchronization_health_desc as varchar(30)), ars.last_connect_error_number, last_connect_error_description=cast(ars.last_connect_error_description as varchar(30)), ars.last_connect_error_timestamp, primary_role_allow_connections_desc=cast(ar.primary_role_allow_connections_desc as varchar(30)) from sys.dm_hadr_availability_replica_cluster_nodes arc join sys.dm_hadr_availability_replica_cluster_states arcs on arc.replica_server_name=arcs.replica_server_name join sys.dm_hadr_availability_replica_states ars on arcs.replica_id=ars.replica_id join sys.availability_replicas ar on ars.replica_id=ar.replica_id join sys.availability_groups ag on ag.group_id = arcs.group_id and ag.name = arc.group_name ORDER BY cast(arc.group_name as varchar(30)), cast(ars.role_desc as varchar(30))' as CheckReplicaHealth
        select 'select database_name=cast(drcs.database_name as varchar(30)), drs.database_id, drs.group_id, drs.replica_id, drs.is_local,drcs.is_failover_ready,drcs.is_pending_secondary_suspend, drcs.is_database_joined, drs.is_suspended, drs.is_commit_participant, suspend_reason_desc=cast(drs.suspend_reason_desc as varchar(30)), synchronization_state_desc=cast(drs.synchronization_state_desc as varchar(30)), synchronization_health_desc=cast(drs.synchronization_health_desc as varchar(30)), database_state_desc=cast(drs.database_state_desc as varchar(30)), drs.last_sent_lsn, drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn, drs.last_hardened_time,drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate, drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.low_water_mark_for_ghosts, drs.recovery_lsn, drs.truncation_lsn, pr.file_id, pr.error_type, pr.page_id, pr.page_status, pr.modification_time from sys.dm_hadr_database_replica_cluster_states drcs join sys.dm_hadr_database_replica_states drs on drcs.replica_id=drs.replica_id and drcs.group_database_id=drs.group_database_id left outer join sys.dm_hadr_auto_page_repair pr on drs.database_id=pr.database_id  order by drs.database_id' as LogMovementHealth
        select 'For more information see https://learn.microsoft.com/troubleshoot/sql/availability-groups/error-9002-transaction-log-large' as OnlineDOCResource
    END
    else if (@log_reuse_wait in (10, 11, 12, 14))
    BEGIN
        select 'This state is not documented and is expected to be rare and short-lived' as Recommendation
    END
    else if (@log_reuse_wait = 13)
    BEGIN
        select 'The oldest page on the database might be older than the checkpoint log sequence number (LSN). In this case, the oldest page can delay log truncation.' as Finding
        select 'This state should be short-lived, but if you find it is taking a long time, you can consider disabling Indirect Checkpoint temporarily' as Recommendation
        select 'ALTER DATABASE [' +@dbname+ '] SET TARGET_RECOVERY_TIME = 0 SECONDS' as DisableIndirectCheckpointTemporarily
    END
    else if (@log_reuse_wait = 16)
    BEGIN
        select 'For memory-optimized tables, an automatic checkpoint is taken when transaction log file becomes bigger than 1.5 GB since the last checkpoint (includes both disk-based and memory-optimized tables)' as Finding
        select 'Review https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/' as ReviewBlog
        select 'use ' +@dbname+ ' CHECKPOINT' as RunCheckpoint
    END

    FETCH NEXT FROM no_truncate_db into @log_reuse_wait, @log_reuse_wait_desc, @dbname, @database_id, @recovery_model_desc

END

CLOSE no_truncate_db
DEALLOCATE no_truncate_db

Importante

Si la base de datos estaba en recuperación cuando se produjo el error 9002, una vez resuelto el problema, recupere la base de datos mediante ALTER DATABASE nombre_de_base_de_datos SET ONLINE.

LOG_BACKUP log_reuse_wait

La acción más común que puede considerar si ve LOG_BACKUP o log_reuse_wait es revisar el modelo de recuperación de la base de datos y realizar una copia de seguridad del registro de transacciones de la base de datos.

Consideración del modelo de recuperación de la base de datos

Es posible que el registro de transacciones no se pueda truncar con la categoría LOG_BACKUP log_reuse_wait, porque nunca se ha hecho una copia de seguridad de él. En muchos de esos casos, la base de datos usa el modelo de recuperación FULL o BULK_LOGGED, pero no ha hecho una copia de seguridad del registro de transacciones. Debe considerar detenidamente cada modelo de recuperación de base de datos: realice periódicamente copias de seguridad del registro de transacciones en todas las bases de datos en los modelos de recuperación FULL o BULK LOGGED para minimizar las apariciones del error 9002. Para obtener más información, consulte Modelos de recuperación.

Crear copia de seguridad del registro

En el modelo de recuperación FULL o BULK_LOGGED, si no se ha creado recientemente una copia de seguridad del registro de transacciones, puede que la copia de seguridad sea la que evita el truncamiento del registro. Debe hacer una copia de seguridad del registro de transacciones para permitir que se liberen las entradas de registro y se trunque el registro. Si nunca hizo una copia de seguridad del registro, debe crear dos copias de seguridad de registros para que el Motor de base de datos pueda truncar el registro en el punto de la última copia de seguridad. El truncamiento del registro libera espacio lógico para entradas nuevas del registro. Para evitar que el registro se vuelva a llenar, cree copias de seguridad del registro de transacciones más frecuentes de manera periódica. Para obtener más información, consulte Modelos de recuperación.

En la base de datos del sistema msdb se almacena un historial completo de todas las operaciones de copia de seguridad y restauración de SQL Server en una instancia de servidor. Para revisar el historial completo de las copias de seguridad de una base de datos, use el script de ejemplo siguiente:

SELECT bs.database_name
, backuptype = CASE
    WHEN bs.type = 'D' and bs.is_copy_only = 0 THEN 'Full Database'
    WHEN bs.type = 'D' and bs.is_copy_only = 1 THEN 'Full Copy-Only Database'
    WHEN bs.type = 'I' THEN 'Differential database backup'
    WHEN bs.type = 'L' THEN 'Transaction Log'
    WHEN bs.type = 'F' THEN 'File or filegroup'
    WHEN bs.type = 'G' THEN 'Differential file'
    WHEN bs.type = 'P' THEN 'Partial'
    WHEN bs.type = 'Q' THEN 'Differential partial' END + ' Backup'
, bs.recovery_model
, BackupStartDate = bs.Backup_Start_Date
, BackupFinishDate = bs.Backup_Finish_Date
, LatestBackupLocation = bf.physical_device_name
, backup_size_mb = bs.backup_size/1024./1024.
, compressed_backup_size_mb = bs.compressed_backup_size/1024./1024.
, database_backup_lsn -- For tlog and differential backups, this is the checkpoint_lsn of the FULL backup it is based on.
, checkpoint_lsn
, begins_log_chain
FROM msdb.dbo.backupset AS bs
LEFT OUTER JOIN msdb.dbo.backupmediafamily AS bf ON bs.[media_set_id] = bf.[media_set_id]
WHERE recovery_model in ('FULL', 'BULK-LOGGED')
AND bs.backup_start_date > DATEADD(month, -2, sysdatetime()) --only look at last two months
ORDER BY bs.database_name asc, bs.Backup_Start_Date desc;

En la base de datos del sistema msdb se almacena un historial completo de todas las operaciones de copia de seguridad y restauración de SQL Server en una instancia de servidor. Para más información sobre el historial de copia de seguridad, vea Historial de copias de seguridad e información de encabezados (SQL Server).

Creación de una copia de seguridad del registro de transacciones

Ejemplo de cómo hacer una copia de seguridad del registro:

BACKUP LOG [dbname] TO DISK = 'some_volume:\some_folder\dbname_LOG.trn'

Importante

Si la base de datos está dañada, vea Copias del final del registro (SQL Server).

ACTIVE_TRANSACTION log_reuse_wait

Los pasos para solucionar el motivo ACTIVE_TRANSACTION incluyen la detección de la transacción de larga duración y su resolución (en algún caso, recurriendo al comando KILL).

Detección de transacciones de larga duración

Una transacción de ejecución muy prolongada puede hacer que el registro de transacciones se llene. Para buscar las transacciones de ejecución prolongada, use una de las opciones siguientes:

sys.dm_tran_database_transactions.

Esta vista de administración dinámica devuelve información sobre las transacciones en la base de datos. En una transacción de ejecución prolongada, las columnas de especial interés incluyen la hora de la primera entrada del registro (database_transaction_begin_time), el estado actual de la transacción (database_transaction_state) y el número de secuencia de registro (LSN) del registro BEGIN del registro de transacciones (database_transaction_begin_lsn).

DBCC OPENTRAN. Esta instrucción permite identificar el Id. de usuario del propietario de la transacción, por lo que se puede realizar un seguimiento del origen de la misma para terminarla de forma más ordenada (confirmándola en lugar de revirtiéndola).

Terminar una transacción

Hay veces en que simplemente tiene que finalizar la transacción; puede que tenga que usar la instrucción KILL. Use la instrucción KILL con sumo cuidado, especialmente cuando se estén ejecutando procesos críticos que no desea terminar.

CHECKPOINT log_reuse_wait

No se ha producido ningún punto de comprobación desde el último truncamiento o el encabezado del registro no se movió más allá de un archivo de registro virtual (VLF). (Todos los modelos de recuperación)

Este es un motivo habitual para retrasar el truncamiento. Si se retrasa, considere la posibilidad de ejecutar el comando CHECKPOINT en la base de datos o examinar los VLF del registro.

USE dbname; CHECKPOINT
select * from sys.dm_db_log_info(db_id('dbname'))

AVAILABILITY_REPLICA log_reuse_wait

Cuando los cambios de transacción en la réplica de disponibilidad principal Grupo de disponibilidad Always On aún no están en la réplica secundaria, no se puede truncar el registro de transacciones en la réplica principal. Esto puede hacer que el registro crezca, y se produce si la réplica secundaria está establecida para el modo de confirmación sincrónico o asíncrono. Para obtener información sobre cómo solucionar este tipo de problema, vea Error 9002. El registro de transacciones de la base de datos está lleno debido a un error de AVAILABILITY_REPLICA.

Replicación, seguimiento de cambios o CDC

Las características como la replicación, el seguimiento de cambios y la captura de datos modificados (CDC) dependen del registro de transacciones, por lo que si no se entregan transacciones o cambios, puede impedir que el registro de transacciones se trunque.

Use DBCC OPENTRAN, Monitor de replicación o procedimientos almacenados para el seguimiento de cambios y CDC para investigar y resolver cualquier problema con estas características.

Para obtener más información sobre los factores de log_reuse_wait

Para obtener más detalles, vea Factores que pueden ralentizar el truncamiento del registro.

2. Resolución del volumen de disco completo

En algunas situaciones, el volumen del disco que hospeda el archivo de registro de transacciones se puede llenar. Puede realizar una de estas acciones para resolver el escenario de registro lleno que resulta de un disco lleno:

Espacio libre en disco

Puede liberar espacio en la unidad de disco que contiene el archivo de registro de transacciones de la base de datos eliminando o desplazando otros archivos. La liberación de espacio de disco permite que el sistema de recuperación amplíe automáticamente el archivo de registro.

Mover el archivo de registro a otro disco

Si no puede liberar suficiente espacio en la unidad de disco que contiene el archivo de registro, considere la posibilidad de desplazarlo a otra unidad con suficiente espacio.

Importante

Los archivos de registro no se deben almacenar en sistemas de archivo comprimidos.

Consulte Mover archivos de base de datos si desea información sobre cómo cambiar la ubicación de un archivo de registro.

Agregar un archivo de registro en otro disco

Agregue un archivo de registro nuevo a la base de datos en otro disco que tenga espacio suficiente mediante ALTER DATABASE <database_name> ADD LOG FILE. Varios archivos de registro para una base de datos única deben considerarse una condición temporal para resolver un problema de espacio, no una condición a largo plazo. La mayoría de las bases de datos solo deben tener un archivo de registro de transacciones. Siga investigando la causa por la que el registro de transacciones está lleno y no se puede truncar. Considere la posibilidad de agregar archivos de registro de transacciones adicionales temporales únicamente como paso avanzado para la solución de problemas.

Para obtener más información, consulte Agregar archivos de datos o de registro a una base de datos.

Script de utilidad para las acciones recomendadas

Estos pasos se pueden automatizar parcialmente mediante la ejecución del siguiente script de T-SQL para identificar los archivos de registro que usan un gran porcentaje de espacio en disco y sugerir acciones:

DECLARE @log_reached_disk_size BIT = 0

SELECT
    name LogName,
    physical_name,
    CONVERT(bigint, size)*8/1024 LogFile_Size_MB,
    volume_mount_point,
    available_bytes/1024/1024 Available_Disk_space_MB,
    (CONVERT(bigint, size)*8.0/1024)/(available_bytes/1024/1024 )*100 file_size_as_percentage_of_disk_space,
    db_name(mf.database_id) DbName
FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats (mf.database_id, file_id)
WHERE mf.[type_desc] = 'LOG'
    AND (CONVERT(bigint, size)*8.0/1024)/(available_bytes/1024/1024 )*100 > 90 --log is 90% of disk drive
ORDER BY size DESC

if @@ROWCOUNT > 0
BEGIN

    set @log_reached_disk_size = 1

    -- Discover if any logs have are close to or completely filled disk volume they reside on.
    -- Either Add A New File To A New Drive, Or Shrink Existing File
    -- If Cannot Shrink, Go To Cannot Truncate Section

    DECLARE @db_name_filled_disk sysname, @log_name_filled_disk sysname, @go_beyond_size bigint

    DECLARE log_filled_disk CURSOR FOR
        SELECT
            db_name(mf.database_id),
            name
        FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats (mf.database_id, file_id)
        WHERE mf.[type_desc] = 'LOG'
            AND (convert(bigint, size)*8.0/1024)/(available_bytes/1024/1024 )*100 > 90 --log is 90% of disk drive
        ORDER BY size desc

    OPEN log_filled_disk

    FETCH NEXT FROM log_filled_disk into @db_name_filled_disk , @log_name_filled_disk

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT 'Transaction log for database "' + @db_name_filled_disk + '" has nearly or completely filled disk volume it resides on!' AS Finding
        SELECT 'Consider using one of the below commands to shrink the "' + @log_name_filled_disk +'" transaction log file size or add a new file to a NEW volume' AS Recommendation
        SELECT 'DBCC SHRINKFILE(''' + @log_name_filled_disk + ''')' AS Shrinkfile_Command
        SELECT 'ALTER DATABASE ' + @db_name_filled_disk + ' ADD LOG FILE ( NAME = N''' + @log_name_filled_disk + '_new'', FILENAME = N''NEW_VOLUME_AND_FOLDER_LOCATION\' + @log_name_filled_disk + '_NEW.LDF'', SIZE = 81920KB , FILEGROWTH = 65536KB )' AS AddNewFile
        SELECT 'If shrink does not reduce the file size, likely it is because it has not been truncated. Please review next section below. See https://learn.microsoft.com/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql' AS TruncateFirst
        SELECT 'Can you free some disk space on this volume? If so, do this to allow for the log to continue growing when needed.' AS FreeDiskSpace

         FETCH NEXT FROM log_filled_disk into @db_name_filled_disk , @log_name_filled_disk

    END

    CLOSE log_filled_disk
    DEALLOCATE log_filled_disk

END

3. Cambio del límite de tamaño del registro o habilitación del crecimiento automático

Se puede generar el error 9002 si el tamaño del registro de transacciones tiene establecido un límite superior o no se permite la característica de crecimiento automático. En este caso, habilitar el crecimiento automático o aumentar el tamaño del registro de manera manual puede ayudar a resolver el problema. Use este comando de T-SQL para buscar estos archivos de registro y siga las recomendaciones proporcionadas:

SELECT DB_NAME(database_id) DbName,
       name LogName,
       physical_name,
       type_desc ,
       CONVERT(bigint, SIZE)*8/1024 LogFile_Size_MB ,
       CONVERT(bigint,max_size)*8/1024 LogFile_MaxSize_MB ,
       (SIZE*8.0/1024)/(max_size*8.0/1024)*100 percent_full_of_max_size,
       CASE WHEN growth = 0 THEN 'AUTOGROW_DISABLED' ELSE 'Autogrow_Enabled' END as AutoGrow
FROM sys.master_files
WHERE file_id = 2
    AND (SIZE*8.0/1024)/(max_size*8.0/1024)*100 > 90
    AND max_size not in (-1, 268435456)
    OR growth = 0

if @@ROWCOUNT > 0
BEGIN
    DECLARE @db_name_max_size sysname, @log_name_max_size sysname, @configured_max_log_boundary bigint, @auto_grow int

    DECLARE reached_max_size CURSOR FOR
        SELECT db_name(database_id),
               name,
               CONVERT(bigint, SIZE)*8/1024,
               growth
        FROM sys.master_files
        WHERE file_id = 2
            AND ( (SIZE*8.0/1024)/(max_size*8.0/1024)*100 > 90
            AND max_size not in (-1, 268435456)
            OR growth = 0 )

    OPEN reached_max_size

    FETCH NEXT FROM reached_max_size into @db_name_max_size , @log_name_max_size, @configured_max_log_boundary, @auto_grow

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @auto_grow = 0
          BEGIN
            SELECT 'The database "' + @db_name_max_size+'" contains a log file "' + @log_name_max_size + '" whose autogrow has been DISABLED' as Finding
            SELECT 'Consider enabling autogrow or increasing file size via these ALTER DATABASE commands' as Recommendation
            SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', FILEGROWTH = 65536KB)' as AutoGrowth
          END
        ELSE
          BEGIN
            SELECT 'The database "' + @db_name_max_size+'" contains a log file "' + @log_name_max_size + '" whose max limit is set to ' + convert(varchar(24), @configured_max_log_boundary) + ' MB and this limit has been reached!' as Finding
            SELECT 'Consider using one of the below ALTER DATABASE commands to either change the log file size or add a new file' as Recommendation
          END

        SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', MAXSIZE = UNLIMITED)' as UnlimitedSize
        SELECT 'ALTER DATABASE ' + @db_name_max_size + ' MODIFY FILE ( NAME = N''' + @log_name_max_size + ''', MAXSIZE = something_larger_than_' + CONVERT(varchar(24), @configured_max_log_boundary) +'MB )' as IncreasedSize
        SELECT 'ALTER DATABASE ' + @db_name_max_size + ' ADD LOG FILE ( NAME = N''' + @log_name_max_size + '_new'', FILENAME = N''SOME_FOLDER_LOCATION\' + @log_name_max_size + '_NEW.LDF'', SIZE = 81920KB , FILEGROWTH = 65536KB )' as AddNewFile

        FETCH NEXT FROM reached_max_size into @db_name_max_size , @log_name_max_size, @configured_max_log_boundary, @auto_grow

    END

    CLOSE reached_max_size
    DEALLOCATE reached_max_size
END
ELSE
    SELECT 'Found no files that have reached max log file size' as Findings

Aumento del tamaño del archivo de registro o habilitación del crecimiento automático

Si hay espacio disponible en el disco del registro, puede aumentar el tamaño del archivo de registro. El tamaño máximo de los archivos de registro es de 2 terabytes (TB) por cada archivo de registro.

Si el crecimiento automático está deshabilitado, la base de datos está en línea y hay suficiente espacio en el disco, puede llevar a cabo los siguientes pasos:

Aumentar manualmente el tamaño del archivo para producir un solo incremento de tamaño. Estas son recomendaciones generales sobre el tamaño y el crecimiento del tamaño del registro.
Habilitar el crecimiento automático utilizando la instrucción ALTER DATABASE para establecer un incremento de tamaño distinto de cero para la opción FILEGROWTH. Consulte Consideraciones sobre la configuración del crecimiento automático y de la reducción automática en SQL Server.

Nota:

En cualquier caso, si se ha alcanzado el límite del tamaño actual, aumente el valor MAXSIZE.

Compartir a través de

Solución de problemas de un registro de transacciones lleno (Error 9002 de SQL Server)

Opción 1: Ejecute los pasos directamente en un cuaderno ejecutable a través de Azure Data Studio

Opción 2: Seguir el paso manualmente

Causas comunes de un registro de transacciones lleno

Resolución de un registro de transacciones lleno

1. Truncamiento de un registro

Explicación del truncamiento del registro

¿Qué impide el truncamiento del registro?

LOG_BACKUP log_reuse_wait

Consideración del modelo de recuperación de la base de datos

Crear copia de seguridad del registro

Creación de una copia de seguridad del registro de transacciones

ACTIVE_TRANSACTION log_reuse_wait

Detección de transacciones de larga duración

Terminar una transacción

CHECKPOINT log_reuse_wait

AVAILABILITY_REPLICA log_reuse_wait

Replicación, seguimiento de cambios o CDC

Para obtener más información sobre los factores de log_reuse_wait

2. Resolución del volumen de disco completo

Espacio libre en disco

Mover el archivo de registro a otro disco

Agregar un archivo de registro en otro disco

Script de utilidad para las acciones recomendadas

3. Cambio del límite de tamaño del registro o habilitación del crecimiento automático

Aumento del tamaño del archivo de registro o habilitación del crecimiento automático

Comentarios

Recursos adicionales

Compartir a través de

Solución de problemas de un registro de transacciones lleno (Error 9002 de SQL Server)

Opción 1: Ejecute los pasos directamente en un cuaderno ejecutable a través de Azure Data Studio

Opción 2: Seguir el paso manualmente

Causas comunes de un registro de transacciones lleno

Resolución de un registro de transacciones lleno

1. Truncamiento de un registro

Explicación del truncamiento del registro

¿Qué impide el truncamiento del registro?

LOG_BACKUP log_reuse_wait

Consideración del modelo de recuperación de la base de datos

Crear copia de seguridad del registro

Creación de una copia de seguridad del registro de transacciones

ACTIVE_TRANSACTION log_reuse_wait

Detección de transacciones de larga duración

Terminar una transacción

CHECKPOINT log_reuse_wait

AVAILABILITY_REPLICA log_reuse_wait

Replicación, seguimiento de cambios o CDC

Para obtener más información sobre los factores de log_reuse_wait

2. Resolución del volumen de disco completo

Espacio libre en disco

Mover el archivo de registro a otro disco

Agregar un archivo de registro en otro disco

Script de utilidad para las acciones recomendadas

3. Cambio del límite de tamaño del registro o habilitación del crecimiento automático

Aumento del tamaño del archivo de registro o habilitación del crecimiento automático

Contenido relacionado

Comentarios

Recursos adicionales