Archiving a Hot Audit Table Without Making the Archive the Outage

A large audit table needed old data moved to an archive table. A naïve version is one large insert followed by one large delete. Against a live system that can create a long transaction, sustained log pressure, long-lived locks, a large rollback surface and poor progress visibility.

The requirement was therefore not simply “copy old rows.” It was: move eligible rows in bounded, atomic units that can be safely repeated after interruption.

Stage identity, not the full row§

The process first materialised the keys of rows eligible for archiving into a temporary table. Simplified — the real schema and predicates were application-specific and are intentionally not reproduced here:

CREATE TABLE #ToArchive
(
    audit_id bigint NOT NULL
        PRIMARY KEY CLUSTERED
);

INSERT INTO #ToArchive (audit_id)
SELECT a.audit_id
FROM dbo.LiveAudit AS a
WHERE a.created_at < @Cutoff
  AND NOT EXISTS
  (
      SELECT 1
      FROM dbo.ReferencedRows AS r
      WHERE r.audit_id = a.audit_id
  );

The staging table has one job: define the workset by primary key. That gives the batch loop cheap, deterministic identifiers and avoids repeatedly evaluating the full eligibility predicate for every slice.

Referential integrity is part of the algorithm§

Some audit rows were still referenced elsewhere. Deleting them would violate a foreign key. Rather than waiting for the delete to fail, candidate selection excluded referenced rows: old enough, archive-eligible, and not currently referenced, in that order.

Process one bounded batch§

A second temporary table represents the current slice:

INSERT INTO #CurrentBatch (audit_id)
SELECT TOP (@BatchSize) audit_id
FROM #ToArchive
ORDER BY audit_id;

The critical boundary is the transaction around each batch:

BEGIN TRANSACTION;

BEGIN TRY

    INSERT INTO dbo.AuditArchive (...)
    SELECT ...
    FROM dbo.LiveAudit AS a
    WHERE EXISTS
    (
        SELECT 1
        FROM #CurrentBatch AS b
        WHERE b.audit_id = a.audit_id
    );

    DELETE a
    FROM dbo.LiveAudit AS a
    WHERE EXISTS
    (
        SELECT 1
        FROM #CurrentBatch AS b
        WHERE b.audit_id = a.audit_id
    );

    COMMIT TRANSACTION;

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    THROW;

END CATCH;

Insert and delete belong in the same transaction. Otherwise a failure can leave a row copied to the archive but still present in the live table, or create the opposite inconsistency if the ordering is reversed. Atomic per-batch movement gives a small correctness boundary: a batch is moved, or it is not moved.

Remove work only after commit§

After the batch commits, its IDs are removed from #ToArchive, the current batch is cleared, and the loop continues:

1. 01 SELECT

   Take a bounded slice of candidate IDs into #CurrentBatch.

   ↓

2. 02 MOVE

   Insert into the archive and delete from the live table in one transaction.

   ↓

3. 03 COMMIT CONFIRMED

   Both sides land together, or neither does.

   ↓

4. 04 CLEAR

   Remove the committed IDs from the workset and repeat.

If the transaction fails, those IDs are not removed from the workset. The THROW ends the current execution; on a deliberate retry or rerun, the same source-driven candidate logic makes the failed rows eligible again. Rows moved by a committed batch are no longer in the live source and therefore are not selected again.

Throttling is an operational control§

The implementation uses a configurable delay between batches. The initial policy used a batch size in the order of ten thousand rows and a short pause between commits. Those are not universal values — the useful controls are batch size, which governs transaction size and lock footprint, and batch delay, which governs how much pressure is applied over time. The archive shares log throughput, storage and locks with production traffic, so maximum maintenance throughput is not automatically the right target.

A note on the original NOLOCK choice§

The source implementation used WITH (NOLOCK) while building the candidate set, to reduce blocking during the initial scan. That was a deliberate availability trade-off.

The general lesson: reducing blocking and preserving correctness are separate requirements. A hint that helps one can weaken the other.

Restartability is more than dropping temp tables§

The script cleans transient objects at startup, but restartability comes primarily from the movement boundaries: candidates are keys, not a second copy of truth; each batch is atomic; committed rows disappear from the live source; failed batches roll back; and the workset can be reconstructed. That is what makes rerunning the operation understandable.

Progress should be observable§

For a long operation, progress is an operational feature. Useful signals include candidate count, current batch size, batches committed, rows moved, elapsed time, remaining candidates, log utilisation and blocking/waits. The original work focused on the archive algorithm; in a fully productised framework I would expose those signals to monitoring rather than rely only on console output.

What batching does not guarantee§

Batched work is not automatically harmless. One batch size can be too large for one workload and trivial for another. Archive indexes, foreign-key checks, log throughput and storage bandwidth still matter, and candidate-selection isolation still defines what “eligible” means. The design does not make those constraints disappear — it exposes controls for them.

Takeaway§

The archive process ended up looking less like a copy statement and more like a small work processor: discover work, take a bounded slice, move it atomically, commit, record progress, throttle, repeat. That is the pattern I reach for when maintenance has to coexist with a live workload.

The safest archive is not the one that finishes in the fewest seconds. It is the one whose failure and concurrency behaviour you can explain before you run it.