4

I ran the below query and it shows list of currently running jobs. However, in Activity Monitor none of these are running. How do I verify that these are/are not running?

And how do I kill them?

    select sj.name
    ,sja.*
from msdb.dbo.sysjobactivity as sja
inner join msdb.dbo.sysjobs as sj on sja.job_id = sj.job_id
where sja.start_execution_date is not null
and sja.stop_execution_date is null

enter image description here

4
  • Have you checked this answer?
    – Tom V
    Commented Aug 11, 2015 at 13:14
  • You mean Activity monitor or Job Activity Monitor?
    – KASQLDBA
    Commented Aug 11, 2015 at 14:20
  • Actually that procedure doesn’t really kill jobs. I’ve executed it and when checking, the job is still running. Has this been tested? Does any one have a procedure that actually kill a job?
    – sqlRelated
    Commented Aug 29, 2018 at 17:01
  • Assuming you're referring to this answer, please note that the comments there indicate that the initial query won't return different results after you kill the job, because it includes historical data. The Job Monitor dialog in SSMS can be used to confirm that sp_stop_job works.
    – RDFozz
    Commented Aug 29, 2018 at 19:36

3 Answers 3

6

To Get Currently running jobs and their Status

--- Script By: Amna Asif___________________For: sqlcache.blogspot.com  
--- Purpose: Currently running jobs and their Status  



    SELECT  jb.name,  
            jb.originating_server ServerName,  
            ja.run_requested_date JobStartTime,  
            DATEDIFF(MINUTE, ja.run_requested_date, GETDATE()) as TimeElapsed_Minutes,  
            case when ja.last_executed_step_id is null  
                 then 'Step '+Cast (start_step_id as varchar(20)) +' ['+sstep.step_name+'] executing'  
                 else 'Step ' +Cast ( last_executed_step_id+1 as varchar(20))+' ['+ lstep.step_name  
                      + '] executing'   
            end CurrentStepID , sstep.step_name JobStartedfromStep  
            , Case ja.run_requested_source when 1 then 'SCHEDULER'  
                                   when 2 then 'ALERTER'  
                                   when 3 then 'BOOT'  
                                   when 4 then 'USER'  
                                   when 6 then 'ON_IDLE_SCHEDULE' else 'Unknown' end JobRunSource  
    FROM    msdb.dbo.sysjobs_view jb  
            INNER JOIN msdb.dbo.sysjobactivity ja ON jb.job_id = ja.job_id  
            INNER JOIN msdb.dbo.syssessions sess ON sess.session_id = ja.session_id  
            LEFT JOIN MSDB.DBO.sysjobsteps sstep ON sstep.step_id =start_step_id and sstep.job_id=jb.job_id  
            LEFT JOIN MSDB.DBO.sysjobsteps lstep ON lstep.step_id =last_executed_step_id + 1 and lstep.job_id=jb.job_id   
    WHERE   stop_execution_date IS NULL   
            AND run_requested_date IS NOT NULL

Stop\kill Job command

USE msdb ;
GO
EXEC dbo.sp_stop_job N'Your Job Name' ;
GO
2
  • I like this query, but when I call sp_stop_job N'<name>' I get told "job stopped successfully" but if I rerun the query, it still appears. Why? Commented Jun 20, 2016 at 11:55
  • because sysjobs_view contain list of all jobs and sysjobactivity/ syssessions contain history of jobs
    – AA.SC
    Commented Jun 20, 2016 at 12:48
0

First, try to stop job using user interface, if you did the database returned "job stopped successfully" but the job history shows the job is still running, then following steps worked for me:

  • Make sure no job activity exists where stop_execution_date is null, if so update it for example with getdate() value

     SELECT 
     j.job_id
     ,a.*
     FROM msdb.dbo.sysjobs J 
         JOIN msdb.dbo.sysjobactivity A ON A.job_id=J.job_id 
     WHERE J.name IN (N'CRUNCHTIME_ORCH') -- Your Job Name
     AND A.run_requested_date IS NOT NULL 
     AND A.stop_execution_date IS NULL
    
  • Insert a row into msdb.dbo.sysjobhistory where step name is "(Job outcome)" for the unfinished job_id (leave column instance_id blank since it is autogenerated). Easiest way is to use same values as lates row in table for the same step name:

     insert into msdb.dbo.sysjobhistory ([job_id]
     ,[step_id]      ,[step_name]
     ,[sql_message_id]       ,[sql_severity]
     ,[message]      ,[run_status]
     ,run_date       ,run_time
     ,[run_duration]     ,[operator_id_emailed]
     ,[operator_id_netsent]      ,[operator_id_paged]
     ,[retries_attempted]        ,[server])
     select 
     [job_id]        ,[step_id]
     ,[step_name]        ,[sql_message_id]
     ,[sql_severity]     ,[message]
     ,[run_status]       ,[run_date]
     ,run_time       ,0 [run_duration]
     ,[operator_id_emailed]      ,[operator_id_netsent]
     ,[operator_id_paged]        ,[retries_attempted]
     ,[server]
     from msdb.dbo.sysjobhistory
     where job_id = @job_id
     and step_name = '(Job outcome)' 
     and instance_id = @anExistingInstanceId 
    

The value of @anExistingInstanceId could be calculated as follows:

    select max(instance_id) 
    from msdb.dbo.sysjobhistory
    where job_id = @job_id
    and step_name = '(Job outcome)' 

Afterwards, if you refresh the job history you should see that job is already finished.

0

Previous Ismael Serrano answer was hopefully the solution, but after some modifications even adding last step, link to job history, etc. It didn't work for the current job activity (just history).

If you really want to get rid of the problem without restarting the agent (best way), after tried stopping it, create a script to DROP and CREATE the job, this will clean the problem. (You will lose the history).

1
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented May 14, 2023 at 10:16

Not the answer you're looking for? Browse other questions tagged or ask your own question.