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;

WHILE 1 = 1
BEGIN
    DELETE TOP (10000)
    FROM dbo.log_EventLog
    WHERE DateTimeLogged < '01/01/2025';

    IF @@ROWCOUNT = 0
        BREAK;

    -- Optional: pause slightly to reduce system pressure
    WAITFOR DELAY '00:00:01';
END

SELECT 'All Done'