Agent SQL jobs remain in Active status even though they completed at Database level

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

Randomly ( impossible to replicate) a few SQL (Oracle) jobs remain in the "Active" status on the Agent SQL even though the job processing on the Oracle SQL database is complete, this particular Agent has a lot of activity around the time of the issue but no errors are displayed in the log file.

When this occurs, the AE/SQL Agent does not notice the end and leaves the SQL job in active status

After a manual restart of the SQL Agent, the affected jobs go into ENDED_LOST status.

The impacted Agent SQL is started with 1GB of Xmx: U02000110 Maximum Heap Memory: '1024' MB

Environment

Automation Engine 21.0.x and Agent SQL 21.0.x

Cause

Insufficient memory (-Xmx setting) for the Agent SQL

Resolution

After increasing the maximum heap memory value to 2048 MB for the SQL Agents and adjusting the library path, the problem with the SQL jobs did not occur anymore.

Documentation will be enhanced with the following way to start the Agent SQL to avoid such kind of problems, starting the Agent with 2GB of Xmx and only having -Xrs on Windows.

On the host, start the Agent with the following command: 

java [Windows:]-Xrs -Xmx2G -jar ucxjsqlx.jar