Analytics task causing SQL log bloat and server crash

Issue

A scheduled task is calling the process ContentPersonalization_CleanAnalyticsData. This process causes the SQL log file to bloat, eventually using up all disk space on the server and causing it to crash. The issue does not occur when the scheduler is disabled.

Environment: DNN 9.0 or newer

Root Cause

This problem occurs when the Content Personalization Data Clean stored procedure cannot complete. This task is intended to clear the Analytics_PageViews and Analytics_EventData table of pageviews older than 90 days. In cases where the Analytics_PageViews and Analytics_EventData table is very large, the Content Personalization Data Clean task will time out before it is able to delete any records. As the task repeatedly runs and times out, it adds a large number of logs from memory to disk, bloating the SQL log files and eventually taking up all available space on the server.

Resolution

Truncate the Analytics_PageViews table using the steps below. This will enable the scheduled cleanup task to complete without timing out or writing large amounts of data to disk.

1. Back up your database.
2. Run the following query:
   

   TRUNCATE TABLE Analytics_PageViews
   TRUNCATE TABLE Analytics_EventData 

3. Manually run the Content Personalization Data Clean task to confirm that it completes successfully.

In the future, you can recover truncated data from an old database backup if needed.