2

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

  • Have you checked this answer? – Tom V - try topanswers.xyz Aug 11 '15 at 13:14
  • You mean Activity monitor or Job Activity Monitor? – KASQLDBA Aug 11 '15 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 Aug 29 '18 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 Aug 29 '18 at 19:36
1

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
|improve this answer|||||
  • 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? – VictorySaber Jun 20 '16 at 11:55
  • because sysjobs_view contain list of all jobs and sysjobactivity/ syssessions contain history of jobs – AA.SC Jun 20 '16 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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