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

Se aplica a:SQL Server

Este artículo se aplica a las instancias 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

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 lo ayudarán 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 garantizará que el registro se trunca. 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.

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 no puede resolver por sí sola el problema de un archivo de registro lleno; en su lugar, debe detectar por qué el archivo de registro está lleno y no se puede truncar.

¿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 lo ayudará 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 recomendará 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

LOG_BACKUP log_reuse_wait

Las acciones más comunes que puede considerar aquí 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 de los registros de transacciones. Debe considerar detenidamente cada modelo de recuperación de base de datos: realice 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 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 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'

• Realizar copia de seguridad de un registro de transacciones (SQL Server)

• SqlBackup (SMO)

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 flujo de registro (LSN) del registro inicial 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 el transacción; puede que tenga que usar la instrucción KILL. Use esta instrucción con sumo cuidado, especialmente cuando se estén ejecutando procesos críticos que no desea terminar. Para más información, consulte KILL (Transact-SQL)

AVAILABILITY_REPLICA log_reuse_wait

Cuando los cambios de transacción en la réplica de disponibilidad principal 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 puede producirse si la réplica secundaria está establecida para el modo de confirmación sincrónico o asincrónico. 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 AVAILABILITY_REPLICA.

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 ha movido 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'))

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.

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 como paso avanzado para la solución de problemas.

Para 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 de este script de T-SQL que 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 el 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 dos terabytes (TB) por cada archivo.

Si el crecimiento automático está deshabilitado, la base de datos está en línea y hay espacio en el disco suficiente disponible, puede:

• 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.

Pasos siguientes

• ALTER DATABASE (Transact-SQL)
• Administrar el tamaño del archivo de registro de transacciones
• Copias de seguridad de registros de transacciones (SQL Server)
• sp_add_log_file_recover_suspect_db (Transact-SQL)
• MSSQLSERVER_9002
• Cómo puede afectar una estructura de archivos de registro al tiempo de recuperación de la base de datos: Microsoft Tech Community