0

I am using SQL Server 2014 and I have a job (let's call it JN5) scheduled to run at a specific time on a daily basis. The job runs an SSIS package and has 11 steps in it. Looking at the Job History in SSMS, the job seems to be stuck at Step 9. It is not showing as a failure but "In Progress".

I am having a hard time trying to stop it. I have tried the following:

USE msdb ; 
GO      

EXEC dbo.sp_stop_job N'JN5' ;  
GO

I get the following error after execution:

Msg 22022, Level 16, State 1, Line 2
SQLServerAgent Error: Request to stop job JN5 (from User sa) refused because the job is not currently running.

Then, I tried the following: I restarted the SQL Server Agent through SQL Server Configuration Manager. When I viewed the SQL Job Status (after a Refresh) in SSMS, it was still showing "In Progress" !

My next attempt to solve the issue was to restart SQL Server 2014. I then checked the status again in SSMS. It was still showing the "In Progress" status!

I just had a look at the following on StackOverflow: SQL Server job activity (job failed but history records in job activity still showing 'running' status)

However, I am not sure if the above link relates to my problem.

Any ideas on what I can attempt next?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3115933
  • 4,303
  • 15
  • 54
  • 94
  • The linked you included _appears_ to answer your question. Were you able to attempt to apply the solution and if so, can you edit your original post? – Sean Brookins May 18 '19 at 09:24
  • No, the solution could not be applied in my case. My SSIS package is not run from the SSIS Catalog as referred to in that link. – user3115933 May 18 '19 at 10:17
  • So here is what I did. Despite the SQL Job showing an "In Progress" status, I re-scheduled it for a new run. The SQL job executed correctly this time and showed a "Completed" status. I don't consider this as the "solution" to the problem but it did work in my case to clear that "In Progress" status. – user3115933 May 18 '19 at 10:21
  • That is an odd behavior. I don't know what controls the display status of the jobs in SQL, so I'm not sure if perhaps it was aborted in a way that precludes the flag from being updated or what. Thanks for following up with the end result! – Sean Brookins May 20 '19 at 03:29

2 Answers2

0

I encountered a similar issue recently. I had a job running, scheduled via SQL Server Agent Jobs, executing from the SSISDB Catalog. (ie a scheduled SSIS job).

During the job, one of the steps processes an SSIS task which used a connection manager to a file (a sql script). While this was running, someone applied some patches to the server and restarted the SQL services stack.

This resulted in the job looking like it was processing (via job history) and appeared to be running (via Integration Services dashboard)... but there was no longer any IO (and none of the SQL in the script had completed execution)...so I knew it was relatively safe to kill the job.

I got the same error:

Msg 22022, Level 16, State 1, Line 9 SQLServerAgent Error: Request to stop job XXXXX (from User YYYYY) refused because the job is not currently running.

So I referenced the ID of the operation from the Integration Services Dashboard, and issues the following commands:

USE SSISDB
GO

EXEC [catalog].[stop_operation] <OperationId>

This removed the running job from the Integration Services dashboard.

Aksen P
  • 4,564
  • 3
  • 14
  • 27
user11797773
  • 1
-1

When I had this problem I could only solve it by deleting the job. Before, I created the script to recreate the job. So I only lost the job history.

Steffen Jabin
  • 1
  • This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/low-quality-posts/27737825) – EJoshuaS - Stand with Ukraine Nov 30 '20 at 16:34
  • @EJoshuaS-ReinstateMonica The answer has "... I could only solve it by ..." literally in the text. – TT. Nov 30 '20 at 16:43
  • Thank you, @TT. I don't see the mistake I made either. – Steffen Jabin Dec 02 '20 at 08:02