USE master
GO
IF OBJECT_ID('sp_RunningJobs') IS NULL EXEC ('CREATE PROCEDURE sp_RunningJobs AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_RunningJobs
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT name AS 'JobName',
start_execution_date AS 'StartTime',
--AvgRuntimeOnSucceed,
DATEDIFF(s, start_execution_date, GETDATE()) AS 'CurrentRunTime',
ja.last_executed_step_id,
js.step_id,
js.step_name,
js.subsystem,
j.job_id AS 'JobId'
FROM msdb.dbo.sysjobactivity ja (NOLOCK)
INNER JOIN msdb.dbo.sysjobs j (NOLOCK) ON ja.job_id = j.job_id
--CROSS APPLY (SELECT CAST(AVG((run_duration / 10000 * 3600) + ((run_duration % 10000) / 100 * 60) + (run_duration % 10000) % 100) + STDEV((run_duration / 10000
-- * 3600) + ((run_duration % 10000) / 100 * 60) + (run_duration % 10000) % 100) AS INT) AS 'AvgRuntimeOnSucceed'
-- FROM msdb.dbo.sysjobhistory h (NOLOCK)
-- WHERE step_id = 0
-- AND run_status = 1
-- AND h.job_id = j.job_id) k
LEFT OUTER JOIN msdb.dbo.sysjobsteps js ON js.job_id = ja.job_id
AND js.step_id = ISNULL(ja.last_executed_step_id, 1)
WHERE stop_execution_date IS NULL
AND start_execution_date > (SELECT login_time
FROM master.dbo.sysprocesses(NOLOCK)
WHERE program_name = N'SQLAgent - Generic Refresher')
ORDER BY start_execution_date DESC
GO
EXEC sys.sp_ms_marksystemobject sp_RunningJobs
GO