Skip to main content

List running Schedule jobs with elapsed time in SQL Server

A few months ago, I wrote some articles about SQL Server scheduled jobs and details for same which are listed below
  1. When exactly scheduled jobs started and completed
  2. Find running scheduled jobs in SQL Server - Alternate way
  3. Another way to list out running scheduled jobs
Now I am sharing another one script which belongs to above relevant articles with capture all details of executing scheduled jobs with elapsed time. Below is a script which collects all user scheduled jobs which are running for 30 minutes,
USE msdb

 job.Originating_Server as ServerName, 
 job.Name as JobName, 
 activity.run_requested_Date as StartDate,
 DATEDIFF(MINUTE, activity.run_requested_Date, GETDATE()) as ElapsedTime_Minute
FROM dbo.sysjobs_view job WITH (NOLOCK)
INNER JOIN dbo.sysjobactivity activity WITH (NOLOCK)
 ON (job.job_id = activity.job_id)
WHERE run_Requested_date is not null and stop_execution_date is null
AND activity.run_requested_Date > DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
AND job.category_id = 0
AND DATEDIFF(MINUTE, activity.run_requested_Date, GETDATE()) >= 30
I would like you to share other ways to capture the same information. Stay tuned for more.