SQL Agent job in Executing state even after failure

Question

Hi ,
I have a SQL agent job setup which is set to quit upon Job failure.

]1

Now oddly, even when the job fails - I confirmed this from Integration Services Catalog > Job Name > All Reports ., the actual job from within the SQL agent stays in Executing state. This is preventing further re-runs of the schedule. I have restarted the server (VM) , and it resolves for that instant. But the issues comes back again during next failure.

Any recommendation on why this is happening and what is the resolution.

Answer 1

Hi @sam nick ,

Please set Retry attempts as 0 and Retry interval(minutes) as 0 in Job Step Properties if you want to quit upon Job failure.

Best regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I made the change as per your recommendation, but the Job still shows as Executing in the Job Monitor even though the Services Catalog shows a failure. I have to manually terminate the job from the Job monitor.

Answer 3

Hi
if job still running u can stop it with below script:
please find Projectname and jobname from below table :

jobname=select name from msdb.dbo.sysjobs
projectname=select Project_name from SSISDB.catalog.executions

IF   (SELECT  TOP 1  sja.stop_execution_date FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj
ON sja.job_id = sj.job_id
WHERE name=@job_name
order by session_id desc) IS NULL
BEGIN
EXEC dbo.sp_stop_job @job_name ;
if exists(   select * from SSISDB.catalog.executions where status = 2 and Project_name=@project_name)
begin
	EXEC SSISDB.catalog.stop_operation where (select TOP 1 execution_Id from SSISDB.catalog.executions where status = 2 and Project_name=@project_name)
END