Request a topic or
contact an Arke consultant
404-812-3123
DNN Event Viewer times out

Arke Systems Blog

Useful technical and business information straight from Arke.

About the author

Author Name is someone.
E-mail me Send mail

Recent comments

Archive

Authors

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2018

DNN Event Viewer times out

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



Posted by David Eison on Monday, February 28, 2011 2:26 PM
Permalink | Comments (0) | Post RSSRSS comment feed