Supprimer les anciennes données SQL Server par lots reprenables

Supprimer plusieurs années de données dans une seule transaction peut prolonger les blocages, remplir le journal et rendre une annulation très coûteuse. Le découpage en lots aide, mais une boucle autour de DELETE TOP ne suffit pas. Un traitement fiable exige également une définition stable des lignes éligibles, un accès efficace et un critère de fin vérifiable.

Commencez par traduire la politique de conservation en prédicat. Utilise-t-elle la date de l'événement, son ingestion ou la clôture d'un dossier? Certaines lignes doivent-elles être conservées exceptionnellement? Les dépendances et l'archivage éventuel doivent être réglés avant la première suppression en production.

Fixer la limite pour toute l'exécution

Cet exemple ne supprime que des données temporaires de démonstration. Trois lignes précèdent la limite. Avec des lots de deux, les suppressions comptent 2, 1 puis 0 lignes; les lignes 4 et 5 restent présentes.

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;

La limite est définie une seule fois. En production, calculez-la selon la politique convenue et enregistrez-la dans le journal d'exécution. La recalculer à chaque tour déplace la cible pendant le traitement et complique le rapprochement des résultats.

DELETE TOP seul ne garantit pas quelles lignes seront choisies. La CTE ordonnée rend la sélection explicite; EventId départage les horodatages identiques. Un index commençant par la date de conservation puis l'identifiant peut éviter de reparcourir les lignes récentes. D'autres conditions d'éligibilité peuvent demander un autre index ou laisser un filtrage résiduel.

L'exemple refuse une transaction déjà ouverte. Une transaction extérieure empêcherait les COMMIT de chaque lot de terminer réellement la transaction globale. Vérifiez donc que le framework de traitement n'enveloppe pas silencieusement toute la boucle.

Limiter les effets réels

Chaque lot est validé séparément. Capturez @@ROWCOUNT immédiatement après DELETE avant qu'une autre instruction le modifie. Le nombre maximal de lots fournit un point d'arrêt même si de nouvelles lignes anciennes arrivent. Ajoutez un budget de temps et distinguez un budget épuisé d'un travail terminé.

Un nombre fixe de lignes ne garantit pas un coût fixe. Lignes larges, index secondaires, suppressions en cascade et déclencheurs peuvent multiplier journalisation et verrous. Ajustez la taille selon la durée, les blocages, la consommation du journal et le retard des réplicas. Une pause doit suivre COMMIT, sans conserver les verrous du lot.

Les petits lots ne rendent pas impossible une escalade de verrous. N'imposez pas ROWLOCK partout sans diagnostic. Examinez le chemin d'accès et la charge. Les suppressions ordinaires restent journalisées; en récupération complète, les validations intermédiaires ne remplacent pas les sauvegardes de journal nécessaires à sa réutilisation.

Si une archive est obligatoire, assurez sa durabilité avant la suppression. Un envoi réseau réussi ne prouve pas que la destination a validé les données. Utilisez une étape locale transactionnelle ou un protocole externe idempotent avec confirmation et rapprochement.

Prouver la reprise et la fin

Après une interruption, le même prédicat retrouve les lignes restantes. Enregistrez limite, volumes, durée et statut final. Une coupure pendant COMMIT peut laisser le client dans l'incertitude; revérifier les lignes éligibles est plus fiable que supposer un échec.

Un point de reprise qui avance définitivement peut manquer une ligne reçue tardivement avec une ancienne date d'événement. Prévoyez des balayages renouvelés de la plage éligible ou choisissez une règle fondée sur l'ingestion qui justifie ce point de reprise.

READPAST ne prouve pas la fin du travail. Des lignes verrouillées et ignorées peuvent subsister même après un lot vide. Vérifiez le reste sans cette hypothèse et signalez les lignes différées.

Pour de très grands volumes temporels, retirer des partitions peut mieux convenir, avec une conception compatible et ses propres validations. Le succès consiste à supprimer les bonnes données tout en préservant les autres, pas seulement à terminer sans exception.

Références techniques: Microsoft Learn: DELETE · Microsoft Learn: Lock escalation.