Some tables only exist to pass data along: outbox tables, event queues, staging and audit feeds. They take millions of inserts a day, nobody needs the rows after a day or two, and a nightly DELETE ... WHERE CreatedAt < ... gets slower, bloats the log and blocks the application. Daily partitioning fixes that, but only if somebody keeps the partitions in shape every single day.
This post describes the framework I built and tested to do exactly that on Azure SQL Database: a set of table-agnostic procedures, one configuration row per table and one scheduled job. It keeps empty partitions ready ahead of today, switches expired days out in a metadata-only operation, and never needs a human. It also includes the bug that broke it for a while: three milliseconds that turned every archive run into error 7715.
- Sliding window: daily
RANGE RIGHTpartitions. New days areSPLITahead of time, expired days areSWITCHed to an archive table,TRUNCATEd, and the empty boundary isMERGEd. - Table-agnostic: every procedure takes
@TableNameand reads retention and buffer days from one config table. - Safe by default: archiving is a dry run unless
@Execute = 1, one transaction per partition, row counts verified after every SWITCH, every step logged. - The bug: boundaries read back as
DATETIME2(0)rounded23:59:59.997up to the next midnight, a value that does not exist, soMERGE RANGEfailed with error 7715. - The design lesson: with
RANGE RIGHT, use plain midnight boundaries and carry them at the column's exact type.
The architecture
A Windows Task Scheduler job on an admin VM runs a PowerShell wrapper once a day. The wrapper calls one stored procedure, usp_PartitionMaintenance, which is a pure orchestrator: it holds no boundary logic of its own and calls four child procedures in a fixed order.
| Object | Type | Job |
|---|---|---|
PartitionMaintenanceConfig | Table | One row per managed table: partition column, archive table, retention days, future buffer days, enabled flag. |
PartitionMaintenanceLog | Table | One row per partition processed: run ID, boundary, rows, duration, status, error. |
ufn_GetPartitionBoundaryMap | Inline function | The single source of truth: partition number, lower and upper boundary, row count. |
usp_ValidatePartitionEnvironment | Procedure | Checks the table, archive table, scheme, function and clustered index all exist and line up. |
usp_ShowPartitionStatus | Procedure | Lists every partition with its boundary and row count. |
usp_CreateFuturePartitions | Procedure | Keeps FutureBufferDays of empty daily partitions ahead of today. Idempotent. |
usp_ArchiveOldPartitions | Procedure | SWITCH, TRUNCATE and MERGE for every partition older than RetentionDays. Dry run by default. |
usp_PartitionMaintenance | Procedure | The orchestrator and the only thing the schedule calls. |
The daily run is always the same six steps: validate, show status, read config, create future partitions, archive expired partitions, show status again. The "before" and "after" status listings end up in the log file, which makes any change easy to review the next morning.
The sliding window
The example table is dbo.EventOutbox, partitioned by day on CreatedAt, with one day of retention and a 15-day future buffer. Every run does four things:
- SPLIT new boundaries until there are 15 empty daily partitions ahead of today. Incoming rows always land in a partition that already exists, and the rightmost, open-ended partition stays empty, so a SPLIT never has to move rows.
- SWITCH each expired partition into
dbo.EventOutbox_Archive, a non-partitioned table with exactly the same structure. This is a metadata change: no rows are copied. - TRUNCATE the archive table, which deallocates pages instead of deleting rows one by one.
- MERGE the boundary between two empty partitions, so the partition count stays stable and no data moves.
Why midnight boundaries
With RANGE RIGHT, a boundary value belongs to the partition on its right. A boundary of 2026-11-15 00:00:00.000 therefore gives each day the range [00:00, next 00:00): no gaps, no overlap, no arithmetic. An older convention puts the boundary at 23:59:59.997, the last tick of the old DATETIME type. On a datetime2(3) column that is subtly wrong: rows stamped .997, .998 or .999 land in the next day's partition. It is also the convention that caused the bug further down.
Why MERGE the lower boundary
MERGE RANGE removes a boundary and combines the two partitions on either side of it. For RANGE RIGHT, the partition on the right of the boundary is folded into the one on the left, and if it holds rows they are physically moved. Merging the upper boundary of a partition you have just emptied folds the next day's data into it, which turns a metadata operation into a fully logged data move. Merging the lower boundary combines two empty partitions instead: the emptied day and the permanently empty leftmost partition. Nothing moves.
Why keys on boundary values, not partition numbers
Every MERGE renumbers all partitions to its right. A loop that captures partition numbers once and then archives "partition 3, then partition 4" will switch out the wrong partition as soon as more than one day is due, for example after a missed run. The archive procedure below re-reads the boundary map before every partition and identifies partitions by their boundary values, which never change.
Deploying it to a table: eight steps
The shared objects (config and log tables, function, five procedures) are created once per database. Each table you want to manage then takes eight short steps, in this order.
| Step | What | Why it matters |
|---|---|---|
| 00 | Drop the old non-partitioned table, only if it is empty | A partitioned table cannot be created over an existing one, and TRUNCATE leaves the object in place. The script checks for zero rows immediately before the drop. |
| 01 | Create the partition function with one seed boundary | Computed from SYSUTCDATETIME(), so no hardcoded dates in any environment. |
| 02 | Create the partition scheme | ALL TO ([PRIMARY]) on Azure SQL, where there is one user filegroup. |
| 03 | Create the partitioned table | The partition column must be part of the clustered primary key (see below). |
| 04 | Create the archive table | Same columns, same key, same filegroup, not partitioned: the requirements for SWITCH. |
| 05 | Register the table in the config | Retention and buffer days per table, the most often forgotten step. |
| 06 | Expand the future partitions | Turns the single seed into a full future buffer before any data arrives. |
| 07 | Verify | usp_ShowPartitionStatus: the boundary count and dates should match the buffer. |
EventId alone; the partitioned version uses (EventId, CreatedAt). That means the database no longer guarantees EventId is unique on its own. For an outbox whose IDs are generated uniquely by the application this is acceptable, but decide it consciously, and say so in your design notes.Safety features
- Dry run by default. With
@Execute = 0the archive step only lists which partitions are due; nothing is switched, truncated or merged. (Future partitions are still created, because adding empty partitions is never destructive.) Running the scheduled job in dry-run mode for a few days first is a cheap way to build trust. - An off switch per table.
IsEnabled = 0in the config skips that table without touching the schedule. - One transaction per partition. A failure rolls back that partition only and stops the run, so the next day starts clean.
- Verification after every SWITCH. The archive row count must equal the source partition's rows, and the source partition must be empty, before anything is truncated.
- Idempotent SPLIT. Existing boundaries are skipped, so a rerun after a partial failure just finishes the job.
- A guard against data movement. If the rightmost, open-ended partition ever contains rows (the buffer ran out), the procedure stops instead of splitting a full partition.
- A log row for every partition with the boundary, rows, duration and any error.
The 3-millisecond bug
During testing, every archive run in execute mode failed with:
Msg 7715, Level 16
The specified partition range value could not be found.
The partition function was datetime2(3) with boundaries like 23:59:59.997. Somewhere in the procedures, the boundary values were read back with CONVERT(DATETIME2(0), value), and the variables holding them were also declared DATETIME2(0). Converting to a lower precision rounds, it does not truncate:
SELECT CONVERT(DATETIME2(0), CAST('2026-11-14 23:59:59.997' AS DATETIME2(3)));
-- 2026-11-15 00:00:00
So the procedure asked SQL Server to MERGE RANGE ('2026-11-15 00:00:00'), a boundary that did not exist. On top of that, the MERGE statement was built as a string with CONVERT(VARCHAR(19), @Boundary, 120), which drops milliseconds a second time. The status procedure displayed boundaries with the same format, so the listing looked perfectly fine while the values were wrong.
The fix had two parts, and both are in the scripts below:
- Every variable, table column and
CONVERTthat holds a boundary uses exactly the partition column's type, hereDATETIME2(3). - Boundaries are passed to
SPLITandMERGEas typedsp_executesqlparameters, never formatted into strings.
The deeper lesson is the boundary convention. With midnight boundaries there is nothing to round in the first place, which is why this version uses them.
Scheduling
The PowerShell wrapper runs usp_PartitionMaintenance through sqlcmd -b, so any SQL error becomes a non-zero exit code that Task Scheduler can see. Each run writes a timestamped log file, and logs older than 30 days are removed automatically. The Task Scheduler settings that matter:
- Create Task (not Basic Task), "Run whether user is logged on or not".
- Trigger: daily at a quiet time, for example 00:30 UTC.
- Action:
powershell.exe -ExecutionPolicy Bypass -File "C:\PartitionMaintenance\Scripts\PartitionMaintenance.ps1" - Settings: run as soon as possible after a missed start, restart every 15 minutes up to 3 times on failure, stop the task after 2 hours.
Export-Clixml (encrypted for that Windows account on that machine) and passes it to sqlcmd through the SQLCMDPASSWORD environment variable instead of the command line. Better still, use Microsoft Entra ID authentication with a managed identity and no password at all. Either way, give the job its own login with only the permissions it needs.Tested
Before publishing, I ran the exact scripts from this post against a test table on Azure SQL Database: seven days of data, one day of retention and a three-day buffer, including one row stamped 23:59:59.999. The dry run listed the five expired days and changed nothing. The real run archived those five days (501 rows) in five transactions, merged every expired boundary, kept the last two days (200 rows), created the future buffer with every boundary at midnight, and kept the 23:59:59.999 row in its own day. A second run changed nothing. Each partition took about 2.2 seconds, almost all of it DDL (SWITCH, TRUNCATE, MERGE) rather than rows, which is the point of switching instead of deleting.
Lessons learned
- Carry boundaries at the partition column's exact type. Converting to a lower precision rounds, and a rounded boundary does not exist.
- Pass boundaries as typed parameters. Formatting a datetime into a string is a second place to lose precision.
- With RANGE RIGHT, use midnight boundaries. They are exact, readable and need no "minus 3 ms".
- Partition numbers change after every MERGE. Identify partitions by boundary value and re-read the map each time.
- Only merge empty partitions. Merging the lower boundary of an emptied partition keeps MERGE metadata-only.
- Partitioning changes your keys. The partition column joins every unique index; decide what that means for uniqueness.
- Default to a dry run, and log everything. Trust in an automated destructive job is earned one clean log at a time.
The scripts
Everything below is generic: replace EventOutbox, EventId, Payload and CreatedAt with your own names. Tested end to end as described above. It has not yet been rolled out to a production system, so test it in your own non-production database first.; for this post it has been anonymised and moved to midnight boundaries, and this exact version was tested as described above. Test it in a non-production database before you rely on it.
1. Config and log tables (once per database)
IF OBJECT_ID(N'dbo.PartitionMaintenanceConfig') IS NULL
CREATE TABLE dbo.PartitionMaintenanceConfig
(
TableName SYSNAME NOT NULL PRIMARY KEY,
PartitionColumn SYSNAME NOT NULL,
ArchiveTableName SYSNAME NOT NULL,
RetentionDays INT NOT NULL,
FutureBufferDays INT NOT NULL,
IsEnabled BIT NOT NULL DEFAULT (1)
);
IF OBJECT_ID(N'dbo.PartitionMaintenanceLog') IS NULL
CREATE TABLE dbo.PartitionMaintenanceLog
(
LogID BIGINT IDENTITY(1,1) PRIMARY KEY,
RunID UNIQUEIDENTIFIER NOT NULL,
RunDateUTC DATETIME2(3) NOT NULL DEFAULT (SYSUTCDATETIME()),
TableName SYSNAME NOT NULL,
StepName VARCHAR(50) NOT NULL,
PartitionNumber INT NULL,
BoundaryValue DATETIME2(3) NULL,
RowsProcessed BIGINT NULL,
DurationMS INT NULL,
Status VARCHAR(20) NOT NULL,
ErrorNumber INT NULL,
ErrorMessage NVARCHAR(4000) NULL
);2. ufn_GetPartitionBoundaryMap (the single source of truth)
CREATE OR ALTER FUNCTION dbo.ufn_GetPartitionBoundaryMap (@TableName SYSNAME)
RETURNS TABLE
AS
RETURN
(
WITH meta AS
(
SELECT t.object_id, ps.name AS PartitionScheme,
pf.function_id, pf.name AS PartitionFunction
FROM sys.tables t
JOIN sys.indexes i ON i.object_id = t.object_id AND i.index_id = 1
JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
WHERE t.name = @TableName
),
b AS
( -- full precision, matching the datetime2(3) partition column
SELECT function_id, boundary_id, CONVERT(DATETIME2(3), value) AS BoundaryValue
FROM sys.partition_range_values
)
SELECT m.PartitionScheme, m.PartitionFunction,
p.partition_number AS PartitionNumber,
lo.BoundaryValue AS LowerBoundary, -- inclusive (RANGE RIGHT)
hi.BoundaryValue AS UpperBoundary, -- exclusive; NULL = open-ended
p.row_count AS PartitionRows
FROM meta m
JOIN sys.dm_db_partition_stats p ON p.object_id = m.object_id AND p.index_id = 1
LEFT JOIN b hi ON hi.function_id = m.function_id AND hi.boundary_id = p.partition_number
LEFT JOIN b lo ON lo.function_id = m.function_id AND lo.boundary_id = p.partition_number - 1
);3. usp_ShowPartitionStatus and usp_ValidatePartitionEnvironment
CREATE OR ALTER PROCEDURE dbo.usp_ShowPartitionStatus @TableName SYSNAME
AS
BEGIN
SET NOCOUNT ON;
IF OBJECT_ID(@TableName) IS NULL THROW 50001, 'Table does not exist.', 1;
SELECT PartitionNumber,
CONVERT(VARCHAR(23), LowerBoundary, 121) AS FromInclusive,
ISNULL(CONVERT(VARCHAR(23), UpperBoundary, 121), 'MAX') AS ToExclusive,
PartitionRows
FROM dbo.ufn_GetPartitionBoundaryMap(@TableName)
ORDER BY PartitionNumber;
END;
GO
CREATE OR ALTER PROCEDURE dbo.usp_ValidatePartitionEnvironment @TableName SYSNAME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Archive SYSNAME, @Column SYSNAME;
SELECT @Archive = ArchiveTableName, @Column = PartitionColumn
FROM dbo.PartitionMaintenanceConfig WHERE TableName = @TableName;
IF @Archive IS NULL THROW 50001, 'No config row for this table.', 1;
IF OBJECT_ID(@TableName) IS NULL THROW 50002, 'Table does not exist.', 1;
IF OBJECT_ID(@Archive) IS NULL THROW 50003, 'Archive table does not exist.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.ufn_GetPartitionBoundaryMap(@TableName))
THROW 50004, 'Table is not partitioned.', 1;
IF NOT EXISTS (SELECT 1
FROM sys.index_columns ic
JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE ic.object_id = OBJECT_ID(@TableName) AND ic.index_id = 1
AND ic.partition_ordinal = 1 AND c.name = @Column)
THROW 50005, 'Config PartitionColumn does not match the table.', 1;
PRINT 'Environment validated.';
END;4. usp_CreateFuturePartitions (midnight boundaries, idempotent)
CREATE OR ALTER PROCEDURE dbo.usp_CreateFuturePartitions @TableName SYSNAME
AS
BEGIN
SET NOCOUNT ON; SET XACT_ABORT ON;
DECLARE @Buffer INT, @Enabled BIT, @Function SYSNAME, @Scheme SYSNAME,
@Today DATE = CAST(SYSUTCDATETIME() AS DATE),
@Last DATETIME2(3), @Next DATE, @Target DATE, @Boundary DATETIME2(3),
@SQL NVARCHAR(MAX), @Created INT = 0;
SELECT @Buffer = FutureBufferDays, @Enabled = IsEnabled
FROM dbo.PartitionMaintenanceConfig WHERE TableName = @TableName;
IF @Buffer IS NULL THROW 50001, 'No config row for this table.', 1;
IF @Enabled = 0 BEGIN PRINT 'Disabled.'; RETURN; END;
SELECT TOP (1) @Function = PartitionFunction, @Scheme = PartitionScheme
FROM dbo.ufn_GetPartitionBoundaryMap(@TableName);
-- Guard: never split a partition that already holds rows
IF EXISTS (SELECT 1 FROM dbo.ufn_GetPartitionBoundaryMap(@TableName)
WHERE UpperBoundary IS NULL AND PartitionRows > 0)
THROW 50002, 'The open-ended partition contains rows: the future buffer ran out. Investigate before splitting.', 1;
SELECT @Last = MAX(UpperBoundary) FROM dbo.ufn_GetPartitionBoundaryMap(@TableName)
WHERE UpperBoundary IS NOT NULL;
SET @Target = DATEADD(DAY, @Buffer + 1, @Today); -- last boundary we want
SET @Next = DATEADD(DAY, 1, CAST(@Last AS DATE));
WHILE @Next <= @Target
BEGIN
SET @Boundary = CAST(@Next AS DATETIME2(3)); -- 00:00:00.000
IF NOT EXISTS (SELECT 1 FROM dbo.ufn_GetPartitionBoundaryMap(@TableName)
WHERE UpperBoundary = @Boundary)
BEGIN
SET @SQL = N'ALTER PARTITION SCHEME ' + QUOTENAME(@Scheme) + N' NEXT USED [PRIMARY];';
EXEC sp_executesql @SQL;
SET @SQL = N'ALTER PARTITION FUNCTION ' + QUOTENAME(@Function) + N'() SPLIT RANGE (@b);';
EXEC sp_executesql @SQL, N'@b DATETIME2(3)', @b = @Boundary; -- typed, never a string
SET @Created += 1;
PRINT 'Created boundary ' + CONVERT(VARCHAR(23), @Boundary, 121);
END;
SET @Next = DATEADD(DAY, 1, @Next);
END;
PRINT 'Future partitions created: ' + CAST(@Created AS VARCHAR(10));
END;5. usp_ArchiveOldPartitions (SWITCH, verify, TRUNCATE, MERGE empty)
CREATE OR ALTER PROCEDURE dbo.usp_ArchiveOldPartitions
@TableName SYSNAME,
@Execute BIT = 0 -- 0 = dry run (default), 1 = do it
AS
BEGIN
SET NOCOUNT ON; SET XACT_ABORT ON;
DECLARE @Archive SYSNAME, @Retention INT, @Enabled BIT, @Function SYSNAME,
@Cutoff DATETIME2(3), @RunID UNIQUEIDENTIFIER = NEWID(),
@P INT, @Lower DATETIME2(3), @Upper DATETIME2(3), @Rows BIGINT,
@ArchRows BIGINT, @SQL NVARCHAR(MAX), @T0 DATETIME2(3), @Loops INT = 0;
SELECT @Archive = ArchiveTableName, @Retention = RetentionDays, @Enabled = IsEnabled
FROM dbo.PartitionMaintenanceConfig WHERE TableName = @TableName;
IF @Archive IS NULL THROW 50001, 'No config row for this table.', 1;
IF @Enabled = 0 BEGIN PRINT 'Disabled.'; RETURN; END;
SELECT TOP (1) @Function = PartitionFunction FROM dbo.ufn_GetPartitionBoundaryMap(@TableName);
SET @Cutoff = CAST(DATEADD(DAY, -@Retention, CAST(SYSUTCDATETIME() AS DATE)) AS DATETIME2(3));
PRINT 'Retention cutoff (UTC): ' + CONVERT(VARCHAR(23), @Cutoff, 121);
-- What is due: every partition that ends on or before the cutoff,
-- except the permanently empty leftmost partition.
SELECT PartitionNumber, LowerBoundary, UpperBoundary, PartitionRows
FROM dbo.ufn_GetPartitionBoundaryMap(@TableName)
WHERE UpperBoundary <= @Cutoff AND NOT (PartitionNumber = 1 AND PartitionRows = 0)
ORDER BY PartitionNumber;
IF @Execute = 0 BEGIN PRINT 'Dry run: nothing changed.'; RETURN; END;
WHILE @Loops < 1000
BEGIN
SET @Loops += 1; SET @P = NULL;
-- Re-read the map every time: each MERGE renumbers partitions.
SELECT TOP (1) @P = PartitionNumber, @Lower = LowerBoundary,
@Upper = UpperBoundary, @Rows = PartitionRows
FROM dbo.ufn_GetPartitionBoundaryMap(@TableName)
WHERE UpperBoundary <= @Cutoff AND NOT (PartitionNumber = 1 AND PartitionRows = 0)
ORDER BY PartitionNumber;
IF @P IS NULL BREAK;
SET @T0 = SYSDATETIME();
BEGIN TRY
BEGIN TRAN;
IF @Rows > 0
BEGIN
SET @SQL = N'SELECT @n = COUNT_BIG(*) FROM ' + QUOTENAME(@Archive) + N';';
EXEC sp_executesql @SQL, N'@n BIGINT OUTPUT', @n = @ArchRows OUTPUT;
IF @ArchRows <> 0 THROW 50002, 'Archive table is not empty.', 1;
SET @SQL = N'ALTER TABLE ' + QUOTENAME(@TableName) + N' SWITCH PARTITION '
+ CAST(@P AS NVARCHAR(10)) + N' TO ' + QUOTENAME(@Archive) + N';';
EXEC sp_executesql @SQL;
SET @SQL = N'SELECT @n = COUNT_BIG(*) FROM ' + QUOTENAME(@Archive) + N';';
EXEC sp_executesql @SQL, N'@n BIGINT OUTPUT', @n = @ArchRows OUTPUT;
IF @ArchRows <> @Rows THROW 50003, 'Row count mismatch after SWITCH.', 1;
IF EXISTS (SELECT 1 FROM dbo.ufn_GetPartitionBoundaryMap(@TableName)
WHERE UpperBoundary = @Upper AND PartitionRows <> 0)
THROW 50004, 'Source partition not empty after SWITCH.', 1;
SET @SQL = N'TRUNCATE TABLE ' + QUOTENAME(@Archive) + N';';
EXEC sp_executesql @SQL;
END;
-- Merge the LOWER boundary: two empty partitions, nothing moves.
IF @Lower IS NOT NULL
BEGIN
SET @SQL = N'ALTER PARTITION FUNCTION ' + QUOTENAME(@Function) + N'() MERGE RANGE (@b);';
EXEC sp_executesql @SQL, N'@b DATETIME2(3)', @b = @Lower;
END;
INSERT dbo.PartitionMaintenanceLog
(RunID, TableName, StepName, PartitionNumber, BoundaryValue, RowsProcessed, DurationMS, Status)
VALUES (@RunID, @TableName, 'Archive', @P, @Upper, @Rows,
DATEDIFF(MILLISECOND, @T0, SYSDATETIME()), 'Success');
COMMIT;
PRINT 'Archived up to ' + CONVERT(VARCHAR(23), @Upper, 121)
+ ' (' + CAST(@Rows AS VARCHAR(20)) + ' rows)';
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
INSERT dbo.PartitionMaintenanceLog
(RunID, TableName, StepName, PartitionNumber, BoundaryValue, RowsProcessed,
DurationMS, Status, ErrorNumber, ErrorMessage)
VALUES (@RunID, @TableName, 'Archive', @P, @Upper, 0,
DATEDIFF(MILLISECOND, @T0, SYSDATETIME()), 'Failed', ERROR_NUMBER(), ERROR_MESSAGE());
THROW;
END CATCH;
END;
END;6. usp_PartitionMaintenance (the orchestrator)
CREATE OR ALTER PROCEDURE dbo.usp_PartitionMaintenance
@TableName SYSNAME,
@Execute BIT = 0
AS
BEGIN
SET NOCOUNT ON; SET XACT_ABORT ON;
PRINT 'Partition maintenance: ' + @TableName + ' Execute=' + CAST(@Execute AS CHAR(1))
+ ' Start=' + CONVERT(VARCHAR(23), SYSUTCDATETIME(), 121);
EXEC dbo.usp_ValidatePartitionEnvironment @TableName = @TableName; -- 1
EXEC dbo.usp_ShowPartitionStatus @TableName = @TableName; -- 2
IF EXISTS (SELECT 1 FROM dbo.PartitionMaintenanceConfig -- 3
WHERE TableName = @TableName AND IsEnabled = 0)
BEGIN PRINT 'Disabled for this table.'; RETURN; END;
EXEC dbo.usp_CreateFuturePartitions @TableName = @TableName; -- 4
EXEC dbo.usp_ArchiveOldPartitions @TableName = @TableName,
@Execute = @Execute; -- 5
EXEC dbo.usp_ShowPartitionStatus @TableName = @TableName; -- 6
PRINT 'Completed: ' + CONVERT(VARCHAR(23), SYSUTCDATETIME(), 121);
END;7. Per-table deployment, steps 00 to 07
-- 00. Drop an old, non-partitioned table, only if it is empty
DECLARE @r BIGINT = (SELECT SUM(row_count) FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(N'dbo.EventOutbox') AND index_id IN (0, 1));
IF @r IS NULL PRINT 'Table does not exist.';
ELSE IF @r > 0 THROW 50001, 'Abort: the table has rows.', 1;
ELSE DROP TABLE dbo.EventOutbox;
GO
-- 01. Partition function with one seed boundary: today 00:00 UTC
DECLARE @seed DATETIME2(3) = CAST(CAST(SYSUTCDATETIME() AS DATE) AS DATETIME2(3));
EXEC sp_executesql
N'CREATE PARTITION FUNCTION PF_EventOutbox_Daily (DATETIME2(3)) AS RANGE RIGHT FOR VALUES (@b);',
N'@b DATETIME2(3)', @b = @seed;
GO
-- 02. Partition scheme
CREATE PARTITION SCHEME PS_EventOutbox_Daily AS PARTITION PF_EventOutbox_Daily ALL TO ([PRIMARY]);
GO
-- 03. Partitioned table: the partition column is part of the clustered key
CREATE TABLE dbo.EventOutbox
(
EventId NVARCHAR(255) NOT NULL,
Payload NVARCHAR(MAX) NULL,
CreatedAt DATETIME2(3) NOT NULL CONSTRAINT DF_EventOutbox_CreatedAt DEFAULT (SYSUTCDATETIME()),
CONSTRAINT PK_EventOutbox PRIMARY KEY CLUSTERED (EventId, CreatedAt)
) ON PS_EventOutbox_Daily (CreatedAt);
GO
-- 04. Archive table: same structure and filegroup, not partitioned
CREATE TABLE dbo.EventOutbox_Archive
(
EventId NVARCHAR(255) NOT NULL,
Payload NVARCHAR(MAX) NULL,
CreatedAt DATETIME2(3) NOT NULL,
CONSTRAINT PK_EventOutbox_Archive PRIMARY KEY CLUSTERED (EventId, CreatedAt)
) ON [PRIMARY];
GO
-- 05. Register the table
INSERT dbo.PartitionMaintenanceConfig
(TableName, PartitionColumn, ArchiveTableName, RetentionDays, FutureBufferDays, IsEnabled)
VALUES (N'EventOutbox', N'CreatedAt', N'EventOutbox_Archive', 1, 15, 1);
GO
-- 06. Expand the seed into the full future buffer
EXEC dbo.usp_CreateFuturePartitions @TableName = N'EventOutbox';
-- 07. Verify
EXEC dbo.usp_ShowPartitionStatus @TableName = N'EventOutbox';8. PartitionMaintenance.ps1 (no password in the file)
$ErrorActionPreference = "Stop"
$Server = "your-server.database.windows.net,1433"
$Database = "YourDatabase"
$TableName = "EventOutbox"
$Execute = 0 # 0 = dry run, 1 = execute
$Base = "C:\PartitionMaintenance"
$LogDir = Join-Path $Base "Logs"
$CredXml = Join-Path $Base "sql.cred.xml"
# One-time setup, as the Windows account that runs the task:
# Get-Credential | Export-Clixml "C:\PartitionMaintenance\sql.cred.xml"
New-Item -ItemType Directory -Path $LogDir -Force | Out-Null
Get-ChildItem $LogDir -Filter *.log |
Where-Object { $_.CreationTime -lt (Get-Date).AddDays(-30) } | Remove-Item -Force
$Log = Join-Path $LogDir ("PartitionMaintenance_{0:yyyyMMdd_HHmmss}.log" -f (Get-Date))
"Start: $(Get-Date) Server: $Server Database: $Database Table: $TableName Execute: $Execute" |
Set-Content $Log
$Cred = Import-Clixml $CredXml
$env:SQLCMDPASSWORD = $Cred.GetNetworkCredential().Password # not on the command line
$ExitCode = 1
try {
$Sql = "EXEC dbo.usp_PartitionMaintenance @TableName = N'$TableName', @Execute = $Execute;"
$Out = & sqlcmd -S $Server -d $Database -U $Cred.UserName -Q $Sql -b 2>&1
$Code = $LASTEXITCODE
$Out | Add-Content $Log
if ($Code -ne 0) { throw "sqlcmd exited with code $Code" }
"STATUS: SUCCESS End: $(Get-Date)" | Add-Content $Log
$ExitCode = 0
}
catch {
"STATUS: FAILED $($_.Exception.Message) End: $(Get-Date)" | Add-Content $Log
}
finally {
Remove-Item Env:SQLCMDPASSWORD -ErrorAction SilentlyContinue
}
exit $ExitCode # 0 = success, 1 = failure, for Task Scheduler9. Test script (the one used for the "Tested" section)
/* =====================================================================
Test for the partition maintenance framework (blog version).
Run in a TEST database only (Azure SQL dev DB, SQL Server 2016 SP1+,
Developer or Express edition), AFTER framework_objects.sql.
What it does:
- builds dbo.EventOutboxTest with 7 days of history (D-6 .. today)
- retention 1 day, future buffer 3 days
- runs the job as a dry run, then for real, then again
- checks every expected result and stops with an error if one fails
At the end it drops everything it created (the framework objects stay).
===================================================================== */
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#fail') IS NOT NULL DROP TABLE #fail;
CREATE TABLE #fail (msg NVARCHAR(200));
-- ---------- 0. Clean up any previous test run ----------
IF OBJECT_ID('dbo.EventOutboxTest') IS NOT NULL DROP TABLE dbo.EventOutboxTest;
IF OBJECT_ID('dbo.EventOutboxTest_Archive') IS NOT NULL DROP TABLE dbo.EventOutboxTest_Archive;
IF EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = 'PS_EventOutboxTest') DROP PARTITION SCHEME PS_EventOutboxTest;
IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = 'PF_EventOutboxTest') DROP PARTITION FUNCTION PF_EventOutboxTest;
DELETE dbo.PartitionMaintenanceConfig WHERE TableName = N'EventOutboxTest';
DELETE dbo.PartitionMaintenanceLog WHERE TableName = N'EventOutboxTest';
GO
-- ---------- 1. Partition function with daily midnight boundaries D-6 .. D+1 ----------
DECLARE @D DATE = CAST(SYSUTCDATETIME() AS DATE);
DECLARE @b DATETIME2(3) = CAST(DATEADD(DAY, -6, @D) AS DATETIME2(3));
EXEC sp_executesql N'CREATE PARTITION FUNCTION PF_EventOutboxTest (DATETIME2(3)) AS RANGE RIGHT FOR VALUES (@b);',
N'@b DATETIME2(3)', @b = @b;
EXEC (N'CREATE PARTITION SCHEME PS_EventOutboxTest AS PARTITION PF_EventOutboxTest ALL TO ([PRIMARY]);');
DECLARE @i INT = -5;
WHILE @i <= 1
BEGIN
SET @b = CAST(DATEADD(DAY, @i, @D) AS DATETIME2(3));
EXEC (N'ALTER PARTITION SCHEME PS_EventOutboxTest NEXT USED [PRIMARY];');
EXEC sp_executesql N'ALTER PARTITION FUNCTION PF_EventOutboxTest() SPLIT RANGE (@b);', N'@b DATETIME2(3)', @b = @b;
SET @i += 1;
END;
GO
-- ---------- 2. Tables and config ----------
CREATE TABLE dbo.EventOutboxTest
(
EventId NVARCHAR(255) NOT NULL,
Payload NVARCHAR(MAX) NULL,
CreatedAt DATETIME2(3) NOT NULL DEFAULT (SYSUTCDATETIME()),
CONSTRAINT PK_EventOutboxTest PRIMARY KEY CLUSTERED (EventId, CreatedAt)
) ON PS_EventOutboxTest (CreatedAt);
CREATE TABLE dbo.EventOutboxTest_Archive
(
EventId NVARCHAR(255) NOT NULL,
Payload NVARCHAR(MAX) NULL,
CreatedAt DATETIME2(3) NOT NULL,
CONSTRAINT PK_EventOutboxTest_Archive PRIMARY KEY CLUSTERED (EventId, CreatedAt)
) ON [PRIMARY];
INSERT dbo.PartitionMaintenanceConfig
(TableName, PartitionColumn, ArchiveTableName, RetentionDays, FutureBufferDays, IsEnabled)
VALUES (N'EventOutboxTest', N'CreatedAt', N'EventOutboxTest_Archive', 1, 3, 1);
GO
-- ---------- 3. Data: 100 rows per day for D-6 .. D, plus one edge row at 23:59:59.999 on D-3 ----------
DECLARE @D DATE = CAST(SYSUTCDATETIME() AS DATE);
;WITH n AS (SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS k FROM sys.all_objects),
d AS (SELECT v AS dayoffset FROM (VALUES (-6),(-5),(-4),(-3),(-2),(-1),(0)) x(v))
INSERT dbo.EventOutboxTest (EventId, Payload, CreatedAt)
SELECT CONCAT('E', d.dayoffset, '-', n.k), N'payload',
DATEADD(HOUR, 12, CAST(DATEADD(DAY, d.dayoffset, @D) AS DATETIME2(3)))
FROM n CROSS JOIN d;
INSERT dbo.EventOutboxTest (EventId, Payload, CreatedAt)
VALUES ('EDGE-1', N'last millisecond of D-3', DATEADD(MILLISECOND, -1, CAST(DATEADD(DAY, -2, @D) AS DATETIME2(3))));
IF (SELECT COUNT(*) FROM dbo.EventOutboxTest) <> 701 BEGIN INSERT #fail VALUES ('FAIL: setup row count'); RAISERROR('FAIL: setup row count', 16, 1); END;
-- the edge row must be in the D-3 partition, not D-2 (the point of midnight boundaries)
IF NOT EXISTS (SELECT 1 FROM dbo.ufn_GetPartitionBoundaryMap(N'EventOutboxTest')
WHERE LowerBoundary = CAST(DATEADD(DAY, -3, @D) AS DATETIME2(3)) AND PartitionRows = 101)
BEGIN INSERT #fail VALUES ('FAIL: edge row at 23:59:59.999 is not in its own day'); RAISERROR('FAIL: edge row at 23:59:59.999 is not in its own day', 16, 1); END;
IF NOT EXISTS (SELECT 1 FROM #fail) PRINT 'PASS 1: setup, 701 rows, edge row in the right day';
GO
-- ---------- 4. Dry run: nothing may be archived ----------
EXEC dbo.usp_PartitionMaintenance @TableName = N'EventOutboxTest', @Execute = 0;
IF (SELECT COUNT(*) FROM dbo.EventOutboxTest) <> 701 BEGIN INSERT #fail VALUES ('FAIL: dry run changed data'); RAISERROR('FAIL: dry run changed data', 16, 1); END;
IF EXISTS (SELECT 1 FROM dbo.PartitionMaintenanceLog WHERE TableName = N'EventOutboxTest')
BEGIN INSERT #fail VALUES ('FAIL: dry run wrote archive log rows'); RAISERROR('FAIL: dry run wrote archive log rows', 16, 1); END;
IF NOT EXISTS (SELECT 1 FROM #fail) PRINT 'PASS 2: dry run changed nothing';
GO
-- ---------- 5. Real run ----------
EXEC dbo.usp_PartitionMaintenance @TableName = N'EventOutboxTest', @Execute = 1;
DECLARE @D DATE = CAST(SYSUTCDATETIME() AS DATE);
-- D-6 .. D-2 archived (500 + edge row), D-1 and D kept (200 rows)
IF (SELECT COUNT(*) FROM dbo.EventOutboxTest) <> 200 BEGIN INSERT #fail VALUES ('FAIL: expected 200 rows left (D-1 and D)'); RAISERROR('FAIL: expected 200 rows left (D-1 and D)', 16, 1); END;
IF (SELECT COUNT(*) FROM dbo.EventOutboxTest_Archive) <> 0 BEGIN INSERT #fail VALUES ('FAIL: archive table not truncated'); RAISERROR('FAIL: archive table not truncated', 16, 1); END;
IF (SELECT MIN(CreatedAt) FROM dbo.EventOutboxTest) < CAST(DATEADD(DAY, -1, @D) AS DATETIME2(3))
BEGIN INSERT #fail VALUES ('FAIL: rows older than the cutoff are still there'); RAISERROR('FAIL: rows older than the cutoff are still there', 16, 1); END;
-- old boundaries merged away: the first boundary is now the cutoff (D-1 00:00)
IF (SELECT MIN(UpperBoundary) FROM dbo.ufn_GetPartitionBoundaryMap(N'EventOutboxTest'))
<> CAST(DATEADD(DAY, -1, @D) AS DATETIME2(3))
BEGIN INSERT #fail VALUES ('FAIL: expired boundaries were not merged'); RAISERROR('FAIL: expired boundaries were not merged', 16, 1); END;
-- leftmost partition empty, open-ended partition empty
IF EXISTS (SELECT 1 FROM dbo.ufn_GetPartitionBoundaryMap(N'EventOutboxTest')
WHERE (PartitionNumber = 1 OR UpperBoundary IS NULL) AND PartitionRows <> 0)
BEGIN INSERT #fail VALUES ('FAIL: leftmost or open-ended partition is not empty'); RAISERROR('FAIL: leftmost or open-ended partition is not empty', 16, 1); END;
-- future buffer: boundaries up to D+4 (buffer 3 + 1)
IF NOT EXISTS (SELECT 1 FROM dbo.ufn_GetPartitionBoundaryMap(N'EventOutboxTest')
WHERE UpperBoundary = CAST(DATEADD(DAY, 4, @D) AS DATETIME2(3)))
BEGIN INSERT #fail VALUES ('FAIL: future partitions were not created'); RAISERROR('FAIL: future partitions were not created', 16, 1); END;
-- every boundary is exactly midnight
IF EXISTS (SELECT 1 FROM dbo.ufn_GetPartitionBoundaryMap(N'EventOutboxTest')
WHERE UpperBoundary IS NOT NULL AND UpperBoundary <> CAST(CAST(UpperBoundary AS DATE) AS DATETIME2(3)))
BEGIN INSERT #fail VALUES ('FAIL: a boundary is not at midnight'); RAISERROR('FAIL: a boundary is not at midnight', 16, 1); END;
-- one success log row per archived day
IF (SELECT COUNT(*) FROM dbo.PartitionMaintenanceLog
WHERE TableName = N'EventOutboxTest' AND Status = 'Success') <> 5
BEGIN INSERT #fail VALUES ('FAIL: expected 5 success log rows'); RAISERROR('FAIL: expected 5 success log rows', 16, 1); END;
IF (SELECT SUM(RowsProcessed) FROM dbo.PartitionMaintenanceLog WHERE TableName = N'EventOutboxTest') <> 501
BEGIN INSERT #fail VALUES ('FAIL: expected 501 archived rows in the log'); RAISERROR('FAIL: expected 501 archived rows in the log', 16, 1); END;
IF NOT EXISTS (SELECT 1 FROM #fail) PRINT 'PASS 3: 5 days archived (501 rows), boundaries merged, buffer created, all midnight';
GO
-- ---------- 6. Run again: must be a no-op ----------
EXEC dbo.usp_PartitionMaintenance @TableName = N'EventOutboxTest', @Execute = 1;
IF (SELECT COUNT(*) FROM dbo.EventOutboxTest) <> 200 BEGIN INSERT #fail VALUES ('FAIL: second run changed data'); RAISERROR('FAIL: second run changed data', 16, 1); END;
IF (SELECT COUNT(*) FROM dbo.PartitionMaintenanceLog WHERE TableName = N'EventOutboxTest') <> 5
BEGIN INSERT #fail VALUES ('FAIL: second run archived again'); RAISERROR('FAIL: second run archived again', 16, 1); END;
IF NOT EXISTS (SELECT 1 FROM #fail) PRINT 'PASS 4: second run is a no-op (idempotent)';
GO
-- ---------- 7. Final status, then clean up the test objects ----------
EXEC dbo.usp_ShowPartitionStatus @TableName = N'EventOutboxTest';
SELECT RunDateUTC, StepName, PartitionNumber, BoundaryValue, RowsProcessed, DurationMS, Status
FROM dbo.PartitionMaintenanceLog WHERE TableName = N'EventOutboxTest' ORDER BY LogID;
DROP TABLE dbo.EventOutboxTest;
DROP TABLE dbo.EventOutboxTest_Archive;
DROP PARTITION SCHEME PS_EventOutboxTest;
DROP PARTITION FUNCTION PF_EventOutboxTest;
DELETE dbo.PartitionMaintenanceConfig WHERE TableName = N'EventOutboxTest';
IF EXISTS (SELECT 1 FROM #fail)
BEGIN
SELECT msg AS failed_check FROM #fail;
PRINT '*** TESTS FAILED: see failed_check above. Test objects removed. ***';
END
ELSE PRINT 'ALL TESTS PASSED. Test objects removed.';
GOSummary
A daily partitioned table only stays healthy if something maintains it every day. This framework does that with one config row per table and one scheduled call: it keeps empty partitions ready ahead of today, switches expired days out without copying a row, merges only empty partitions, and logs every step. The bug that broke it was three milliseconds of rounding, and the lasting fix was a better convention: midnight boundaries, carried at the column's exact type and passed as typed parameters.
Framework designed and tested by me. Names, values and scripts are generic; test in a non-production database before use.
Tamilselvan Perumal is an Oracle ACE Associate and Senior DBA with 12+ years in banking databases, working on database performance and benchmarking. Connect on LinkedIn.



