Three-Year-Old Production Instance: 1.8 TB Database, Failed Purge, and Disk at 95%
A Sterling B2B Integrator production instance has run for three years with no successful business process purge for the last six months. The database is 1.8 TB, and the filesystem holding logs, temp files, and adapter work directories is 95% full. The BP engine is configured with 50 worker threads and a database connection pool of 60. Average BP latency has doubled, the JMS queue for inbound AS2 has grown to 25,000 messages, and the team restarts the application every Sunday to recover heap. A purge job was scheduled nightly but has failed since a schema change; the last error was a rollback segment/tablespace issue. The business cannot accept a full outage longer than two hours, and month-end peak starts in five days. The team can change configuration, run maintenance jobs, and archive data.
1. What is your remediation sequence for the next five days, and what would you explicitly not do?
Read the worked answer
The sequence is: protect the system from running out of disk, restore a controlled purge capability, then reduce queue and latency pressure without a risky big-bang change before month-end.
First, free safe space: rotate and compress logs, clear stale temp and adapter work files after confirming no in-flight transfer needs them, and move completed archives off the affected filesystem. Verify backups and archive integrity before deleting anything. Second, fix the purge job in a non-production environment with a representative data volume. The rollback/tablespace error indicates the purge is trying to delete too much in one transaction; change it to delete in batches by date and status with a commit interval, and pre-create or extend the undo/temp tablespace if needed. Third, run the corrected purge in a maintenance window in batches, monitoring tablespace and rollback usage. Do not attempt a single DELETE of millions of BP rows during peak; it will generate excessive undo, lock resources, and can fail again. Fourth, triage the JMS backlog: determine whether consumers are stopped, failing, or simply behind. Restarting consumers without understanding poison messages can loop.
What I would explicitly not do: disable monitoring to reduce load, purge all history without a retention decision, add worker threads before checking database waits, or use the weekly restart as the capacity plan.
2. How would you tune BP engine threads, DB pool, and JMS consumers, and what evidence would you collect first?
Read the worked answer
I would collect evidence before changing any of the three. For the BP engine: active versus maximum threads, queue wait time, long-running process count, and thread dumps during latency spikes. For the database: active versus idle connections, wait events, top SQL, undo/temp usage, and whether the pool is exhausted by adapters or by BPs. For JMS: queue depth, consumer count, redelivery count, and whether messages are poison or simply unconsumed. I would also check adapter concurrency, because an SFTP or AS2 adapter can hold sessions and threads independently of BP workers.
The 50-thread and 60-connection settings are not obviously wrong, but they are only meaningful relative to database capacity and adapter demand. If the database is already showing heavy wait events, raising BP threads will increase contention. If the pool is fully checked out while BP threads are idle, the bottleneck is elsewhere. If JMS has 25,000 messages and zero active consumers, tuning BP threads will not drain it; fix the consumer first.
I would change one variable at a time in small increments, during a controlled window, and measure p95 latency, error rate, queue drain rate, and database waits after each change. Increase heap only after taking heap dumps and ruling out a leak; a Sunday restart that restores service is a symptom, not a tuning strategy. For month-end, I would prioritize stabilizing the queue and disk over aggressive performance tuning.
3. What long-term purge/archive and monitoring policy would prevent recurrence?
Read the worked answer
The policy needs a retention decision by document type and regulatory requirement, not a single “delete everything older than X” rule. Closed business processes and completed transactions should be archived to a separate archive store or archive tables on a defined schedule, then purged from the operational tables in controlled batches. The purge job should run off-peak, commit at a configured batch size, and record rows deleted, duration, and errors. It should fail loudly and alert; the six-month silent failure is the real root cause.
Monitoring should cover: database and tablespace growth trend, undo/temp consumption during purge, purge job success and last successful run, filesystem usage for logs/temp/adapter work directories, JMS queue depth and oldest-message age, BP queue age, and heap after garbage collection. Capacity planning should project growth from current rates rather than waiting for 95% disk. I would also schedule index statistics refresh and review whether archive queries need separate indexes so archiving does not slow production.
Finally, document a runbook for the purge/archive cycle, including how to validate a backup, how to pause and resume the job, and who approves retention exceptions. The goal is a predictable maintenance rhythm that fits inside the two-hour outage limit, not an annual emergency cleanup.