Prevent Analytics Table from Growing

Issue

Administrators might often face issues either with disk space on their DNN server, or multiple 500 errors in response to a fairly large amount of queries performed against the Analytics Service.

Environment

DNN 8.5.0 and below versions.

Root Cause

For versions prior to DNN 9.0.0, a fairly large amount of disk space and several running queries were consumed by three Analytics database tables:

  • Analytics_PageViews
  • Analytics_EventData
  • Mechanics_UserScoringLog

If the Analytics module is not used, disabling Analytics and file statistics would benefit to free disk space and lower the server load for any queries for the Analytics service (that can often run into the millions). These tables are used to collect information about site visitors (who is visiting your page, IP address of a visitor, date of visit, which portal is being visited, which page is being visited, etc).

Table locations: [SITENAME].[dbo].[Analytics_PageViews][SITENAME].[dbo].[Analytics_EventData][SITENAME].[dbo].[Mechanics_UserScoringLog]

You may use the following SQL statements to check if there are numerous entries for these tables:

SELECT CreatedOnDate FROM [SITENAME].[dbo].[Analytics_PageViews] ORDER BY CreatedOnDate DESC;
SELECT CreatedOnDate FROM [SITENAME].[dbo].[Analytics_EventData] ORDER BY CreatedOnDate DESC;
SELECT CreatedOnDate FROM [SITENAME].[dbo].[Mechanics_UserScoringLog] ORDER BY CreatedOnDate DESC;

Resolution

  1. Create a backup of your database before performing any actions.
  2. Locate the file  DesktopModules\DNNCorp\EvoqContentLibrary\ClientScripts\dnn.analytics.Injected.js on your DNN server in the site folder.
  3. Remove or comment the two functions listed below.
    $.ajax(baseServicepath + 'LogEventData', options);
    $.ajax(baseServicepath + 'LogPageView', options);
  4. Login to DNN portal as an administrator.
  5. Click Host in the main menu.
  6. Click Advanced Setting.
  7. Click Schedule.
  8. Disable AnalyticsContent.
  9. Disable AnalyticsEngage (if applicable).
    inline-1038974513.png
  10. Truncate the tables to remove any existing records by running the following queries:
    TRUNCATE TABLE Mechanics_UserScoringLog;
    TRUNCATE TABLE Analytics_PageViews;
    TRUNCATE TABLE Analytics_EventData;

Confirmation

You can use the following SQL statements to check if the collection for Analytics logs and files has been disabled:

SELECT CreatedOnDate FROM [SITENAME].[dbo].[Analytics_PageViews] ORDER BY CreatedOnDate DESC;
SELECT CreatedOnDate FROM [SITENAME].[dbo].[Analytics_EventData] ORDER BY CreatedOnDate DESC;
SELECT CreatedOnDate FROM [SITENAME].[dbo].[Mechanics_UserScoringLog] ORDER BY CreatedOnDate DESC;

Any Analytics collection should stop and you should see an increase in free available space on your DNN server.

 

Content Contributors: Giancarlo Mora, Manuel Gonzales

 

Comments

0 comments

Please sign in to leave a comment.