For my fellow DNN Enthusiasts out there, who know enough to be dangerous, but don't know enough to be smart, I thought I would post what I learned today about working with your database from within DNN in a hosted environment.  

First of all, I happen to host my sites at 3Essentials, and I'm sure your hosting provider probably has something to do with whether or not all of these things will work for you as a solution.  I highly recommend 3Essentials, as I had tried several hosters before them, including GoDaddy and IHostAsp.Net, and found myself disappointed with service at both places.  Support at 3Essentials is unrivaled on all levels.

Second of all, this information is very basic, but I thought it might be helpful for others like me (basic!).  So, by all means, consider this free for coming, and realize that you get what you pay for.

Third of all, thanks to Mitchel Sellers who helped me through much of this.

NOTE* Every command below can be performed in your HOST > SQL page inside your portal 
(at least if you host at 3Essentials, but I assume this would be fairly universal)  I am NOT sure how this would work in a locally hosted environment, such as an Intranet portal.

Problem: Database out of Control

Why would I have a 630MB database on a 1 page website?  
The most likely cause for this type of BLOAT in a database is caused by the EventLog.  The problem can also be caused by a growing SiteLog, if you have that enabled.  SiteLOg is found under HOST > Host Settings > Advanced Settings > Other Settings > Site Log History (I set it to 0 days)

Where do I go in DNN to 1) assess the problem, and 2) fix the problem?

Solution(s): How to Assess / Potential Fixes

Assessing the problem and looking at potential fixes.  I had read about EventLog and SiteLog issues creating a bloated database.  But how do you figure out if they are actually the problem?  While this is not a comprehensive answer, it is a start.

• Login and go to HOST > SQL
  • Paste this into your SQL screen: sp_spaceused
  • Click Execute
  • This generates a small table showing you how much space is used by your database (note the results)
  • Note the database size, and the unallocated space (this is my database size AFTER all the steps I took)
  • Note the name of the database (below I have substituted database_dnn), as you will use it for the SHRINKDATABASE command below

• Is the problem with your database tables? (read this from Mitchel Sellers)
  • Look at that post from Mitchel on his DNN blog, and you'll be able to generate a report right inside HOST > SQL on your DNN portal.
  • If you have a "problem" table, you'll need to figure out how to reduce the size of that table, if necessary.

• Truncate your EventLog -- A more common problem
  • At HOST > SQL, enter this text: TRUNCATE TABLE eventlog
  • Do not need to check the "Run as Script" box
  • Click Execute (it may not appear that anything is happening, but this trims your EventLog file)
  • Run the sp_spaceused again and see if there is a difference between the first time you ran it and this time (If so, there was likely a problem with your EventLog) 

• Truncate your SiteLog -- A more common problem
  • At HOST > SQL enter this text: TRUNCATE TABLE sitelog
  • Do not need to check the "Run as Script" box
  • Click Execute (it may not appear that anything is happening, but this trims your SiteLog file)
  • Run the sp_spaceused again and see if there is a difference between the first time you ran it and this time (If so, there was likely a problem with your SiteLog)
• Shrink Your Database
  • disclaimer: i don't know the full consequences of shrinking your database, but it can be done through HOST > SQL
  • At HOST > SQL, enter this text: DBCC SHRINKDATABASE (MyDatabase);
  • Include the semicolon at the end of the previous line
  • Do not need to check the "Run as Script" box
  • Click Execute
  • Run the sp_spaceused again and see what the database_size is, compared to the unallocated space (both may decrease significantly)
  • Read more about this SQL command here

this was my sp_spaceused BEFORE i ran the TRUNCATE commands and the SHRINK command

this was my sp_spaceused AFTER i ran the TRUNCATE commands and the SHRINK command

AFTER TRUNCATE sitelog and TRUNCATE eventlog (note the HUGE difference in unallocated--free-space)

Well, maybe this will help some basic folks, like me, out there.

Regards,

Chad Voller

EDIT:
As leupold has pointed out, there is a Scheduler built-in to DotNetNuke to provide some regular maintenance of these items.  If you read up here: http://www.dotnetnuke.com/default.aspx?tabid=787&helpculture=en-us&helpmodule=Schedule you can learn more about how to use the scheduling functionality.  Thanks, Sebastian!

Great post, sir.  Thanks.  I've been asking about a few of these commands in other threads, and this is exactly what I was looking for.

So there is a difference between the sitelog, the eventlog, AND the transaction log? I am still trying to figure out the jargon, but know it was my transaction log that got quite large last time, as I never had it backed-up and truncated previously...

I could use some serious training on db maintenance and SQL...think I'll get our Uncle Sam to pay for a few beginner SQL courses in the not-too-distant future.  :)

Sebastian, thanks for the added input.  Your advice always brings something beneficial to the discussion.

Terp.  I believe the EventLog is the same thing as the TransactionLog.  Sebastian or Mitchel, could you please confirm that for the sake of this thread?

Thank you,
Chad

As leupold has pointed out, there is a Scheduler built-in to DotNetNuke to provide some regular maintenance of these items.  If you read up here: http://www.dotnetnuke.com/default.aspx?tabid=787&helpculture=en-us&helpmodule=Schedule you can learn more about how to use the scheduling functionality.  Thanks, Sebastian!