How do I speed up the Deleted Data Cleanup event in installations which use MS SQL for the system database?


Webtrends Analytics 8.5+
Webtrends Analytics 9.x


The “Deleted Data Cleanup” event deletes data from the database and files within Webtrends. Deleting a profile from the Webtrends user interface marks the profile for deletion within the database, and even though the profile and its data are no longer visible, the files and data are not permanently deleted until fourteen days have passed from the time the request was submitted. The analysis event for the profile is still visible in the Scheduled Events, but it is disabled. This event will be deleted on the same day the profile and its data is permanently deleted.


To reduce the time period before the “Deleted Data Cleanup” event permanently deletes a profile and its data, perform the following SQL query:


SET @INIid =(SELECT id FROM wtmaster.dbo.wt_app_profiles WHERE profile_name = ‘deletedprofilecleanup.ini’)
SET @Default =(SELECT id FROM wtmaster.dbo.wt_app_sections WHERE section_name = ‘default’)
SET @DaysID = (SELECT id FROM wtmaster.dbo.wt_app_tokens WHERE key_name = ‘daysuntilprofiledeletion’)
SET @Days = 1

UPDATE wtmaster.dbo.wt_app_confdata SET data = @Days
WHERE profile_id = @INIid
AND section_id = @Default
AND key_id = @DaysID;

Change the SET @Days = 1 to the desired number of days to wait before the event is finally deleted.

To use this query, you will need to install and run SQL Management Studio Express or use the SQL Management Studio that came with the SQL database distribution. Click the New Query button and paste the query into the query field, then click Execute.