Excluir dados antigos do SQL Server em lotes retomáveis

Excluir vários anos de dados em uma única transação pode prolongar bloqueios, consumir muito log e tornar uma reversão demorada. Dividir a operação em lotes ajuda, mas uma repetição de DELETE TOP ainda não é um projeto completo. A rotina precisa de critérios estáveis, acesso eficiente e uma definição verificável de término.

Comece convertendo a política de retenção em um predicado. A referência é a data do evento, da ingestão ou do encerramento? Existem registros sujeitos a retenção especial? Dependências e exigências de arquivamento devem estar resolvidas antes de qualquer exclusão em produção.

Fixar o corte durante a execução

O exemplo altera somente dados temporários de demonstração. Três linhas são anteriores ao corte. Com lotes de duas, as quantidades excluídas são 2, 1 e 0; as linhas 4 e 5 permanecem.

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;

O corte é definido uma vez e fica estável. Uma rotina real pode calculá-lo a partir da política e registrá-lo no histórico da execução. Recalculá-lo em cada volta desloca o objetivo durante o processamento e dificulta a conferência.

DELETE TOP sozinho não garante quais linhas serão escolhidas. A CTE ordenada explicita a seleção e EventId desempata horários iguais. Um índice começando pela data de retenção e depois pelo identificador pode evitar leituras repetidas de dados recentes. Outros critérios podem exigir outro índice ou gerar filtragem residual.

O exemplo recusa uma transação já existente. Uma transação externa impediria cada COMMIT de encerrar efetivamente a transação global. Verifique se o mecanismo que executa a rotina não envolve toda a repetição em uma única transação sem deixar isso claro.

Limitar o impacto verdadeiro

Cada lote confirma separadamente. Capture @@ROWCOUNT logo após DELETE, antes que outra instrução altere o valor. O limite de lotes fornece um ponto de parada mesmo com a chegada contínua de registros antigos. Em produção, acrescente um orçamento de tempo e diferencie orçamento esgotado de trabalho concluído.

Uma quantidade fixa de linhas não significa custo fixo. Linhas largas, índices adicionais, exclusões em cascata e gatilhos podem multiplicar log e bloqueios. Ajuste o lote conforme duração, bloqueios, consumo de log e atraso das réplicas. Faça pausas depois do COMMIT, sem manter os bloqueios do lote.

Lotes pequenos não impossibilitam a escalada de bloqueios. Não comece forçando ROWLOCK em todo lugar. Analise primeiro o caminho de acesso e a carga. Exclusões comuns continuam registradas no log; no modelo de recuperação completa, confirmar lotes não substitui os backups de log necessários para reutilizar espaço.

Se houver arquivamento obrigatório, estabeleça uma entrega durável antes de excluir. Uma chamada de rede bem-sucedida não comprova que o destino confirmou a gravação. Use uma etapa local transacional ou um protocolo externo idempotente com confirmações e reconciliação.

Comprovar retomada e término

Após uma interrupção, o mesmo predicado encontra as linhas restantes. Registre corte, quantidade excluída, duração e situação final. Uma queda de conexão durante COMMIT deixa o resultado possivelmente desconhecido; consultar novamente os dados elegíveis é mais confiável que presumir falha.

Um marcador que só avança pode deixar para trás um evento recebido com atraso e data antiga. Programe novas varreduras da faixa elegível ou escolha uma política baseada na ingestão que torne o marcador válido.

READPAST não comprova que terminou. Linhas bloqueadas podem ser ignoradas, produzindo um lote vazio mesmo com trabalho pendente. Valide o restante sem essa hipótese e informe explicitamente o que ficou adiado.

Para volumes temporais muito grandes, remover partições pode ser mais adequado, desde que tabelas e índices tenham estrutura compatível. Essa estratégia também exige validação. Sucesso significa excluir os dados corretos e preservar os demais, não apenas encerrar sem uma exceção.

Referências técnicas: Microsoft Learn: DELETE · Microsoft Learn: Lock escalation.