DNN Community Blog

The Community Blog is a personal opinion of community members and by no means the official standpoint of DNN Corp or DNN Platform. This is a place to express personal thoughts about DNNPlatform, the community and its ecosystem. Do you have useful information that you would like to share with the DNN Community in a featured article or blog? If so, please contact .

The use of the Community Blog is covered by our Community Blog Guidelines - please read before commenting or posting.


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

Comments

Torsten Weggen
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)
cathal connolly
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)
Sebastian Leupold
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)
Sebastian Leupold
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)
Patrick Richards
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
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 10:16 AM (link)
Patrick Richards
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)
Sebastian Leupold
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)
jose cardoso
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)
Sebastian Leupold
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)
Gus Beare
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)
Sebastian Leupold
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)

Comment Form

Only registered users may post comments.

NewsArchives


Aderson Oliveira (15)
Alec Whittington (11)
Alex Shirley (10)
Andrew Nurse (30)
Andy Tryba (1)
Anthony Glenwright (5)
Antonio Chagoury (28)
Ash Prasad (32)
Ben Schmidt (1)
Benjamin Hermann (25)
Benoit Sarton (9)
Beth Firebaugh (12)
Bill Walker (36)
Bob Kruger (5)
Bogdan Litescu (1)
Brian Dukes (2)
Brice Snow (1)
Bruce Chapman (20)
Bryan Andrews (1)
cathal connolly (55)
Charles Nurse (163)
Chris Hammond (209)
Chris Paterra (55)
Clinton Patterson (40)
Cuong Dang (21)
Daniel Bartholomew (2)
Daniel Mettler (154)
Dave Buckner (2)
David Poindexter (4)
David Rodriguez (3)
Doug Howell (11)
Erik van Ballegoij (30)
Ernst Peter Tamminga (74)
Geoff Barlow (10)
George Alatrash (6)
Gifford Watkins (3)
Gilles Le Pigocher (3)
Ian Robinson (7)
Israel Martinez (17)
Jan Blomquist (2)
Jan Jonas (3)
Jaspreet Bhatia (1)
Jenni Merrifield (6)
Joe Brinkman (274)
John Mitchell (1)
Jon Henning (14)
Jonathan Sheely (4)
Jordan Coopersmith (1)
Joseph Craig (2)
Kan Ma (1)
Keivan Beigi (3)
Ken Grierson (10)
Kevin Schreiner (6)
Leigh Pointer (31)
Lorraine Young (60)
Malik Khan (1)
Matthias Schlomann (15)
Mauricio Márquez (5)
Michael Doxsey (7)
Michael Tobisch (3)
Michael Washington (202)
Miguel Gatmaytan (3)
Mike Horton (19)
Mitchel Sellers (28)
Nathan Rover (3)
Navin V Nagiah (14)
Néstor Sánchez (31)
Nik Kalyani (14)
Peter Donker (54)
Philip Beadle (135)
Philipp Becker (4)
Richard Dumas (22)
Robert J Collins (5)
Roger Selwyn (8)
Ruben Lopez (1)
Ryan Martinez (1)
Salar Golestanian (4)
Sanjay Mehrotra (9)
Scott McCulloch (1)
Scott S (11)
Scott Wilkinson (3)
Scott Willhite (97)
Sebastian Leupold (80)
Shaun Walker (237)
Shawn Mehaffie (17)
Stefan Cullmann (12)
Stefan Kamphuis (12)
Steve Fabian (31)
Timo Breumelhof (24)
Tony Henrich (3)
Torsten Weggen (2)
Vicenç Masanas (27)
Vincent Nguyen (3)
Vitaly Kozadayev (6)
Will Morgenweck (40)
Will Strohl (165)
William Severance (5)

Content Layout

Subscribe to DNN Digest

DNN Digest is our monthly email newsletter. It highlights news and content from around the DNN ecosystem, such as new modules and themes, messages from leadership, blog posts and notable tweets. Keep your finger on the pulse of the ecosystem by subscribing.  


Copyright 2017 by DNN Corp Terms of Use Privacy
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out