Archiving Old Rows to a History Table in Small Batches

Old operational rows belong in history, but moving them should not create a missing-data interval. For archiving old rows, DELETE OUTPUT can move each selected batch atomically. Commit small batches and let the next scheduled run continue from what remains.

Set a Retention Boundary Before Archiving Old Rows

Choose a cutoff that represents the retention rule, not the current wall clock in every loop. Fix it for the run and use a half-open predicate. CreatedAt less than the cutoff gives the boundary a clear meaning.

Decide whether the timestamp is UTC, local time, or a business-effective date. The archive process must use the same interpretation as the application. A guessed timezone can move records earlier than the business intended.

I inspect the archive's key and column types before writing the deletion statement. I also ask how archived records will be retrieved. Keeping the data is useful only when its identifiers and meaning remain intact.

Moving rows does not automatically reduce allocated database files or eliminate backup requirements. An archive table in the same database still belongs to that database's full backup. The immediate goal is separating active data and bounding the move workload.

Preserve Source Identity Without Generating New Keys

Run this setup in a test database. The active table generates ActivityId values, while the archive stores those values as ordinary integers. That deliberately preserves identifiers without introducing a second identity generator.

DROP TABLE IF EXISTS dbo.ActivityHistory;
DROP TABLE IF EXISTS dbo.ActivityLog;
CREATE TABLE dbo.ActivityLog
(
    ActivityId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CreatedAt datetime2(0) NOT NULL,
    Details nvarchar(200) NOT NULL
);
CREATE INDEX IX_ActivityLog_CreatedAt
ON dbo.ActivityLog(CreatedAt, ActivityId);
CREATE TABLE dbo.ActivityHistory
(
    ActivityId int NOT NULL PRIMARY KEY,
    CreatedAt datetime2(0) NOT NULL,
    Details nvarchar(200) NOT NULL,
    ArchivedAt datetime2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);
;WITH Digits AS
(
    SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(n)
), Numbers AS
(
    SELECT a.n + 10*b.n + 100*c.n + 1000*d.n AS n
    FROM Digits AS a CROSS JOIN Digits AS b
    CROSS JOIN Digits AS c CROSS JOIN Digits AS d
)
INSERT dbo.ActivityLog(CreatedAt, Details)
SELECT DATEADD(day, n % 400, CONVERT(datetime2(0), '20240101')),
       CONCAT(N'Sample activity ', n)
FROM Numbers;

An archive identity column would require explicit identity insertion to preserve the source values. It would also add session-level cleanup obligations. An ordinary archive key is simpler when the archive never invents its own entity identifiers.

Do not use SELECT INTO blindly when creating the history structure. Identity inheritance and unwanted schema differences can complicate the move. Define the intended archive columns explicitly and verify their types against the source.

Move a Batch in One Atomic Statement

Select the oldest eligible rows using an ordered, updatable common table expression. The identity key breaks ties between equal timestamps. DELETE TOP alone cannot express the ordered selection you need here.

DECLARE @Cutoff datetime2(0) = '20250101';
DECLARE @BatchSize int = 2000;
;WITH Batch AS
(
    SELECT TOP (@BatchSize) ActivityId, CreatedAt, Details
    FROM dbo.ActivityLog
    WHERE CreatedAt < @Cutoff
    ORDER BY CreatedAt, ActivityId
)
DELETE FROM Batch
OUTPUT deleted.*
INTO dbo.ActivityHistory(ActivityId, CreatedAt, Details);
SELECT @@ROWCOUNT AS MovedRows;

Here deleted.* contains exactly the three columns projected by Batch. The archive column list maps them explicitly, leaving ArchivedAt to its default. In a changing production schema, spelling out each deleted column makes that mapping easier to review.

The deletion and OUTPUT insertion belong to the same statement. If the archive insertion fails, the statement does not successfully leave its source rows deleted. A separate INSERT followed by DELETE needs additional transaction and concurrency design to achieve that guarantee.

OUTPUT INTO has important target restrictions. The archive target cannot have enabled triggers, CHECK constraints, or foreign-key participation. Review those restrictions before adapting the sample to an existing history table.

Commit Each Batch and Stop Starting Work on Time

The procedure owns its transactions and rejects an ambient transaction. Each successful batch commits before the next begins. That prevents an outer transaction from quietly turning the entire archive run into one enormous move.

