Identifying & dropping orphan temp tables

[wALF]

Hiya,

We are hosted on ACC v7 build 8894. While the campaign workflows are getting executed and when the interim run results option is checked, there are quite a few temp tables that get created.  These workflows don't get dropped (by the daily OOB cleanup workflow) even after the end user has unchecked that option after the workflow has finished running.  They continue to stay in the database and will only get dropped if you disable all activities of that workflow, run the workflow again that has a new branch comprising of only Start --> End.  Unfortunately, end users don't care of temp tables and don't perform this and hence a lot of unwanted temp tables continue to lurk in the database taking a hit on performance.

Is there an easier way to identify such orphan temp tables and drop them?  Obviously, we could exclude the temp tables that we need to preserver (eg: Lists or Deliveries).

[wALF]

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: @wodnicki 

-wALF

[wALF]

Is there a way to list down all temp tables based on their create date?

[wALF · Accepted solution]

Additionally, you can ask your DBA to execute Vacuum command on your DB. It might help you to free some space occupied by these temp tables. 

[wALF · Accepted solution]

That's right. We carry out the partial DB vacuuming almost every month and it really helps. However, DB vacuuming is more like 'disk fragmentation' of Windows and I doubt if it would drop such unwanted temp tables.

[#6]

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-oper...

Regards,

Craig

[wALF]

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

[wALF]

Hi Inder,

We already have a tech workflow for respective end users to be notified and take corrective action.  However, end users are end users and not many adhere to best practices, unfortunately.  Hence, the thought of identifying & dropping temp tables.

-wALF