SQL Server 2012 – Archiving High Write Logging Data

I have a logging database with a few lookup tables and one massive event table that has 79 million rows. On busy days my code inserts over 3 million rows, so the event table would be enormous in production if not for the fact that every half hour I copy the latest data to an archive database on a separate server and then every night delete all rows more than one month old from production.

The problem is that these deletes are now taking over 10 hours to run. I don't think they cause any blocking as long as I put 100 ms delay between each delete statement (script below), but I feel like things could be improved. Should the logging db be in snapshot isolation mode? We typically write the row and then update the EndTime column after the function has completed. Any suggestions on how to improve the process and/or speed up production would be appreciated.


DECLARE @CutoffDate datetime = DATEADD(DAY, -30, GETDATE()), @Time datetime, @Stop bit = 0, @RowCount int

WHILE (@Stop = 0)
BEGIN
    SET @Time = GETDATE()

    DELETE TOP (100) af 
    FROM ApplicationEvent ae
    JOIN ApplicationFault af ON af.ApplicationEventID = ae.ApplicationEventID
    WHERE LogTime < @CutoffDate 

    DELETE TOP (100) ae 
    FROM ApplicationEvent ae
    LEFT JOIN ApplicationFault af ON af.ApplicationEventID = ae.ApplicationEventID 
    WHERE LogTime < @CutoffDate AND af.ApplicationFaultID IS NULL

    SELECT @RowCount = @@ROWCOUNT
    SELECT @Stop = CASE @RowCount WHEN 0 THEN 1 ELSE 0 END 

    --PRINT DATEDIFF(MS, GETDATE(), @Time)

    WAITFOR DELAY '00:00:00:100';   
END

Here's the schema.


CREATE TABLE [dbo].[ApplicationEvent](
[ApplicationEventID] [int] IDENTITY(1,1) NOT NULL,
[LogTime] [datetime] NOT NULL,
[TransactionID] [int] NULL,
[TransactionType] [int] NULL,
[EventID] [int] NOT NULL,
[EventLevelID] [smallint] NOT NULL,
[StartTime] [datetime] NOT NULL,
[EndTime] [datetime] NULL,
[IPAddress] nvarchar NULL,
[UserName] nvarchar NULL,
[ApplicationID] [smallint] NOT NULL,
[EventInformation] nvarchar NULL,
[HostName] nvarchar NULL,
[ApplicationSourceId] [smallint] NULL,
CONSTRAINT [PK_ApplicationEvent_1] PRIMARY KEY CLUSTERED
(
[ApplicationEventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]