Alte SQL-Server-Daten in wiederholbaren Batches löschen

Ein Aufbewahrungsjob, der mehrere Jahre an Daten in einer Transaktion löscht, kann lange sperren, viel Transaktionsprotokoll belegen und bei einem Fehler lange zurückrollen. Batches verringern diese Auswirkungen, doch eine Schleife um DELETE TOP ist noch kein vollständiger Entwurf. Zusätzlich braucht der Job einen eindeutigen Löschbereich, einen passenden Zugriffspfad und überprüfbare Abschlussbedingungen.

Klären Sie zuerst die fachliche Regel. Zählt das Ereignisdatum, der Eingang oder der Abschluss eines Vorgangs? Gibt es Aufbewahrungssperren? Müssen abhängige Datensätze oder Archivkopien erhalten bleiben? Diese Anforderungen gehören in den Löschentwurf, bevor erstmals Produktionsdaten betroffen sind.

Einen festen Stichtag verwenden

Das Beispiel verändert ausschließlich temporäre Übungsdaten. Drei Zeilen liegen vor dem Stichtag. Die Batchgröße zwei führt zu den Löschmengen 2, 1 und 0. Die Zeilen 4 und 5 bleiben erhalten.

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;

Der Stichtag wird einmal festgelegt und während des Durchlaufs nicht verändert. Ein produktiver Job kann ihn aus der vereinbarten Regel ableiten und im Ausführungsprotokoll speichern. Eine Neuberechnung in jeder Iteration verschiebt dagegen während der Arbeit den Zielbereich.

DELETE TOP allein garantiert keine Reihenfolge. Die geordnete CTE macht die Auswahl ausdrücklich nachvollziehbar; EventId löst gleiche Zeitstempel auf. Ein Index auf Zeitstempel und Kennung kann wiederholte Scans über jüngere Daten vermeiden. Zusätzliche Ausschlussregeln können einen anderen Index erfordern oder als Restfilter wirken.

Das Beispiel lehnt eine bereits offene Transaktion ab. Sonst würden die einzelnen COMMIT-Anweisungen nicht die gesamte umschließende Transaktion beenden. Ein Jobrahmen, der automatisch alles in eine Transaktion einbettet, würde den gewünschten Vorteil zunichtemachen.

Die tatsächlichen Auswirkungen begrenzen

Jeder Batch wird separat bestätigt. @@ROWCOUNT muss direkt nach DELETE gespeichert werden, bevor eine andere Anweisung den Wert verändert. Eine maximale Batchanzahl garantiert einen Haltepunkt, auch wenn neue alte Datensätze eintreffen. Ergänzen Sie im Scheduler ein Zeitbudget und unterscheiden Sie zwischen ausgeschöpftem Budget und vollständig erledigter Arbeit.

Eine konstante Zeilenanzahl bedeutet keine konstante Belastung. Breite Zeilen, zusätzliche Indizes, kaskadierende Fremdschlüssel und Trigger können Protokollvolumen und Sperren vervielfachen. Passen Sie die Größe anhand von Dauer, Blockierungen, Protokollverbrauch und Replikatverzögerung an. Eine Pause gehört hinter COMMIT, nicht in eine noch sperrende Transaktion.

Kleine Batches verhindern Sperreskalation nicht grundsätzlich. Erzwingen Sie deshalb nicht vorschnell überall ROWLOCK. Prüfen Sie zuerst Zugriffspfad und tatsächliche Belastung. Normale Löschoperationen bleiben protokolliert; im vollständigen Wiederherstellungsmodell ersetzen Batch-Commits keine notwendigen Protokollsicherungen.

Eine erforderliche Archivierung braucht eine dauerhafte Übergabe vor dem Löschen. Ein erfolgreicher Netzwerkaufruf beweist keine bestätigte Archivspeicherung. Geeignet sind beispielsweise eine lokale transaktionale Zwischenablage oder ein wiederholbares externes Protokoll mit Bestätigung und Abgleich.

Wiederanlauf und Abschluss nachweisen

Nach einer Unterbrechung findet die Schleife anhand desselben Prädikats die verbleibenden Zeilen erneut. Protokollieren Sie Stichtag, Löschmengen, Laufzeit und Endstatus. Bei einem Verbindungsabbruch während COMMIT kann dessen Erfolg unklar sein; eine erneute Prüfung der Eignung ist verlässlicher als eine Vermutung.

Ein dauerhaft fortgeschriebener Kontrollpunkt kann verspätet eingetroffene Ereignisse mit altem Zeitstempel übersehen. Planen Sie erneute Durchläufe über den gesamten relevanten Bereich oder wählen Sie eine fachlich passende Eingangszeitregel.

READPAST ist kein allgemeiner Abschlussnachweis. Übersprungene gesperrte Zeilen können zurückbleiben, obwohl ein Durchlauf nichts findet. Prüfen Sie den Restbestand ohne diese Annahme und melden Sie verschobene Arbeit ausdrücklich.

Für sehr große zeitbasierte Bestände kann das Entfernen ganzer Partitionen passen. Dafür müssen Tabellen und Indizes vorbereitet sein. Entscheidend bleibt stets, dass die richtigen Daten gelöscht und die übrigen erhalten wurden.

Technische Referenzen: Microsoft Learn: DELETE · Microsoft Learn: Lock escalation.