Blog

Clear Event Log and Schedule History in DotNetNuke 7+

By Host Account on 11/10/2016

If you run DNN site for a while without clearing the Event Log, the log will grow to the point that you will get timeout error when go to Admin > Event Viewer menu.   See Figure 1 for a screenshot of the Event Viewer where you can find the Clear Log button at the bottom (click on image to zoom in).   How would you clear the log if you can not get to the screen?

image

Figure 1 – Screenshot of Event Viewer where the Clear Log button is found

The work around is to run SQL script to clear the logging data.   Notice that the script will Truncate tables (same as Delete without database log).   Did it work?  Yes.   After running the script, the Event Viewer can be opened.  Thank you to Sebastian Leupold, creator of dnnWerk

--EventLog table

/* Truncate EventLog - Version 0.9.8 (2015-07-25)

   ================================================================================

   (c) Sebastian Leupold, dnnWerk/gamma concept mbH 2014-2015

   Run this Script to remove all entries of EventLog in DNN Platform V.5.0.0 - 7.4.0

   == Please make sure to use latest version from http://dnnscript.codeplex.com ===

   Instructions:

   =============

   - Install by running as script from SQL item in Host menu inside DNN or run in

     SQL Server Management Studio

*/

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = object_id(N'dbo.sys_currentDNNVersion') AND Type = N'FN')

DROP FUNCTION dbo.sys_currentDNNVersion

GO

-- --------- create tooling: ---------

CREATE FUNCTION dbo.sys_currentDNNVersion()

RETURNS Int

AS

BEGIN

DECLARE @Vers Int;

SELECT Top(1) @Vers = Major * 10000 + Minor * 100 + Build FROM dbo.Version ORDER BY CreatedDate DESC;

RETURN @Vers;

END

GO

IF dbo.sys_currentDNNVersion() >= 70400 BEGIN

-- Drop Foreign Key Constraints:

DECLARE @fkName nVarChar(100) = Null;

SELECT @fkName = name FROM sys.foreign_keys

WHERE parent_object_id = OBJECT_ID(N'dbo.ExceptionEvents')

AND Object_id IN (SELECT constraint_object_id

FROM sys.foreign_key_columns F

JOIN sys.columns C ON F.parent_object_id = C.object_id AND F.parent_column_id = C.column_ID

WHERE C.Name = N'LogEventID');

IF Not @fkName Is Null

Exec(N'ALTER TABLE dbo.ExceptionEvents DROP CONSTRAINT ' + @fkName +';');

SET @fkName = Null;

SELECT @fkName = name FROM sys.foreign_keys

WHERE parent_object_id = OBJECT_ID(N'dbo.EventLog')

AND Object_id IN (SELECT constraint_object_id

FROM sys.foreign_key_columns F

JOIN sys.columns C ON F.parent_object_id = C.object_id AND F.parent_column_id = C.column_ID

WHERE C.Name = N'ExceptionHash');

IF Not @fkName Is Null

Exec(N'ALTER TABLE dbo.EventLog DROP CONSTRAINT ' + @fkName +'')

END

GO

-- Truncate tables:

IF dbo.sys_currentDNNVersion() >= 70400 BEGIN

TRUNCATE TABLE dbo.Exceptions

TRUNCATE TABLE dbo.ExceptionEvents

TRUNCATE TABLE dbo.EventLog

END ELSE

TRUNCATE TABLE dbo.EventLog

GO

IF dbo.sys_currentDNNVersion() >= 70400 BEGIN

-- Recreate Foreign Key Constraints (using common naming):

ALTER TABLE dbo.ExceptionEvents

WITH CHECK ADD CONSTRAINT FK_objectQualifierExceptionEvents_EventLog

FOREIGN KEY(LogEventID)

REFERENCES dbo.EventLog (LogEventID)

ON DELETE CASCADE;

ALTER TABLE dbo.EventLog

WITH CHECK ADD CONSTRAINT FK_objectQualifierEventLog_Exceptions

FOREIGN KEY(ExceptionHash)

REFERENCES dbo.Exceptions (ExceptionHash)

ON DELETE NO ACTION;

END

GO

DROP FUNCTION dbo.sys_currentDNNVersion

GO

--Schedule Log table

TRUNCATE TABLE ScheduleHistory