- 0 minutes to read

Maintenance and Purging Failed LogEvents

💡Remove invalid LogEvents safely to avoid unbounded table growth and to keep your Pickup Service processing healthy.

How do I safely purge failed LogEvents?

The procedure below deletes rows where ValidationFailed = 1 based on a configurable age (@olderThanHours) and a maximum number of rows to remove per invocation (@maxRows). It performs deletes in batches to reduce locking and transaction size. Use @dryRun = 1 to list candidate rows without deleting them.

-- Purge failed LogEvents safely in batches
CREATE PROCEDURE dbo.PurgeFailedLogEvents
  @olderThanHours INT = 168,  -- default: 7 days
  @maxRows INT = 1000,        -- maximum rows to remove in total
  @batchSize INT = 200,       -- rows to delete per batch (smaller is safer)
  @dryRun BIT = 0             -- 1 = only list candidates, 0 = actually delete
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON;

  DECLARE @deletedTotal INT = 0;
  DECLARE @deleted INT;

  IF (@dryRun = 1)
  BEGIN
    SELECT TOP (@maxRows)
         Id, Created, ValidationFailedText
    FROM dbo.LogEvents
    WHERE ValidationFailed = 1
      AND Created <= DATEADD(hour, -@olderThanHours, SYSUTCDATETIME())
    ORDER BY Created ASC;

    RETURN 0; -- dry-run, no deletion
  END

  WHILE (@deletedTotal < @maxRows)
  BEGIN
    BEGIN TRANSACTION;

    DELETE TOP (@batchSize)
    FROM dbo.LogEvents
    WHERE ValidationFailed = 1
      AND Created <= DATEADD(hour, -@olderThanHours, SYSUTCDATETIME())
    ;

    SET @deleted = @@ROWCOUNT;
    IF (@deleted > 0)
    BEGIN
      SET @deletedTotal = @deletedTotal + @deleted;
      COMMIT TRANSACTION;
    END
    ELSE
    BEGIN
      ROLLBACK TRANSACTION;
      BREAK; -- nothing left to delete
    END

    -- stop if we've reached the requested maximum
    IF (@deletedTotal >= @maxRows)
      BREAK;
  END

  SELECT N'Deleted rows: ' + CAST(@deletedTotal AS NVARCHAR(12)) AS Result;
  RETURN @deletedTotal;
END;
GO

What is batch deletion and why should I use it?

Batch deletion divides a large delete operation into smaller transactions, providing several benefits:

Benefits of batch deletion

  • Reduced locking - Smaller transactions hold locks for shorter periods, minimizing impact on other queries
  • Manageable transaction log - Avoids filling the transaction log with one massive delete operation
  • Interruptible - Can stop between batches without rolling back all progress
  • Performance - More predictable and consistent performance compared to single large deletes
  • Resource-friendly - Reduces memory pressure and tempdb usage

How it works

The stored procedure:

  1. Deletes @batchSize rows in each transaction (default: 200 rows)
  2. Commits the transaction after each batch
  3. Repeats until @maxRows total are deleted or no more candidates exist
  4. Uses SET XACT_ABORT ON to automatically rollback on errors

How do I test before deleting?

Always use dry-run mode first to preview what will be deleted:

-- Dry run: preview candidates without deleting
EXEC dbo.PurgeFailedLogEvents 
  @olderThanHours = 168,  -- 7 days
  @maxRows = 1000,
  @batchSize = 200,
  @dryRun = 1;            -- DRY RUN MODE

This will SELECT (not DELETE) the candidate rows, showing you:

  • Id - Row identifiers
  • Created - When the LogEvent was created
  • ValidationFailedText - Why validation failed

Once you've reviewed the candidates, run with @dryRun = 0 to actually delete.

How do I schedule automatic purging?

Create a SQL Server Agent job to run the purge procedure on a schedule:

-- Example: Run weekly on Sunday at 2 AM during low traffic
USE msdb;
GO

