Our ActivityLog table in TN has grown to over 13 million records. Most records seem to be conversation references but some are system entries and are not tied to any conversations. This is still very 4.6. DB is SQL. Does anyone have a script/storeproc for cleaning this data base on a date value?? I believe 90% of this data is probably worthless… It would have to be deleted in all the tables it is referenced and then the ActivityLog.
I don’t know about wM 4.6, but in 6.0.1 and above, there’s a service called wm.tn.archive:archive that will not only take care of archiving the ACTIVITYLOG table, but also other TN-related tables, such as BizDoc, BizDocContent, BizDocAttribute, BizDocRelationship, BizDocUniqueKeys, ActivityLog, DeliveryJob, and EDITracking.
Now, the one thing that this service does not account for are ACTIVITYLOG records that do not contain related documents. To account for those records, I created a JDBC adapter service that runs right after wm.tn.archive:archive and executes the following SQL:
DELETE FROM ACTIVITYLOG
WHERE RELATEDDOCID IS NULL
AND ENTRYTIMESTAMP < SYSDATE-?
Where ? is an input to the service representing the number of days to keep.
If you wished, you could change this statement to insert the data into ARCHIVE_ACTIVITYLOG instead of just deleting it.
The archive service do exists in IS/TN4.6 version too…Bryan the Pierco mentioned suggestion should help for cleaning Activity log entries.
Thank you! I will give it a try…