snippetsqlMinor
SQL Jobs Running - How to Kill
Viewed 0 times
killsqljobsrunninghow
Problem
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?
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 nullSolution
To Get Currently running jobs and their Status
Stop\kill Job command
--- 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 NULLStop\kill Job command
USE msdb ;
GO
EXEC dbo.sp_stop_job N'Your Job Name' ;
GOCode Snippets
--- 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 NULLUSE msdb ;
GO
EXEC dbo.sp_stop_job N'Your Job Name' ;
GOContext
StackExchange Database Administrators Q#110672, answer score: 6
Revisions (0)
No revisions yet.