SQL Script to find Current Running Jobs and elapsed time

Leave a comment (1) Go to comments

I do get a call from developers to check weather a any job is running on the server or not ? Mostly on Sunday morning they want to check that all data import / exports jobs finishes on time. So they wanted me to know what all the jobs are currently 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.

SQL Script to find current Running Jobs

CREATE TABLE #enum_job 
  ( 
    Job_ID uniqueidentifier, 
    Last_Run_Date         INT, 
    Last_Run_Time         INT, 
    Next_Run_Date         INT, 
    Next_Run_Time         INT, 
    Next_Run_Schedule_ID  INT, 
    Requested_To_Run      INT, 
    Request_Source        INT, 
    Request_Source_ID     VARCHAR(100), 
    Running               INT, 
    Current_Step          INT, 
    Current_Retry_Attempt INT, 
    State                 INT 
  ) 
 
 
INSERT INTO 
  #enum_job EXEC master.dbo.xp_sqlagent_enum_jobs 1,  garbage 
 
 
SELECT 
  R.name , 
  R.last_run_date, 
  R.RunningForTime, 
  GETDATE()AS now 
FROM 
  #enum_job a 
INNER JOIN 
  ( 
    SELECT 
      j.name, 
      J.JOB_ID, 
      ja.run_requested_date AS last_run_date, 
      (DATEDIFF(mi,ja.run_requested_date,GETDATE())) AS RunningFor, 
      CASE LEN(CONVERT(VARCHAR(5),DATEDIFF(MI,JA.RUN_REQUESTED_DATE,GETDATE())/60)) 
        WHEN 1 THEN '0' + CONVERT(VARCHAR(5),DATEDIFF(mi,ja.run_requested_date,GETDATE())/60) 
        ELSE CONVERT(VARCHAR(5),DATEDIFF(mi,ja.run_requested_date,GETDATE())/60) 
      END  
      + ':' + 
      CASE LEN(CONVERT(VARCHAR(5),(DATEDIFF(MI,JA.RUN_REQUESTED_DATE,GETDATE())%60))) 
        WHEN 1 THEN '0'+CONVERT(VARCHAR(5),(DATEDIFF(mi,ja.run_requested_date,GETDATE())%60)) 
        ELSE CONVERT(VARCHAR(5),(DATEDIFF(mi,ja.run_requested_date,GETDATE())%60)) 
      END  
      + ':' + 
      CASE LEN(CONVERT(VARCHAR(5),(DATEDIFF(SS,JA.RUN_REQUESTED_DATE,GETDATE())%60))) 
        WHEN 1 THEN '0'+CONVERT(VARCHAR(5),(DATEDIFF(ss,ja.run_requested_date,GETDATE())%60)) 
        ELSE CONVERT(VARCHAR(5),(DATEDIFF(ss,ja.run_requested_date,GETDATE())%60)) 
      END AS RunningForTime 
    FROM 
      msdb.dbo.sysjobactivity AS ja 
    LEFT OUTER JOIN msdb.dbo.sysjobhistory AS jh 
    ON 
      ja.job_history_id = jh.instance_id 
    INNER JOIN msdb.dbo.sysjobs_view AS j 
    ON 
      ja.job_id = j.job_id 
    WHERE 
      ( 
        ja.session_id = 
        ( 
          SELECT 
            MAX(session_id) AS EXPR1 
          FROM 
            msdb.dbo.sysjobactivity 
        ) 
      ) 
  ) 
  R ON R.job_id = a.Job_Id 
AND a.Running   = 1 
DROP TABLE #enum_job 

Script OUTPUT


SQL Script to find Current Running Jobs and elapsed time sql server agent current running jobs sql server agent current executing jobs SQL Script to find Current Running Jobs

EOF - SQL Script to find Current Running Jobs and elapsed time, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

  1. On SQL Agent Server restat the data on the table msdb.dbo.sysjobactivity will be flushed and no data will be available.
    In such case this query will not return job run details, however the job history records are available based on the retention period.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.