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