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
     ElapsedTime = dt.days + ':' + dt.Hours + ':' + dt.Minutes + ':' + dt.Seconds,
     ja.job_id, AS job_name,
     ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
 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;
Previous Article
Next Article

Leave a Reply