Unable to stop SQL Server Agent Job showing "In Progress" status

  • hoolash

    Ten Centuries

    Points: 1258

    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:?

    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!

    What can I try as a next step to solve this problem?

  • Jeff Moden

    SSC Guru

    Points: 994542

    Considering that you posted this in the SQL Server 7/2000 forum and you clearly state that you're using 2014 and an SSIS package (and SSIS didn't even exist in 7/2000), you might have better luck posting this question in the 2014 forum. 😉

    As to your question, it sounds like your "Step 9" in the SSIS package might be running something outside of SQL Server.  In such cases, bouncing the SQL Server Service won't actually stop the stuck process.  You either need to find out what the stuck process is and stop that processes (make sure you kill the entire tree process) or you need to actually restart the whole server.

    Whichever, you need to find out WHY and HOW this process got stuck because it WILL happen again. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply