Snippet Name: envrunning
Tags: helpful,scriptgeneration
Created Date: Oct 1 2021 8:05AM
Last Modified Date: Oct 1 2021 8:05AM
–desc: all currently running jobs
Dependencies: none-standalone code
default description
Sample Output:
{C}
--desc: all currently 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] = RIGHT('000' + CONVERT(VARCHAR(128),datepart(day,GETDATE() - start_execution_date)-1),2),
[Hours] = RIGHT('000' + CONVERT(VARCHAR(128),datepart(Hour,GETDATE() - start_execution_date) ),2),
[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;
execute msdb.dbo.sp_help_job @execution_status= 1