Share via

How to find the SQL Server Running Jobs Time Elapsed status

  • Article

There are many situations wherein we want to know the time for which a particular or all the jobs are executing. We can find the execution status from the Job Activity Monitor but we don't get the duration of execution in it. we can use the below query to find out the same. The Query fetches the Running jobs and calculates the time for which they are executing.

01.CREATE TABLE  #enum_job
02.  (
03.    Job_ID uniqueidentifier,
04.    Last_Run_Date         INT,
05.    Last_Run_Time         INT,
06.    Next_Run_Date         INT,
07.    Next_Run_Time         INT,
08.    Next_Run_Schedule_ID  INT,
09.    Requested_To_Run      INT,
10.    Request_Source        INT,
11.    Request_Source_ID     VARCHAR(100),
12.    Running               INT,
13.    Current_Step          INT,
14.    Current_Retry_Attempt INT,
15.    State                 INT
16.  )
17.INSERT INTO
18.  #enum_job EXEC  master.dbo.xp_sqlagent_enum_jobs 1,  garbage
19. 
20. 
21.SELECT
22.  R.name ,
23.  R.last_run_date,
24.  R.RunningForTime,
25.  GETDATE()AS now
26.FROM
27.  #enum_job a
28.INNER JOIN
29.  (
30.    SELECT
31.      j.name,
32.      J.JOB_ID,
33.      ja.run_requested_date AS  last_run_date,
34.      (DATEDIFF(mi,ja.run_requested_date,GETDATE())) AS  RunningFor,
35.      CASE LEN(CONVERT(VARCHAR(5),DATEDIFF(MI,JA.RUN_REQUESTED_DATE,GETDATE())/60))
36.        WHEN 1 THEN '0' + CONVERT(VARCHAR(5),DATEDIFF(mi,ja.run_requested_date,GETDATE())/60)
37.        ELSE CONVERT(VARCHAR(5),DATEDIFF(mi,ja.run_requested_date,GETDATE())/60)
38.      END
39.      + ':' +
40.      CASE LEN(CONVERT(VARCHAR(5),(DATEDIFF(MI,JA.RUN_REQUESTED_DATE,GETDATE())%60)))
41.        WHEN 1 THEN '0'+CONVERT(VARCHAR(5),(DATEDIFF(mi,ja.run_requested_date,GETDATE())%60))
42.        ELSE CONVERT(VARCHAR(5),(DATEDIFF(mi,ja.run_requested_date,GETDATE())%60))
43.      END
44.      + ':' +
45.      CASE LEN(CONVERT(VARCHAR(5),(DATEDIFF(SS,JA.RUN_REQUESTED_DATE,GETDATE())%60)))
46.        WHEN 1 THEN '0'+CONVERT(VARCHAR(5),(DATEDIFF(ss,ja.run_requested_date,GETDATE())%60))
47.        ELSE CONVERT(VARCHAR(5),(DATEDIFF(ss,ja.run_requested_date,GETDATE())%60))
48.      END AS  RunningForTime
49.    FROM
50.      msdb.dbo.sysjobactivity AS  ja
51.    LEFT OUTER JOIN  msdb.dbo.sysjobhistory AS jh
52.    ON
53.      ja.job_history_id = jh.instance_id
54.    INNER JOIN msdb.dbo.sysjobs_view AS  j
55.    ON
56.      ja.job_id = j.job_id
57.    WHERE
58.      (
59.        ja.session_id =
60.        (
61.          SELECT
62.            MAX(session_id) AS  EXPR1
63.          FROM
64.            msdb.dbo.sysjobactivity
65.        )
66.      )
67.  )
68.  R ON  R.job_id = a.Job_Id
69.AND a.Running   = 1
70.DROP TABLE  #enum_job