Este artículo documenta un incidente real en un entorno de producción. Los nombres de servidores y bases de datos han sido modificados por confidencialidad. Los datos de rendimiento (volumen, tamaños, tiempos) son reales.

El contexto: una carga ETL nocturna que se cae al amanecer

Era una mañana de abril cuando el equipo de BI reportó que el servidor de staging del data warehouse estaba inaccesible para escritura. Las queries de lectura funcionaban, pero cualquier intento de escribir en la base de datos BD_STAGING devolvía el clásico error 9002:

The transaction log for database 'BD_STAGING' is full due to 'ACTIVE_TRANSACTION'.

Hasta aquí, nada nuevo: un log lleno suele resolverse en segundos ampliando el fichero. Pero en este caso, el ALTER DATABASE para ampliar el log también fallaba. Y ahí empezaron las preguntas incómodas.

El intento de solución que no funcionó

Lo primero que cualquier DBA prueba ante un log al 100% es ampliar el fichero:

USE master;
GO
ALTER DATABASE BD_STAGING
MODIFY FILE (NAME = 'BD_STAGING_log', SIZE = 350GB);

Resultado: el comando se quedaba colgado indefinidamente. Sin error, sin progreso. Solo esperaba.

El motivo es sutil pero importante: cuando log_reuse_wait_desc = ACTIVE_TRANSACTION, SQL Server no puede truncar el log porque hay una transacción abierta que retiene VLFs activos. Y aunque parezca contraintuitivo, ampliar el log no resuelve el problema raíz: el motor sigue necesitando reservar espacio para esa transacción activa, y el ALTER queda en cola esperando a que el log pueda crecer físicamente.

El diagnóstico: ¿qué retiene el log?

La query estándar para encontrar al culpable:

-- ¿Qué retiene el log?
SELECT name, log_reuse_wait_desc, recovery_model_desc
FROM sys.databases
WHERE name = 'BD_STAGING';

-- Transacción más antigua
DBCC OPENTRAN('BD_STAGING');

-- Detalle de la transacción
SELECT t.transaction_id, t.transaction_begin_time,
       DATEDIFF(MINUTE, t.transaction_begin_time, GETDATE()) AS minutos_abierta,
       s.session_id, s.login_name, s.host_name, s.program_name,
       LEFT(r.text, 200) AS query_actual
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests req ON s.session_id = req.session_id
OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) r
ORDER BY t.transaction_begin_time;

El resultado señalaba a una sesión del agente ETL ejecutando un único INSERT desde la BD de orígenes hacia BD_STAGING.dbo.fact_movimientos:

INSERT INTO BD_STAGING.dbo.fact_movimientos
SELECT *
FROM BD_ORIGEN.dbo.movimientos_diarios;

Una línea. Sin batches. Sin COMMIT intermedio. Sin TOP. 419 millones de filas en una única transacción.

A las 4 horas de ejecución, el log de 293 GB estaba al 100% y la transacción seguía abierta. Cualquier cosa que intentase escribir contra esa BD se quedaba esperando.

Por qué un INSERT masivo así llena el log aunque el modelo sea SIMPLE

Un error común es asumir que en RECOVERY MODEL = SIMPLE no hay que preocuparse por el log. No es cierto. El recovery model controla cuándo SQL Server puede reutilizar el espacio del log, no cuánto se escribe.

En SIMPLE, el log se trunca automáticamente al cerrar cada transacción. Pero si la transacción no termina, el motor no puede reutilizar nada. Cada fila insertada genera una entrada en el log que queda retenida hasta que se haga COMMIT (o ROLLBACK).

419 millones de filas × ~700 bytes por entrada de log ≈ 280 GB de log retenido. Justo lo que ocurrió.

Lo que NO hay que hacer

La tentación inmediata fue clara: matar el SPID. Es lo que probablemente harías si fuera una query interactiva. En un INSERT masivo, casi nunca es buena idea.

Un KILL sobre una transacción que lleva 4 horas insertando significa que el motor tiene que hacer ROLLBACK de esas 4 horas de trabajo. Y el ROLLBACK no es más rápido que el INSERT — habitualmente tarda lo mismo o más, porque tiene que deshacer entrada por entrada del log.

