February 3, 2016 at 2:05 am
Hi All
I use the below script to monitor currently running jobs in SQL Server. It seems to be reporting on some jobs that are definitely not running when I execute this script.
Where am I going wrong with this script?
SELECT
job.name,
job.job_id,
job.originating_server,
activity.run_requested_date,
DATEDIFF( SECOND, activity.run_requested_date, GETDATE() ) as Elapsed,
run_requested_date, stop_execution_date
FROM
msdb.dbo.sysjobs_view job
JOIN
msdb.dbo.sysjobactivity activity
ON
job.job_id = activity.job_id
JOIN
msdb.dbo.syssessions sess
ON
sess.session_id = activity.session_id
WHERE
run_requested_date IS NOT NULL AND stop_execution_date IS NULL
Thanks
February 3, 2016 at 4:34 am
Possibly the stop_execution_date stays NULL if the job is cancelled or interrupted? That would mean any such jobs will appear in your result set until the cancelled execution is aged out of the job history. Try grouping by job_id and taking the max run_requested_date. You might also consider using the sysjobhistory table, which has a run_status column telling you whether the job succeeded or failed, or whether it was retried or cancelled.
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply