query to show currently running sql agent jobs

Question

Hi All,

Using Tsql query I want to know currently running sql agent jobs and how long they have been running in [dd hh:mi:ss]?

Expected output:

job name , start time, duration [dd hh:mi:ss]

I can see it in Job activity monitor for currently running jobs but it is not telling me when it started and how long it has been running.

Regards,

Sam

Answer 1

Hi @Samantha r

Please check this query:

SELECT j.name AS job_name, 
       ja.start_execution_date AS StartTime,
	   COALESCE(CONVERT(VARCHAR(5),ABS(DATEDIFF(DAY,(GETDATE()-ja.start_execution_date),'1900-01-01'))) + ' '
               +CONVERT(VARCHAR(10),(GETDATE()-ja.start_execution_date),108),'00 00:00:00') AS [Duration] 
FROM msdb.dbo.sysjobactivity ja 
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY session_id DESC)
  AND start_execution_date is not null
  AND stop_execution_date is null;

Best regards,

Cosmog Hong

Answer 2

SELECT 
    j.name AS JobName,
    ja.start_execution_date AS StartTime,
    CONVERT(varchar, DATEADD(ms, DATEDIFF(ms, ja.start_execution_date, GETDATE()), 0), 20) AS Duration
FROM 
    msdb.dbo.sysjobactivity ja
JOIN 
    msdb.dbo.sysjobs j ON ja.job_id = j.job_id
JOIN 
    (SELECT 
        job_id, MAX(start_execution_date) AS MaxStart
     FROM 
        msdb.dbo.sysjobactivity
     GROUP BY 
        job_id) MaxDates ON ja.job_id = MaxDates.job_id AND ja.start_execution_date = MaxDates.MaxStart
WHERE 
    ja.stop_execution_date IS NULL
    AND ja.start_execution_date IS NOT NULL
ORDER BY 
    StartTime;