Master appears hung on refresh thread due to long-running substitution variable SQL in Applications Manager

Products

• Automic Applications Manager

Issue/Introduction

The Applications Manager Master may appear to be in a hung or unresponsive state. This behavior occurs when a substitution variable query takes an excessive amount of time to return from the database, causing the refresh thread to wait.

Symptoms

• The Master process appears hung or stops processing jobs.
• RmiServer.log shows long durations for getSqlData() associated with substitution variables.
• Error messages in logs may include:
  AwE-9999 Internal error
Details: connection found to be in use for more than 5 minutes and validate method failed
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot continue the execution because the session is in the kill state.

Example lines found in logs:

10:11:12.329 rfp0: .DBAccess: getSqlData(): sID-null query elapsed ms 705989 rows 1 (11 minutes, 46.0 seconds)
10:30:03.366 rfp0: .DBAccess: getSqlData(): sID-null query elapsed ms 822742 rows 1 (13 minutes, 42.7 seconds)

 

Environment

Applications Manager
Database: SQL Server or Oracle

Cause

When a substitution variable is evaluated, the Master executes the associated SQL query. If the query execution time is excessive (e.g., several minutes or longer), it occupies the refresh thread. While the thread is waiting for the database to return the data, the Master cannot perform other refresh activities, leading to a perceived hang.

Resolution

1. Review the RmiServer<timestamp>.log to identify the specific SQL query that is taking a long time to return. Search for the string elapsed ms to find high millisecond values.
2. Work with the Database Administrator (DBA) to optimize the identified SQL query. This may involve adding indexes, updating statistics, or rewriting the query for better performance.
3. Verify the database connection pool settings and ensure the database server is not experiencing resource contention (CPU, Memory, or Temp space).
4. In theory, restarting the Applications Manager Master or deleting the condition/job associated with the subvar may terminate the running query, but optimization of the SQL is the permanent solution.

For further updates on this topic, users are encouraged to subscribe to this article.

Additional Information

Contact Broadcom Support if the hang persists after SQL optimization.