EXEC dbo.sp_add_job
    @job_name = N'Purge Failed LogEvents Weekly';

EXEC dbo.sp_add_jobstep
    @job_name = N'Purge Failed LogEvents Weekly',
    @step_name = N'Execute Purge',
    @subsystem = N'TSQL',
    @command = N'EXEC dbo.PurgeFailedLogEvents @olderThanHours = 168, @maxRows = 10000, @batchSize = 500, @dryRun = 0',
    @database_name = N'YourDatabaseName';

EXEC dbo.sp_add_schedule
    @schedule_name = N'Weekly Sunday 2AM',
    @freq_type = 8,  -- Weekly
    @freq_interval = 1,  -- Sunday
    @active_start_time = 020000;  -- 2:00 AM

EXEC dbo.sp_attach_schedule
    @job_name = N'Purge Failed LogEvents Weekly',
    @schedule_name = N'Weekly Sunday 2AM';

EXEC dbo.sp_add_jobserver
    @job_name = N'Purge Failed LogEvents Weekly';

Option 2: Windows Scheduled Task

Run via sqlcmd from a Windows scheduled task:

sqlcmd -S YourServer -d YourDatabase -E -Q "EXEC dbo.PurgeFailedLogEvents @olderThanHours=168, @maxRows=10000, @dryRun=0"

Option 3: Manual execution

Run manually via SSMS or Azure Data Studio during maintenance windows.

What safety precautions should I take?

Tip

Safety recommendations:

  • Always run dry-run first - Use @dryRun = 1 to preview candidates before deletion
  • Schedule during low-traffic windows - Avoid peak hours to minimize lock contention
  • Monitor Pickup Service - Ensure the Pickup Service isn't actively processing before purging
  • Backup before bulk deletion - Take database backups or export failed LogEvents for forensics
  • Start with small batches - Use smaller @batchSize values (100-200) on first run
  • Verify index exists - Add index on (ValidationFailed, Created) for optimal performance
  • Review validation failures - Investigate root causes before blindly deleting
  • Test in non-production first - Validate the procedure in development environments
-- Create index to optimize purge queries
CREATE NONCLUSTERED INDEX [IX_LogEvents_ValidationFailed_Created]
ON [dbo].[LogEvents] ([ValidationFailed], [Created])
INCLUDE ([Id], [ValidationFailedText])
WITH (FILLFACTOR = 100, DATA_COMPRESSION = PAGE, ONLINE = ON);

How do I adjust purge parameters?

Edit the stored procedure call to match your environment:

Parameter Default Purpose Recommendations
@olderThanHours 168 (7 days) Only delete LogEvents older than this Keep 7-30 days for investigation
@maxRows 1000 Maximum total rows to delete per execution Increase for bulk cleanups (10000+)
@batchSize 200 Rows deleted per transaction Smaller = safer (100-500)
@dryRun 0 (false) Preview mode without deletion Always test with 1 first

Example configurations

Conservative cleanup (test environments):

EXEC dbo.PurgeFailedLogEvents 
  @olderThanHours = 24,   -- Only keep 1 day
  @maxRows = 500,
  @batchSize = 100,
  @dryRun = 0;

Aggressive cleanup (production backlog):

EXEC dbo.PurgeFailedLogEvents 
  @olderThanHours = 720,  -- Keep 30 days
  @maxRows = 50000,
  @batchSize = 500,
  @dryRun = 0;

What happens if the procedure fails?

The procedure uses SET XACT_ABORT ON, which means:

  • Any error automatically rolls back the current batch
  • Previously committed batches remain deleted
  • The procedure returns immediately with an error message
  • You can re-run the procedure to continue from where it stopped

Error handling example

If a batch fails midway through:

  1. Batch 1 (200 rows) - ✅ Committed
  2. Batch 2 (200 rows) - ✅ Committed
  3. Batch 3 (200 rows) - ❌ Error occurs, rolled back
  4. Procedure exits with error

Result: 400 rows deleted, 200 rows remain. Re-run to continue.