HowTo: Truncate your Eventlog in DNN 7.4.0

The DNN event log stores each exception and all other registered events in the database. There is a scheduler job you should have enabled to ensure, the table doesn't grow too large. However, if the job is turned off or there were many events between executions of the scheduler, the cleanup job and even a manual deletion command might time out. Or you already reached the size limit of your database, and delete operation fails as well. In this case, Truncate Table is your only option, as it purges all rows from a database table efficiently and without logging.

While it was quite easy to run this command before, in DNN 7.4.0 the Eventlog table was split into 3 tables (don't ask me, why) which are linked by Foreign Key Constraints to implement referential integrity - which prevents you from simply applying Truncate Table. I created a small script, which temporarily drops these constraints, truncates Eventlog and associated tables and re-adds the constraints afterwards. You may download it for free from https://dnnscript.codeplex.com/releases/view/612643 github.com/dnnwerk/truncateEventlog

Comments

Sebastian, the reason for the split up is buried somewhere here inside: http://www.dnnsoftware.com/community-blog/cid/155166/improved-event-logging-in-dnn-74

Torsten Weggen Thursday, March 19, 2015 7:09 AM (link)

The change is listed on the wiki 7.4.0 enhancements - http://www.dnnsoftware.com/wiki/page/dnn-7-4-developer-quick-start#Add_structure_to_the_event_log_so_it_is_easier_to_drill_down_onto_problems_4

cathal connolly Thursday, March 19, 2015 7:29 PM (link)

Thanks for your feedback, Torsten and Cathal,
and I do understand the motivation of Peter, and I really appreciate his passion on DNN
- but I still don't understand the benefit of this change:
- there is no improvement regarding database growth
- exception and inner exception are still an XML blob without proper data type, improved rendering of stack trace is just on UI level
- it is a nice idea of extracting stack trace to a separate table with multiple references - but in fact, I am getting 100 exception events and 100 exceptions with slightly different stack trace
- exceptions are never cleaned up
- although I am an enthusiastic fan of NF 1, 2, 3, 3.5, 4, 4.1 (whatever Americans are able to invent in the future), I might be too old to understand the benefit of this change, sorry - but I see the risk for existing installations and new level for our site owners, to deal with it

Sebastian Leupold Thursday, March 19, 2015 7:53 PM (link)

I apologize, I should have been more constructive.
There are great UI improvements and I'll provide a pull request to revert data structure, so we may look for a more intelligent option in the future (e.g. by replacing common variables by its abstracted names first).

Sebastian Leupold Thursday, March 19, 2015 8:51 PM (link)

I upgraded a site for a major customer from 7.1.1 to 7.4.0 last week and have noticed no new events in the Event Viewer. I checked the database and saw EventLog had no records, ExceptionEvents had no records, and Exceptions had over 81,000 records. In just under 24 hours, we have added over 16,000 new records to the Exceptions table, while no records have been added to the other tables.

From what I have read, it appears the same data should be stored for events, but is now spread across three tables. Do you know of any way for me to fix this so I can start getting data in the other tables so I can track down the massive number of events? I don't have TabId, UserId, LogCreateDate or any of a number of other details that would help me track down what's happening.

For me, the new table design is not an improvement.

Patrick Richards Wednesday, May 20, 2015 9:12 AM (link)

Patrick Richards Wednesday, May 20, 2015 10:16 AM (link)

I found the solution to my empty log tables. Two years ago, we were getting a flood of DMX errors we could not track down. To prevent them from filling the EventLog table, I added a Trigger to only insert entries that did not match the pattern of the DMX error. I also found an Index someone had added to the EventLog table that was not present in another DNN 7.4.0 installation we host. I removed the Trigger and Index and restarted the Application. I am now seeing new entries in the log.

Patrick Richards Wednesday, May 20, 2015 10:36 AM (link)

Patrick,
I am glad you found the issue of missing Eventlog entries - you now only need a cleanup plan to ensure, the tables don't exceed your database limits.

Sebastian Leupold Thursday, May 28, 2015 1:38 AM (link)

Hello Sebastian

I tried your script in dnn 742 but the sitelog table was not cleaned. I have more than a million rows so clean.

I run it at host-sql tab without any change to the code

any ideas?

Best
Jose

jose cardoso Sunday, July 10, 2016 8:26 AM (link)

Jose,

sorry for the late reply - I just got the notification.

This script is for clearing Eventlog and Exceptions tables.

To clear Sitelog table, run the following statement in Host > SQL:
TRUNCATE TABLE {databaseOwner}[{objectQualifier}EventLog]

Best,
Sebastian

Sebastian Leupold Monday, July 25, 2016 10:06 AM (link)

Hi I wanted a nice clean way to clear the event log from code and I found the proc:

PurgeEventLog

But when I run it I am left with 63 records in the EventLog table. Having looked at the proc I am not sure what it is doing.

Can anyone explain? Should I use this proc or use Sebastian's script?

Gus Beare Thursday, November 10, 2016 6:57 AM (link)

PurgeEventLog is not removing all items from EventLog and - more important - not touching entries in Exceptions table.
I suggest using my script instead.

Sebastian Leupold Monday, November 14, 2016 1:00 PM (link)

Sebastian, where is the script these days, please? There's a link on the page indicated which send you nowhere, or so..

Franco Prati Wednesday, February 7, 2018 4:00 PM (link)

Hi Franco,
unfortunately, I am unable to edit my post.
Please get the script from https://github.com/dnnwerk/truncateEventlog

Sebastian Leupold Wednesday, February 7, 2018 5:00 PM (link)

Thanks, it worked!

But how to avoid the exceptions table go out of control?

Franco Prati Wednesday, February 7, 2018 5:28 PM (link)

DeleteEventLog stored procedure in my Turbo Scripts removes orphaned rows (not referenced by any ExceptionEvent) to take care of the table size.

Sebastian Leupold Wednesday, February 7, 2018 5:46 PM (link)

Hi Sebastian, can we use your script TruncateEventlog2.sql on DNN 7.04.02 ?
Thank you for quick reply.
Jure

jure svetina Wednesday, May 22, 2019 1:21 AM (link)

Products

Solutions

Resources

Partners

Community

About

New Community Website

HowTo: Truncate your Eventlog in DNN 7.4.0

Comments

Comment Form

NewsArchives

ESW Operations, LLC