Empty the DNN Site Log

An excessively large SiteLog table is likely to have an impact on the performance of your website. Do you know how many entries there are and whether the older ones are being cleaned out?

I have found that the DNN Site log table can be prone to growing, unrestricted.

This can be brought about by the failure to set a day limit and not enabling the scheduled clean up task.

In the past I have found values in the site log which precede the given cut-off date. Often as a result of the number of days being reduced, but the SiteLog purge not running properly.

Maybe looking through the scheduled task notifications you are seeing messages which show the purge of the site log is failing.

Failure of the scheduled task to run is often a sign of a table which has grown too large and is timing out on the deletion.

Information about the site log table SiteLog can be gained via the admin SQL Console.

Empty the DNN site log: count number of rows

SQL is entered within the main text area and run by clicking on Run Script.

Take care! It is easy for an entered command to wipe table contents, beyond the intended results.

The size of the table, ie. the numbers of rows in the table, can be determined by running the command:

SELECT count(*) FROM SiteLog

It’s also possible to see if there are entries earlier than your set limits.

This can be done by running the command

SELECT TOP 10 * FROM SiteLog ORDER BY DateTime DESC

Shown below a typical set of results, based upon a newly enabled sitelog, with the number of columns abbreviated for clarity.

DateTime Referrer Url UserAgent
21/03/2018 12:32:00 http://localhost/Admin/Site-Settings http://localhost/Default.aspx?TabId=67&language=en-GB Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:59.0) Gecko/20100101 Firefox/59.0
21/03/2018 12:32:00 http://localhost/ http://localhost/Default.aspx?TabId=56&language=en-GB Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.186 Safari/537.36
21/03/2018 12:33:00 http://localhost/Admin/Site-Settings http://localhost/Default.aspx?TabId=67&language=en-GB Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:59.0) Gecko/20100101 Firefox/59.0
21/03/2018 12:33:00 http://localhost/Admin/Log-Viewer http://localhost/Default.aspx?TabId=21&portalid=0&language=en-GB Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:59.0) Gecko/20100101 Firefox/59.0
21/03/2018 12:33:00 http://localhost/ http://localhost/Default.aspx?TabId=56&language=en-GB Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.186 Safari/537.36

If there are entries preceding the cut-off date these may be deleted using

DELETE FROM SiteLog WHERE DateTime < given date

Dates are awkward to deal with. By default entries are stored in American date format. It is also anticipated that dates used will also be in this format too. Rather than adding formatting information when running these commands you may wish to simply accept the default format.

To ensure that you are going to delete the correct items try a selection first, based upon your date range

SELECT * FROM SiteLog WHERE DateTime < CONVERT(DATETIME, ’03/21/2018′)

From my example above I am expecting this to return no results, which is the case.

Whilst setting the date as 22/03/2018 gives:

DateTime Referrer Url UserAgent
21/03/2018 12:32:00 http://localhost/Admin/Site-Settings http://localhost/Default.aspx?TabId=67&language=en-GB Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:59.0) Gecko/20100101 Firefox/59.0
21/03/2018 12:32:00 http://localhost/ http://localhost/Default.aspx?TabId=56&language=en-GB Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.186 Safari/537.36
21/03/2018 12:33:00 http://localhost/Admin/Site-Settings http://localhost/Default.aspx?TabId=67&language=en-GB Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:59.0) Gecko/20100101 Firefox/59.0
21/03/2018 12:33:00 http://localhost/Admin/Log-Viewer http://localhost/Default.aspx?TabId=21&portalid=0&language=en-GB Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:59.0) Gecko/20100101 Firefox/59.0
21/03/2018 12:33:00 http://localhost/ http://localhost/Default.aspx?TabId=56&language=en-GB Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/64.0.3282.186 Safari/537.36

You may also wish to sort by the datetime field descending, just to get the newer entries

SELECT TOP 20 * FROM SiteLog WHERE DateTime < CONVERT(DATETIME, ’03/21/2018′) ORDER BY DateTime DESC

It’s possible with too many entries in the SiteLog that the deletion of a range of rows will timeout.

You may wish to purge the SiteLog table. I have found that doing this via the DNN SQL control console can lead to time outs and failure to action.

If the truncation of the table times out or fails then you may find that it’s Better to perform the action via the SQL Server Management Studio, if this is available to you .

I navigate to the required database table view and click on the New Query button.

Enter the following

TRUNCATE TABLE SiteLog

Refresh your table view to see the change in the number of records.

DotNetNuke can require a restart of the application to reflect changes made to the database.

With DNN 9 the site log has been removed. Although it can be added back in if you wish.

Reviewing an older site I observed that the site log still existed.

Without the original admin configuration, it’s not possible to empty the log table by following the older practices to restrict the number of days or to turn off the site log.

I chose to use the SQL truncation detailed above to empty the site log table.

References

A Site Log module: https://github.com/DNNCommunity/Dnn.SiteLog