Running Jobs

Which SQL Agent jobs are running right now? This query joins sysjobs_view to sysjobactivity and returns every job that has a requested start but no stop date - with the execution time, elapsed minutes, and the job's most recent execution for context. The final WHERE clause keeps only rows where the current run is actually the latest one.

SELECT
	*
FROM
	(
		SELECT
			Job.job_id AS JobId,
			Job.name AS JobName,
			Job.originating_server AS ServerName, 
			Activity.start_execution_date AS ExecutionDate,
			DateDiff([Minute], Activity.start_execution_date, GetDate()) as ElapsedMinutes,
			(
				SELECT
					MAX(start_execution_date)
				FROM
					msdb.dbo.sysjobactivity as [Last]
				WHERE
					[Last].job_id = Job.job_id
			) AS LastExecutionDate
		FROM
			msdb.dbo.sysjobs_view AS Job
		INNER JOIN msdb.dbo.sysjobactivity AS Activity
			ON Job.job_id = Activity.job_id
		WHERE
			Activity.run_requested_date IS NOT NULL
			AND Activity.stop_execution_date IS NULL
	) AS History
WHERE
	History.ExecutionDate == LastExecutionDate