SQL job running for hours - how to best kill or end it

Products

• CA Workload Automation AE - Business Agents (AutoSys)
• CA Workload Automation AE - System Agent (AutoSys)
• CA Workload Automation AE - Scheduler (AutoSys)
• Workload Automation Agent
• CA Workload Automation AE

Issue/Introduction

We have an issue where a SQL job is running for hours at a time, but not completing, and we are unable to kill the job.

This issue happens sporadically, and we are unable to kill the job when this happens.

The only solution so far, which isn't permanent, is that we have to change the status of the job to SUCCESS.

Unfortunately changing the job status isn't the best solution since it only registers on our end, but not on the SQL side.

Environment

Release : 11.3

Component : CA Workload Automation Database Agent

Resolution

When Autosys sends a Kill Job to a SQLAGENT job type, the Agent receives "MSSQL CANCEL" AFM request.
To submit the cancel request, the plugin basically then executes "sp_stop_job".
If the stored procedure return value is 0 (success), then plugin returns RESPONSE COMPLETE.
For ex:
. . test sql testRunDuplicateJob/TEST.2/MAIN RESPONSE COMPLETE Status(Cancel request submitted) Plugin(sql)

If somehow the job completed in time (very rarely, depends on timing), then you will see a STATE COMPLETE, otherwise it will return STATE FAILED with the Status containing the reason.
For ex:
20200127 15304603+0600 test . testRunDuplicateJob/TEST.1/MAIN STATE FAILED Status(Performing completion actions) LStatus(step 1 Cancelled; ) SetEnd Cmpc(3)

So as long as sp_stop_job really stops all the tasks/actions associated with the SQL Server job then it things should be ending within SQL.