CREATE OR ALTER PROCEDURE dbo.ArchiveActivities
    @Cutoff datetime2(0),
    @BatchSize int = 2000,
    @MaxSeconds int = 120
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    IF @@TRANCOUNT <> 0
        THROW 51000, 'Archive procedure requires no existing transaction.', 1;
    IF @Cutoff IS NULL OR @BatchSize < 1 OR @BatchSize > 10000
       OR @BatchSize IS NULL OR @MaxSeconds < 1 OR @MaxSeconds > 3600
       OR @MaxSeconds IS NULL
        THROW 51001, 'Invalid archive parameters.', 1;
    DECLARE @Deadline datetime2(7) = DATEADD(second, @MaxSeconds, SYSUTCDATETIME());
    DECLARE @Moved int = 1, @TotalMoved bigint = 0;
    WHILE @Moved > 0 AND SYSUTCDATETIME() < @Deadline
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;
            ;WITH Batch AS
            (
                SELECT TOP (@BatchSize) ActivityId, CreatedAt, Details
                FROM dbo.ActivityLog
                WHERE CreatedAt < @Cutoff
                ORDER BY CreatedAt, ActivityId
            )
            DELETE FROM Batch
            OUTPUT deleted.ActivityId, deleted.CreatedAt, deleted.Details
            INTO dbo.ActivityHistory(ActivityId, CreatedAt, Details);
            SET @Moved = @@ROWCOUNT;
            COMMIT TRANSACTION;
            SET @TotalMoved += @Moved;
        END TRY
        BEGIN CATCH
            IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
            THROW;
        END CATCH;
    END;
    SELECT @TotalMoved AS MovedThisRun, @Cutoff AS RetentionCutoff,
           @Deadline AS StartNewBatchDeadline;
END;
GO
EXEC dbo.ArchiveActivities
    @Cutoff = '20250101', @BatchSize = 2000, @MaxSeconds = 120;
GO

The deadline stops the procedure from starting another batch after the time budget. It cannot interrupt an already running or blocked statement at that exact moment. Pair the budget with monitoring and an intentional lock-wait policy for a real job.

Small batches still generate log records and acquire locks. Wide rows, additional indexes, and concurrent access change the practical batch cost. Choose the batch size from observed lock duration and log use rather than treating 2,000 as a universal limit.

Reconcile the Move and Expose Remaining Work

Count both populations after the test and inspect the remaining eligible rows. The sample setup intentionally supplies known identifiers for reconciliation. Production validation should compare a stable source snapshot or recorded batch population, accounting for concurrent application writes.

SELECT N'Active' AS Location, COUNT_BIG(*) AS StoredRows
FROM dbo.ActivityLog
UNION ALL
SELECT N'History', COUNT_BIG(*)
FROM dbo.ActivityHistory;
SELECT COUNT_BIG(*) AS EligibleRowsRemaining
FROM dbo.ActivityLog
WHERE CreatedAt < '20250101';
SELECT ActivityId, COUNT_BIG(*) AS Copies
FROM
(
    SELECT ActivityId FROM dbo.ActivityLog
    UNION ALL
    SELECT ActivityId FROM dbo.ActivityHistory
) AS BothLocations
GROUP BY ActivityId
HAVING COUNT_BIG(*) <> 1;

On my test run, no eligible rows remained after the procedure, and the duplicate check returned nothing. Check payloads as well as counts when testing the design. Equal totals cannot detect a substituted identifier or changed Details value. Archive queries should also preserve the timestamp meaning and identify which location supplied each record.

A rerun continues from rows still present in the active table. Previously committed rows no longer qualify because they are absent there. An archive primary-key violation signals a conflicting history record that requires investigation, not silent suppression.

Schedule Archiving Old Rows as a Bounded Job

Use a SQL Server Agent job step that calls the procedure with approved parameters. Persist each run's cutoff, start time, finish time, moved count, and outcome. A successful run reaching its budget differs from a failed run and from an empty queue.

Prevent overlapping scheduled runs when the workload needs one archive worker. Coordinate application writes that can change the retention column during selection. A supporting date-and-key index makes eligibility checks more focused, but it does not remove concurrency decisions.

I test archiving old rows with an archive insertion failure before scheduling it. I also verify that the committed batches survive a later failed batch. Those checks expose the difference between statement atomicity and whole-job success.

How long can an archive batch block the active workload? Measure that before increasing its size. A job that politely stops starting batches is easier to live with than one that archives its way through breakfast.

For archiving old rows, keep deletion rights limited to the controlled process. Remove the sample objects after testing and preserve the production reconciliation evidence. Retention deserves a repeatable operation, not an occasional handwritten cleanup.

The archive target must also fit the OUTPUT INTO restrictions before the first scheduled run. If existing constraints prevent direct output capture, design a staged, explicitly transactional alternative. Do not remove integrity rules simply to make the example fit.

Measure the run against the actual backlog rather than the original schedule alone. A job can succeed every night while archiving fewer eligible rows than the application creates. Report the oldest remaining eligible timestamp so that gap becomes visible.

Related reading on this blog: Deleting Millions of Rows in Batches Without Filling the Log and 5 Questions Answered OUTPUT Clause: SQL in Sixty Seconds #135.

An archive job is not one giant delete, it is a sequence of verified atomic moves.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.