NServiceBus SQL Persistent outbox cleanup

outboxcleanup.sql

DECLARE @DispatchedBefore datetime = GETUTCDATE()-1 -- Removes entries older then 24 hours
DECLARE @BatchSize INT = 4000 -- Avoid batch sizes over 4.000 to prevent lock escalation

WHILE 1 = 1
BEGIN
	DELETE TOP (@BatchSize) FROM [dbo].[EndpointNameOutboxData] WITH (ROWLOCK)
	WHERE Dispatched = 'true' AND DispatchedAt < @DispatchedBefore;
	IF @@ROWCOUNT < @BatchSize -- Important that @@ROWCOUNT is read immediately after the DELETE
		BREAK
END