-- Ver progreso del rollback (si lo iniciaste y te arrepentiste)
KILL 196 WITH STATUSONLY;
-- Output: SPID 196: transaction rollback in progress.
--         Estimated rollback completion: 47%. Estimated time remaining: 2h 18m.

En el incidente real, la decisión correcta fue dejar que terminara. El INSERT acabó a las 5h 40min. En cuanto hizo COMMIT, el log se truncó automáticamente y todo volvió a la normalidad.

El patrón correcto: batches con COMMIT intermedio

La forma correcta de cargar volúmenes así es romper la transacción en lotes pequeños. El siguiente patrón es el estándar para cargas masivas controladas:

SET NOCOUNT ON;

DECLARE @BatchSize INT = 500000;
DECLARE @RowsAffected INT = 1;

WHILE (@RowsAffected > 0)
BEGIN
    BEGIN TRANSACTION;

    INSERT INTO BD_STAGING.dbo.fact_movimientos WITH (TABLOCK)
    SELECT TOP (@BatchSize) o.*
    FROM BD_ORIGEN.dbo.movimientos_diarios o
    LEFT JOIN BD_STAGING.dbo.fact_movimientos d
        ON d.id = o.id
    WHERE d.id IS NULL
    ORDER BY o.id;

    SET @RowsAffected = @@ROWCOUNT;

    COMMIT TRANSACTION;

    PRINT CONCAT('Filas insertadas: ', @RowsAffected, ' -- ', GETDATE());
    WAITFOR DELAY '00:00:01';
END

Detalles importantes:

TOP (@BatchSize) — controla el tamaño del lote (500.000 es un buen punto de partida).
LEFT JOIN ... WHERE IS NULL — hace el INSERT idempotente: si se interrumpe, al reanudar no inserta duplicados.
COMMIT intermedio — libera espacio del log al final de cada batch.
WAITFOR DELAY '00:00:01' — pausa de un segundo entre lotes para que IIDR, replicación o backups respiren.
WITH (TABLOCK) — para INSERTs en tablas vacías o con TF 610, permite mínimo logging.

Con este patrón, el mismo volumen de 419M filas se carga en 4-6 horas pero sin riesgo de llenar el log, sin bloquear otras escrituras y con la posibilidad de pausar/reanudar en cualquier momento.

Cómo evitar que vuelva a pasar

Tres reglas que adopté tras este incidente para cualquier carga ETL en producción:

Ningún INSERT/DELETE/UPDATE masivo va sin batches. Si el desarrollador insiste, la query no entra en producción. El estándar es 500K-1M filas por lote.

Alerta proactiva sobre el log. Un job ligero en SQL Agent que cada 5 minutos comprueba el porcentaje de uso del log y avisa por email si supera el 70%. Da tiempo a actuar antes de que se llene:

SELECT
    DB_NAME(database_id) AS bd,
    CAST(used_log_space_in_bytes / 1024.0 / 1024 AS DECIMAL(10,2)) AS log_usado_mb,
    used_log_space_in_percent AS porcentaje
FROM sys.dm_db_log_space_usage
WHERE used_log_space_in_percent > 70;

Documentación del retention policy. Toda BD con cargas ETL debería tener su log_reuse_wait_desc esperado documentado. Si dice ACTIVE_TRANSACTION durante más de 30 minutos en horario fuera del ETL conocido, es un evento que hay que investigar.

La lección

El error 9002 con ACTIVE_TRANSACTION no se resuelve ampliando el log. Se resuelve esperando a que la transacción termine o arreglando el código que la abrió. Ampliar el fichero solo retrasa el problema, y matar el SPID puede triplicar el tiempo de espera.

La regla práctica es simple: si una transacción puede afectar a más de 1 millón de filas, debe ir en batches con COMMIT explícito. No es una optimización opcional — es la diferencia entre un ETL que se cae a las 4h y uno que termina a su ritmo sin que nadie se entere.

Transaction Log lleno en SQL Server: cuando un INSERT de 419 millones de filas bloquea ALTER DATABASE