Diagnosticar por qué el log no puede reutilizarse

El porcentaje utilizado del log sigue creciendo o no disminuye después de la operación esperada. Necesitamos identificar qué condición reporta SQL Server como impedimento actual para truncar y reutilizar registros inactivos.

Qué necesitamos distinguir

Un registro de log describe un cambio necesario para confirmar o recuperar una transacción. Cuando deja de ser necesario puede marcarse como inactivo; truncar permite reutilizar ese espacio interno sin encoger el archivo.

Truncar el log significa marcar espacio interno como reutilizable; no reduce el tamaño físico del archivo. El motivo reportado orienta la siguiente investigación, pero puede cambiar entre capturas y no siempre identifica por sí solo el proceso responsable.

Consulta

/*
    Antes de ejecutar:
        Revisa los permisos y los límites antes de ejecutar la consulta.
        Confirma el alcance, la base activa y el costo posible de la consulta.
        Captura evidencia en varios momentos antes de atribuir una causa.

    Este script es de observación. No aplica correcciones ni modifica datos.
*/

SET NOCOUNT ON;
GO
-- Consulta
SELECT
    d.name AS BaseDatos,
    d.state_desc AS Estado,
    d.recovery_model_desc AS ModeloRecuperacion,
    d.log_reuse_wait_desc AS MotivoEsperaReutilizacion,
    CONVERT(DECIMAL(18, 2), ls.total_log_size_mb) AS LogTotalMb,
    CONVERT(DECIMAL(18, 2), ls.active_log_size_mb) AS LogActivoMb,
    CONVERT
    (
        DECIMAL(6, 2),
        100.0 * ls.active_log_size_mb
        / NULLIF(ls.total_log_size_mb, 0)
    ) AS LogActivoPorcentaje,
    ls.total_vlf_count AS CantidadVlf,
    ls.active_vlf_count AS VlfActivos,
    ls.log_backup_time AS UltimoRespaldoLog,
    CONVERT
    (
        DECIMAL(18, 2),
        ls.log_since_last_log_backup_mb
    ) AS LogDesdeUltimoRespaldoMb
FROM sys.databases AS d
OUTER APPLY sys.dm_db_log_stats(d.database_id) AS ls
WHERE d.state_desc = N'ONLINE'
ORDER BY
    LogActivoPorcentaje DESC,
    d.name;
GO

Parámetros o permisos

La función sys.dm_db_log_stats está disponible desde SQL Server 2016 SP2. Se necesita VIEW SERVER STATE hasta SQL Server 2019, o VIEW SERVER PERFORMANCE STATE desde SQL Server 2022. En una réplica secundaria legible solo estarán disponibles algunos campos. MotivoEsperaReutilizacion refleja la condición observada en el último checkpoint; repite la captura si el valor parece transitorio.

Cómo leer el resultado

NOTHING indica que no se reporta un impedimento actual. LOG_BACKUP orienta a revisar la estrategia y ejecución de respaldos. ACTIVE_TRANSACTION pide localizar transacciones abiertas y sesiones inactivas que conservan una transacción. Motivos como AVAILABILITY_REPLICA, REPLICATION o DATABASE_MIRRORING trasladan la comprobación al flujo correspondiente. No conviene aplicar la misma corrección a motivos distintos ni confundir log activo con tamaño físico asignado.

Qué conclusión sí permite obtener

Permite identificar la condición que actualmente retiene el log de cada base en línea y reunir tamaño activo, VLF y respaldo reciente como contexto.

Qué conclusión todavía no permite obtener

No identifica siempre la sesión o transacción concreta, no reconstruye el crecimiento histórico y no autoriza a cambiar el modelo de recuperación, reducir el archivo o interrumpir procesos.

Siguiente comprobación razonable

Sigue la evidencia del motivo reportado. Para ACTIVE_TRANSACTION, revisa las transacciones abiertas y las sesiones inactivas con una transacción; para LOG_BACKUP, valida trabajos, errores y cadena de respaldos antes de intervenir.

Buscar en SQL Server