SQL Agent job in Executing state even after failure

sam nick 286 Reputation points
2021-02-20T15:08:41.997+00:00

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

![70230-image.png]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.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,493 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Monalv-MSFT 5,896 Reputation points
    2021-02-22T03:20:34.287+00:00

    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.

    70390-job-step-properties.png

    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.

    0 comments No comments

  2. sam nick 286 Reputation points
    2021-02-25T04:18:16.337+00:00

    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.


  3. Ame Hajimohammadi 0 Reputation points
    2023-03-15T07:20:36.85+00:00

    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
    
    0 comments No comments