Borrar datos antiguos de SQL Server en lotes reanudables

Borrar varios años de datos en una sola transacción puede mantener bloqueos, consumir mucho registro y tardar demasiado en revertirse. Dividir el trabajo en lotes ayuda, pero una repetición de DELETE TOP no completa el diseño. Un proceso fiable necesita una definición estable de los datos elegibles, un acceso eficiente y una condición de finalización comprobable.

Primero convierte la política de retención en un predicado. ¿Cuenta la fecha del evento, la de ingreso o la de cierre? ¿Existen registros sujetos a conservación especial? Las dependencias y los requisitos de archivo deben resolverse antes de eliminar datos reales.

Mantener fija la fecha de corte

El ejemplo solo elimina datos temporales de práctica. Tres filas son anteriores al corte. Con lotes de dos, los recuentos son 2, 1 y 0; permanecen las filas 4 y 5.

CREATE TABLE #Events (
    EventId bigint NOT NULL PRIMARY KEY,
    OccurredAtUtc datetime2(0) NOT NULL
);
CREATE INDEX IX_Events_Retention ON #Events(OccurredAtUtc, EventId);
INSERT #Events VALUES
(1, '20220901'), (2, '20220902'), (3, '20220903'),
(4, '20221001'), (5, '20221002');

DECLARE @Cutoff datetime2(0) = '20221001';
DECLARE @BatchSize int = 2, @Rows int = 1, @Batches int = 0;
SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
    THROW 50001, 'Run this example without an existing transaction.', 1;

WHILE @Rows > 0 AND @Batches < 10
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        ;WITH Victims AS (
            SELECT TOP (@BatchSize) EventId, OccurredAtUtc
            FROM #Events
            WHERE OccurredAtUtc < @Cutoff
            ORDER BY OccurredAtUtc, EventId
        )
        DELETE FROM Victims;
        SET @Rows = @@ROWCOUNT;
        COMMIT TRANSACTION;
        SET @Batches += 1;
        SELECT @Batches AS BatchNumber, @Rows AS DeletedRows;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;
SELECT EventId, OccurredAtUtc FROM #Events ORDER BY EventId;
DROP TABLE #Events;

El corte se establece una sola vez y no cambia durante la ejecución. Un trabajo real puede calcularlo según la política y registrarlo. Recalcularlo en cada iteración mueve el objetivo mientras se procesa y dificulta comprobar el resultado.

DELETE TOP por sí solo no garantiza qué filas elegibles seleccionará. La CTE ordenada hace explícita la elección y EventId resuelve empates de fecha. Un índice que comience por la marca temporal y continúe por el identificador evita recorrer repetidamente datos más recientes. Otros criterios pueden requerir otro índice o dejar filtrado residual.

El ejemplo rechaza una transacción ya abierta porque una transacción exterior impediría que cada COMMIT terminara realmente la operación global. Comprueba que el sistema que ejecuta el trabajo no envuelve toda la repetición en una sola transacción.

Acotar el impacto real

Cada lote se confirma por separado. Guarda @@ROWCOUNT inmediatamente después de DELETE, antes de que otra instrucción lo cambie. El máximo de lotes ofrece un punto de parada aunque sigan llegando registros antiguos. En producción añade un presupuesto de tiempo y distingue entre agotarlo y terminar todo el trabajo.

Un número fijo de filas no implica un coste fijo. Filas anchas, índices secundarios, cascadas y desencadenadores pueden multiplicar bloqueos y escritura en el registro. Ajusta el tamaño según duración, bloqueos, consumo del registro y retraso de réplicas. Cualquier pausa debe ir después de confirmar, sin conservar los bloqueos del lote.

Los lotes pequeños no garantizan que nunca haya escalado de bloqueos. No empieces imponiendo ROWLOCK en todas partes. Revisa el acceso y la carga real. Los borrados ordinarios siguen registrándose; con recuperación completa, confirmar lotes no sustituye las copias del registro necesarias para su reutilización.

Si se exige archivo, garantiza una entrega duradera antes de borrar. Que una llamada de red termine correctamente no demuestra que el archivo se haya confirmado. Usa una etapa local transaccional o un protocolo externo idempotente con confirmaciones y conciliación.

Verificar reintentos y finalización

Después de una interrupción, el mismo predicado vuelve a encontrar las filas pendientes. Registra corte, filas borradas, duración y estado final. Si se pierde la conexión durante COMMIT, puede desconocerse el resultado; volver a consultar la elegibilidad es mejor que asumir que falló.

Un punto de avance permanente puede omitir registros recibidos tarde con fecha antigua. Programa nuevas revisiones de todo el rango elegible o utiliza una política basada en ingreso que justifique ese punto de avance.

READPAST no es una prueba de finalización. Puede saltar filas bloqueadas y devolver un lote vacío aunque quede trabajo. Valida el resto sin esa suposición y comunica lo aplazado.

En conjuntos temporales enormes puede convenir retirar particiones, pero exige tablas e índices compatibles y validaciones propias. El éxito consiste en borrar exactamente los datos acordados y conservar los demás, no simplemente en que el trabajo termine sin errores.

Referencias técnicas: Microsoft Learn: DELETE · Microsoft Learn: Lock escalation.