Purging Many Records

If you come across the need to purge many records from a datatable, and doing so will result in a large rollback snapshot, you can split the deletion into chunks that will execute much faster and with less resources.

Use something like this:

SET NOCOUNT ON;

-- Loop until done...
WHILE 1 = 1
BEGIN
	-- Remove the first 10k records we find...
    DELETE TOP (10000)
    FROM L5_AssetCentre.dbo.log_EventLog
    WHERE DateTimeLogged < '07/01/01/2025'2024';

	-- End if none were found to delete...
    IF @@ROWCOUNT = 0
        BREAK;

	-- Optional:Pause pausebetween slightlydeletions, toso reduceany systemcontext pressureswitching can occur...
    WAITFOR DELAY '00:00:01';
END

SELECT 'All Done'