Continuing in our theme of adjusting DNN sprocs…

The DNN Event Viewer first runs a purge sproc, then runs a get log sproc.

If you have a lot of events, the purge sproc is almost certain to time out. 

The get log sproc can benefit from a nolock.  But the purge sproc is where we saw most of our trouble.

The purge sproc in dnn 551:

    ;WITH logcounts AS

    (  

      SELECT 

        LogEventID, 

        LogConfigID, 

        ROW_NUMBER() OVER(PARTITION BY LogConfigID ORDER BY LogCreateDate DESC) AS logEventSequence

      FROM dbo.EventLog with(NOLOCK)

    )

    DELETE dbo.EventLog 

    FROM dbo.EventLog el 

        JOIN logcounts lc ON el.LogEventID = lc.LogEventID

        INNER JOIN dbo.EventLogConfig elc ON elc.ID = lc.LogConfigID

    WHERE elc.KeepMostRecent <> -1

        AND lc.logEventSequence > elc.KeepMostRecent 

This was failing for a few clients of ours.  They would end up with say 65k records in the event log table, and this would never complete. 

This should lock less rows, delete in 1000 record chunks, and put an upper bound on how many records it will tackle at once.  This helped this lookup quit failling for our client:

ALTER PROCEDURE [dbo].[PurgeEventLog]
AS
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW

create table #TLog (LogGUID uniqueidentifier not null primary key, LogCreateDate datetime)

;WITH logcounts AS
(  
  SELECT 
    LogEventID, 
    LogConfigID, 
    ROW_NUMBER() OVER(PARTITION BY LogConfigID ORDER BY LogCreateDate DESC) AS logEventSequence
  FROM dbo.EventLog with(NOLOCK)
)
insert into #TLog
 SELECT LogGUID, LogCreateDate
    FROM dbo.EventLog el with(NOLOCK)
        JOIN logcounts lc with(NOLOCK) ON el.LogEventID = lc.LogEventID
        INNER JOIN dbo.EventLogConfig elc with(NOLOCK) ON elc.ID = lc.LogConfigID
    WHERE elc.KeepMostRecent <> -1
        AND lc.logEventSequence > elc.KeepMostRecent 

declare @intRowCount int
declare @intErrNo int
declare @commiteveryn int
declare @maxloops int

set @commiteveryn=1000
set @intErrNo=0
set @intRowCount=1 -- force first loop
set @maxloops=20

WHILE @intRowCount > 0 and @maxloops > 0
    BEGIN
        set @maxloops = @maxloops - 1
        BEGIN TRANSACTION
        BEGIN TRY
        DELETE FROM EventLog WHERE LogGuid IN (select top (@commiteveryn) LogGUID from #TLog order by LogCreateDate DESC)
        SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT        
        DELETE FROM #TLog WHERE LogGuid IN (select top (@commiteveryn) LogGUID from #TLog order by LogCreateDate DESC)

        commit
        END TRY
        BEGIN CATCH
         rollback;
         set @maxloops=0
        END CATCH
    END

drop table #TLog

-- used to be
    --;WITH logcounts AS
    --(  
    --  SELECT 
    --    LogEventID, 
    --    LogConfigID, 
    --    ROW_NUMBER() OVER(PARTITION BY LogConfigID ORDER BY LogCreateDate DESC) AS logEventSequence
    --  FROM dbo.EventLog with(NOLOCK)
    --)
    --DELETE dbo.EventLog 
    --FROM dbo.EventLog el 
    --    JOIN logcounts lc ON el.LogEventID = lc.LogEventID
    --    INNER JOIN dbo.EventLogConfig elc ON elc.ID = lc.LogConfigID
    --WHERE elc.KeepMostRecent <> -1
    --    AND lc.logEventSequence > elc.KeepMostRecent 




GO