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
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
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.
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).
