CODEWITHSUNDEEP
sql-servermonitoringsmo
João Mendes
João Mendes

Reputation: 1425

SQL Server Agent job elapsed time

Is there any way to use SMO to find the elapsed time for a SQL Server job that's currently executing?

Upvotes: 0

Views: 730

Answers (2)

womp
womp

Reputation: 116977

I would think you could use the LastRunDate propery of the Job object (in the Jobs collection of the JobServer) once you've determined that the execution status is currently executing.

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.job.lastrundate.aspx

Grab the property value and do a date diff on it with the current time?

Upvotes: 0

Bruce Dunwiddie
Bruce Dunwiddie

Reputation: 2908

Sorry, this is a query and not SMO. I'm assuming this will also meet your requirements, even if you have to run it from SMO.

https://gallery.technet.microsoft.com/scriptcenter/Running-SQL-Server-Jobs-953caf82

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

Upvotes: 1

Related Questions