Удаление старых данных SQL Server возобновляемыми пакетами

Удаление нескольких лет данных одной транзакцией может надолго удержать блокировки, занять журнал и сделать откат очень долгим. Разбиение на пакеты уменьшает воздействие, но цикл вокруг DELETE TOP ещё не является готовым решением. Нужны стабильный критерий удаления, эффективный путь доступа и проверяемое определение завершения.

Сначала переведите политику хранения в предикат. Важна дата события, поступления или закрытия дела? Есть ли записи с запретом удаления? Требования к зависимым данным и архивированию необходимо решить до первого запуска на рабочей базе.

Граница не меняется во время запуска

Пример удаляет только временные учебные данные. Перед границей находятся три строки. При размере пакета два количество удалений составляет 2, 1 и 0; строки 4 и 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;

Граница задаётся один раз. Рабочее задание может вычислить её по согласованной политике и сохранить в журнале запуска. Пересчёт на каждой итерации сдвигает цель прямо во время работы и затрудняет последующую сверку.

DELETE TOP сам по себе не обещает определённый порядок выбора. Упорядоченное CTE делает выбор явным, а EventId разрешает совпадение временных меток. Индекс по времени и идентификатору помогает не перечитывать каждый раз более свежие строки. Дополнительные условия могут потребовать другого индекса или остаться остаточным фильтром.

Пример отказывается работать внутри уже открытой транзакции. Иначе отдельные COMMIT не завершали бы внешнюю транзакцию и не давали ожидаемого освобождения ресурсов. Проверьте, не оборачивает ли планировщик весь сценарий общей транзакцией.

Ограничивайте реальную нагрузку

Каждый пакет подтверждается отдельно. Сохраняйте @@ROWCOUNT сразу после DELETE, пока другая инструкция не изменила значение. Максимальное число пакетов даёт точку остановки даже при постоянном поступлении старых событий. Для рабочего задания полезен также лимит времени; исчерпание лимита нельзя обозначать как полное завершение очистки.

Одинаковое число строк не означает одинаковую стоимость. Широкие строки, дополнительные индексы, каскады внешних ключей и триггеры увеличивают журналирование и блокировки. Настраивайте размер по длительности, блокировкам, расходу журнала и задержке реплик. Пауза должна выполняться после COMMIT, без удержания блокировок пакета.

Маленькие пакеты не гарантируют отсутствия эскалации блокировок. Не начинайте с принудительного ROWLOCK повсюду. Сначала изучите путь доступа и фактическую нагрузку. Обычное удаление журналируется; в полной модели восстановления подтверждение пакетов не заменяет резервные копии журнала, необходимые для повторного использования его пространства.

Если требуется архив, обеспечьте надёжную передачу до удаления. Успешный сетевой вызов ещё не доказывает подтверждение архивной записи. Подойдут локальный транзакционный промежуточный этап либо идемпотентный внешний протокол с подтверждениями и сверкой.

Проверяйте повторный запуск и остаток

После прерывания тот же предикат снова найдёт оставшиеся строки. Записывайте границу, число удалений, длительность и итоговый статус. При обрыве соединения во время COMMIT клиент может не знать результат; повторная проверка подходящих данных надёжнее предположения об откате.

Постоянно продвигающийся контрольный маркер способен пропустить поздно поступившую запись со старым временем события. Нужны повторные обходы всего подходящего диапазона либо обоснованная политика по времени поступления. Особенно важно проверить такой сценарий при восстановлении очереди загрузки после длительной остановки.

READPAST не является доказательством завершения. Заблокированные строки могут быть пропущены, и пустой пакет не означает отсутствия работы. Проверяйте остаток без этой предпосылки и отдельно сообщайте об отложенных записях.

Для огромных временных наборов может подойти удаление целых секций, но оно требует совместимой структуры таблиц и индексов. Независимо от метода успех означает удаление согласованных данных при сохранении остальных, а не просто отсутствие исключения у задания.

Техническая документация: Microsoft Learn: DELETE · Microsoft Learn: Lock escalation.