Identifying & dropping orphan temp tables

Hi,

Temp tables will not automatically cleaned when you unchecked the interim run results option after workflow finished, as the temp tables are already created so un-checking will not effect anything. You need to ask end users to follow best practices.

You can create a utility workflow to list all workflows where this option is checked and send an email to admins/operators who can see this list in their inbox and take action, or inform end users to take action. This is not direct solution, but at least you will get an alerts email on daily or weekly basis. 

Hope this helps. Thank you.

Regards

Hi wALF,

So with all workflow changes, just changing a setting and saving it doesn't change the underlying workflow job.  The workflow itself would need to be restarted in order for the temp table to be released.  Additionally, even after this the workflow the cleans out the old temp workflow data is the "Database Cleanup" workflow which only runs daily.

Also if you are still seeing temp data being stored, workflows in a paused state or running without an End activity will also retain temp data.

As others mentioned, best practices need to be followed by your users to not use the Keep Interim Results option. https://docs.adobe.com/content/help/en/campaign-classic/using/automating-with-workflows/general-operation/workflow-best-practices.html

Regards,

Craig

Thanks Amit, Inder & Craig for your responses.  Would appreciate if you can comment on,

If I'm not wrong, the naming convention for a workflow temp table comprise of wkf_<workflowID>  (or something similar).  If we can somehow have a javascript scanning through the database to identify workflow temp tables present for workflows that are missing/deleted, can't we drop all such workflow temp tables? Or as an alternative, a javascript for dropping workflow temp tables that are older than X days (say a month, as I don't see any reason why anyone would want to preserve old table records).

Would there be any repercussion to having one these in built? 

CC: @jonathon_wodnicki 

-wALF

Hello,
One thing I've found quite easy is to do it this way:
1- create a filter on a workflow view 
 identify all finished workflow with attribute memo (xml data) containing the string keepResult="true"
In each workflow, disable the "keep result of the interim population" option and then start & stop the workflow:
each time you'll start the workflow it will refresh its context with the keepResult="false" and it will delete all worktables when you'll stop it (this is the runWF process that is asking the DB to drop the tables)
2- create a filter on a workflow view
identify all running workflow with attribute memo (xml data) containing the string keepResult="true" and a process id (@pid) equal to 0
same process as before but just restart each workflow as a pid 0 usually means that the workflow is waiting for a scheduler task (carefull about workflows with a wait task)

Of course, you could create a generic workflow to do that using queries and js activities (check that API function for example: Restart (adobe.com)  Start (adobe.com) Stop (adobe.com)

But in any case, setting such processes in place should never be a free card for bad AC hygiena: educating the users should be a priority