Admin: Housekeeping and temp table cleanup

About housekeeping and temp table cleanup

Databases accumulate temp tables and orphaned data over time. Regular cleanup keeps the system fast, stable, and compliant.

• Performance
  Fewer tables and records mean less to scan, so queries run faster, especially in large, high-volume databases.

• Resources
  Temp tables consume disk and memory. Removing them frees storage for critical data and lets the system allocate memory more effectively.

• Reliability
  Unchecked table growth can overload the system. Scheduling cleanup during maintenance windows prevents the emergency downtime that unplanned failures cause.

• Data integrity
  Orphaned tables (those without corresponding active records) create inconsistencies. Clearing them keeps the database an accurate picture of current data, and simpler for administrators to manage, migrate, and troubleshoot.

• Compliance and security
  Many industries regulate data retention and disposal. Old tables may also hold sensitive data that no longer needs to exist. Removing both reduces regulatory and security exposure.

• Scalability
  Cleanup tasks can be automated, so maintenance stays consistent without administrator effort. This matters more as databases grow and loads increase.

Working with housekeeping and temp table cleanup

Now that you know why housekeeping and temp table cleanup are important, this section provides guidance on how to approach these tasks in RPI.

RPI runs housekeeping automatically on a daily schedule, and as part of that run it evaluates working (temp) tables named Dataflow_<n> to determine whether they're safe to drop. The Housekeeping tab shows a log of what housekeeping has done — tables it dropped and any errors encountered — but it does not list tables that housekeeping evaluated and deliberately left in place. Keep that in mind: a table not appearing in the log doesn't mean it was checked and found fine; it may simply not have been due for cleanup yet.

When housekeeping will not automatically clean up a table

Housekeeping will leave a Dataflow_<n> table in place under any of the following circumstances:

• RPI can't find a status for the table
  If housekeeping can't locate the database records that describe where the table came from, it leaves the table alone rather than risk dropping something still in use.

• The associated workflow hasn't reached a finished state
  Housekeeping only considers a table for cleanup once its workflow's status is one of: Completed, Stopped, Expired, or Rolled Back. Any other status — including Playing, Paused, Failed, Queued, or Waiting for Trigger — means the workflow is still active or in progress, and the table will not be dropped.

• Not enough time has passed since the workflow last had activity
  Even once a workflow reaches one of the four statuses above, RPI keeps the table for a minimum retention period after the workflow's last recorded activity (28 days by default) before it becomes eligible for cleanup. A table from a workflow that completed very recently is expected to still exist; that's not the same as being orphaned.

• There's still an active recurring trigger tied to the workflow
  This check applies specifically when the workflow's status is Completed: even though that one run finished, if a recurring schedule is still active for that workflow, RPI keeps the table because it expects the workflow to run again.

In these cases, you'll need to identify RPI working tables that have become orphaned and need to be manually dropped.

Finding tables that have become orphaned

The integer part of the Dataflow_<n> name can be matched to op_DataWorkflows.ProcessInstanceRefNo, which holds the execution state for that dataflow run (status, current activity, timestamps, and similar). From there, you can join to op_InteractionWorkflows to get the overall workflow's status and last-activity information. Note that the second join is not made on that same integer — op_DataWorkflows and op_InteractionWorkflows are linked by a separate shared workflow-association identifier that both tables carry.

A typical lookup looks like this:

SELECT dw.*, iw.Status, iw.LastEventTimestamp
FROM op_DataWorkflows dw
JOIN op_InteractionWorkflows iw
    ON dw.WorkflowAssociationInstanceID = iw.WorkflowAssociationInstanceID
WHERE dw.ProcessInstanceRefNo = <n>   -- <n> is the integer from Dataflow_<n>

If this query returns no matching row at all, that's itself a sign of the "RPI can't find a status" case described above; treat it as unresolved and investigate further rather than assuming it's safe to drop.

Statuses to check: When you do get a result, use the Status value to judge the state of the workflow.

Anything that doesn't fall clearly into the "finished" group above should be treated as still in use.

Other information worth checking before you conclude a table is orphaned:

• Last activity time
  Compare this against your retention expectations; a recently-finished workflow is not orphaned just because its status looks terminal.

• Whether a recurring schedule is still active for that workflow
  A workflow can show Completed for its most recent run while still being reused on a schedule. If so, its table is intentionally retained.

Should you check for orphaned tables regularly?

Yes. The Housekeeping log only reflects tables that were actually dropped or that errored during cleanup, not tables that were evaluated and deliberately skipped. Because of this, orphaned tables can accumulate silently, so we recommend running the identification steps above on a periodic basis (e.g., monthly, or as part of routine environment maintenance) rather than only when storage or performance issues prompt you to look.

Dropping confirmed orphaned tables

Once you've confirmed through the steps above that a table is genuinely orphaned — no matching workflow record, or a workflow that is truly finished, past its retention window, and without an active recurring trigger — the table can be dropped directly (DROP TABLE). There is currently no dedicated utility or automated tool for this step, and there's no way to flag a table so that housekeeping picks it up sooner on its own; housekeeping re-evaluates everything fresh on its next scheduled run based on current workflow and trigger state. As always, confirm you're connected to the correct tenant database and take a backup or otherwise ensure recoverability before dropping any table.