Snippet Name: runjobs

Tags: Jobs Information

Created Date: Oct 1 2021 8:05AM
Last Modified Date: Oct 1 2021 8:05AM

–desc: list of running jobs

Dependencies: none-standalone code

default description

Sample Output:

 
 --desc: list of running jobs
 
 SELECT
 
     ElapsedTime = dt.days + ':' + dt.Hours + ':' + dt.Minutes + ':' + dt.Seconds,
 
     ja.job_id,
 
     j.name AS job_name,
 
     ja.start_execution_date,      
 
     ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
 
     Js.step_name
 
 FROM msdb.dbo.sysjobactivity ja 
 
 LEFT JOIN msdb.dbo.sysjobhistory jh 
 
     ON ja.job_history_id = jh.instance_id
 
 INNER JOIN msdb.dbo.sysjobs j 
 
 ON ja.job_id = j.job_id
 
 INNER JOIN msdb.dbo.sysjobsteps js
 
     ON ja.job_id = js.job_id
 
     AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
 
     CROSS APPLY(SELECT [Days]          = CONVERT(VARCHAR(128),datepart(day,GETDATE() - start_execution_date)-1),
 
                        [Hours]         = CONVERT(VARCHAR(128),DATEPART(Hour,GETDATE() - start_execution_date)),
 
                        [Minutes]       = RIGHT('000' + CONVERT(VARCHAR(128),datepart(Minute,GETDATE() - start_execution_date)),2),
 
                        [Seconds]       = RIGHT('000' +CONVERT(VARCHAR(128),datepart(Second,GETDATE() - start_execution_date)),2)
 
                ) dt
 
 WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
 
 AND start_execution_date is not null
 
 AND stop_execution_date is null;
 
 {C}