Paul Norwood wrote
If one of these three tables is the cause of the transaction log filling so fast what is a possible solution?
I see now the rest of my post went missing. Well, it was a long and detailed response about how each table gets filled. *slightly annoyed* but I'll try again:
You should first set your database transaction mode to 'simple'. You're not running an international banking system, you don't need the full level of backup options. Changing to simple will mean that when you run a transaction log backup it will truncate the log for you. Your hosting company should be able to help you with this.
The three tables I mentioned are log tables, and can be safely cleared out with 'truncate table xxxx' where xxx is the table name. This clears out the table, without logging each delete to the transaction log (and thus making the problem worse) - unlike 'delete from xxxxx'.
- The site log table has a record logged for each request to your DNN site. IT's an obvious candidate for fast growth, but this should be linear with your visitor numbers, unless you have some sort of redirection loop happening. You should probably just look at this table to see if updates are going in fast.
-The event log table logs a record for a lot of events, including logons, app restarts and page, module and general exceptions. It's here that your biggest chance of finding the culprit is. If you have a page which is causing an exception or 3, then every time you request that page, you are going to get 3 more rows in the table. Bonus points for logging the exception 'running out of disk space' which is a self-fulfilling prophecy. I would clear out your event log table with truncate table (seriously, you're never going to read all those log entries) and then wait and see how fast the table fills up. If it is filling fast, identify the most common records in the table and then troubleshoot from there.
- The schedlue history table contains an entry for each running of a job on the dnn schedule. Depending on what you've got in your schedule this could be a lot of things running very often (culprits like 'users online' running every minute, that sort of thing). ANother issue is that the scheduler runs a job called 'purge schedule' and if your schedule history table is too big, the operation will timeout, which will log an exception or 10, then enter some records in the history table, then turn around and schedule itself to do the same thing all over again. Again, truncate the table and see if it is filling fast.
Most likely it is going to be a combination of things, but you should think about getting rid of unwanted schedule items, clearing out your exception log and turning off your site log and replacing it withe google analytics or something similar. See what optiosn your host has - some packages come with quite good visitor tracking tools built into the hosting package. There really isn't a lot of reason to use the DNN site log.