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.


DNN Tip: Monitoring the Exceptions and EventLog tables

image

At some point, if you manage a DNN based website long enough, you are going to run into a problem where your database seems to have grown WAY bigger than it should be. I’m not talking a couple hundred megs, I’m talking gigabytes, lots of gigabytes. This post will hopefully help you get that under control.

Tonight, I had a DNN instance that I was looking to migrate to Azure, in preparation for this I remoted into the web and database servers to see what the files and database looked like. The database MDF file was almost 1.5gb in size, for a DNN instance that has 5 portals, and very very little activity, this was concerning. I knew where to start looking though, there are two tables to start with, Exceptions and EventLog. The EventLog is tied to the “Admin Logs” in DNN 9, in earlier versions of DNN the menu item was Event Logs or even Event Viewer.

Running the following simple SQL query will get you an idea of how many rows are in those tables.

select count(1) from Exceptions with (nolock)

select count(1) from EventLog with (nolock)

If you’re running this in the SQL Console on your DNN site you might run it with

select count(1) from {databaseOwner}{objectQualifier}Exceptions with (nolock)

select count(1) from {databaseOwner}{objectQualifier}EventLog with (nolock)

This will give you a “count” of the number of rows in each of those tables. In my case, the EventLog table only had 394 records, but the Exceptions table had 300k+. That’s a pretty common cause of database size creep in DNN. Ultimately what my issue was, I had one site that still used “SolPartMenu” in the Containers for the skin, and with DNN9, those no longer work, and every request that tried to load that site (mostly search engines hitting it) caused multiple records to be thrown into the EventLog and Exceptions table.

Message:Object reference not set to an instance of an object.
StackTrace:
at DotNetNuke.UI.Skins.Pane.LoadModuleContainer(ModuleInfo module) at DotNetNuke.UI.Skins.Pane.InjectModule(ModuleInfo module)

Now, the question is how did I know what the cause of that cryptic error message was? Well, I had just spent some time with a customer of mine this weekend upgrading their website, and they ran into the same problem, so it was fresh in my head, and I was easily able to correct this.

But let’s get back to the topic of the post, the Exceptions and EventLog tables. DNN has a way to “purge” the eventlog built into the system, and most “types” of events only get stored 10 times, so when the 11th event comes in, the first one gets cleared from the system. But, the exception Events also get logged to the Exceptions table, and there doesn’t appear to be anything in DNN that cleans that table up automatically. Because I upgraded the site to DNN 9 well over a month ago, I had hundreds of thousands of exceptions in the table. So I needed to get this cleaned up, that’s easy with the following SQL statement.

truncate table Exceptions

or the DNN SQL Console version

truncate table {databaseOwner}{objectQualifier}Exceptions

That forces the table to be purged, and then using SQL Server Management Studio I was able to run a SHRINK command on the database, bringing the files back down to an expected 30 mb.

Now you might think, well can’t I do the same on the EventLog table? Unfortunately the simple answer to that is no. The longer answer is YES, but you’ll want to look at Sebastian’s SQL script for doing so: http://dnnscript.codeplex.com/releases/view/612643

 

Comments

Sebastian Leupold
Note: you should not use "WITH (NoLock)" if you want to retrieve real data.
Sebastian Leupold Thursday, March 16, 2017 6:15 PM (link)
Sebastian Leupold
PS: Kudos for the reference to my script.
I blogged about this subject some years ago and the content still valid:
http://www.dnnsoftware.com/community-blog/cid/155180/howto-truncate-your-eventlog-in-dnn-740

Note: My turbo scripts installs a mechanism, which removes obsolete Exceptions automatically, limiting the size of that table (depending on items in EventLog table)
Sebastian Leupold Thursday, March 16, 2017 6:28 PM (link)
Leith Tussing
Honestly, why hasn't something been designed into DNN to handle this yet? If there is after years of running DNN I'm not aware of it. There should be something on by default to auto prune these tables with options to do something like just delete old entries or to write old entries out to a log file on disk for historic use.
Leith Tussing Tuesday, May 2, 2017 1:46 PM (link)

Comment Form

Only registered users may post comments.

NewsArchives


Aderson Oliveira (15)
Alec Whittington (11)
Alex Shirley (10)
Andrew Nurse (30)
Anthony Glenwright (5)
Antonio Chagoury (28)
Ash Prasad (28)
Ben Schmidt (1)
Benjamin Hermann (25)
Benoit Sarton (9)
Beth Firebaugh (12)
Bill Walker (36)
Bob Kruger (5)
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 (29)
Cuong Dang (21)
Daniel Bartholomew (2)
Daniel Mettler (146)
Dave Buckner (2)
David Poindexter (3)
David Rodriguez (2)
Doug Howell (11)
Erik van Ballegoij (30)
Ernst Peter Tamminga (74)
Geoff Barlow (10)
George Alatrash (1)
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 (272)
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 (53)
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 (39)
Will Strohl (164)
William Severance (5)
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out