I have been using this which I found a few years ago, and modified a bit.
/****** Object: StoredProcedure [dbo].[usp_LongRunningJobs_XML] Script Date: 11/08/2015 13:20:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* =============================================
-- Author: Devin Knight and Jorge Segarra
-- Create date: 7/6/2012
-- Description: Monitors currently running SQL Agent jobs and
-- alerts admins if runtime passes set threshold
-- Updates: 7/11/2012 Changed Method for capturing currently running jobs to use master.dbo.xp_sqlagent_enum_jobs 1, ''
--
Me 6-28-2013 Remove unneeded data in email. was 'SELECT RJ.*'
and format as XML
-- =============================================*/
CREATE PROCEDURE [dbo].[usp_LongRunningJobs_XML]
AS
--Set Mail Profile
DECLARE @MailProfile VARCHAR(50)
DECLARE @tableHTML NVARCHAR(MAX)
declare @BodyText varchar(150)
SET @MailProfile = (
SELECT @@SERVERNAME
) --Replace with your mail profile name
SET @MailProfile = 'DBA_Alerts'
set @BodyText = ' ' -- Should not be NULL
--Set Email Recipients
DECLARE @MailRecipients VARCHAR(50)
SET @MailRecipients = 'MyEmail@Email.com'
--Set limit in minutes (applies to all jobs)
--NOTE: Percentage limit is applied to all jobs where average runtime greater than 5 minutes
--else the time limit is simply average + 10 minutes
DECLARE @JobLimitPercentage FLOAT
SET @JobLimitPercentage = 100 --Use whole percentages greater than 100
-- Create intermediate work tables for currently running jobs
DECLARE @currently_running_jobs TABLE (
job_id UNIQUEIDENTIFIER NOT NULL
,last_run_date INT NOT NULL
,last_run_time INT NOT NULL
,next_run_date INT NOT NULL
,next_run_time INT NOT NULL
,next_run_schedule_id INT NOT NULL
,requested_to_run INT NOT NULL
,-- BOOL
request_source INT NOT NULL
,request_source_id SYSNAME COLLATE database_default NULL
,running INT NOT NULL
,-- BOOL
current_step INT NOT NULL
,current_retry_attempt INT NOT NULL
,job_state INT NOT NULL
) -- 0 = Not idle or suspended, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle, 5 = Suspended, [6 = WaitingForStepToFinish], 7 = PerformingCompletionActions
--Capture Jobs currently working
INSERT INTO @currently_running_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''
-- exec sp_whoisactive
--select * from @currently_running_jobs
--Temp table exists check
IF OBJECT_ID('tempdb..##RunningJobs') IS NOT NULL
DROP TABLE ##RunningJobs
CREATE TABLE ##RunningJobs (
[JobID] [UNIQUEIDENTIFIER] NOT NULL
,[JobName] [sysname] NOT NULL
,[StartExecutionDate] [DATETIME] NOT NULL
,[AvgDurationMin] [INT] NULL
,[DurationLimit] [INT] NULL
,[CurrentDuration] [INT] NULL )
-- truncate table ##RunningJobs
INSERT INTO ##RunningJobs (
JobID
,JobName
,StartExecutionDate
,AvgDurationMin
,DurationLimit
,CurrentDuration )
SELECT jobs.Job_ID AS JobID
,jobs.NAME AS JobName
,act.start_execution_date AS StartExecutionDate
,AVG(FLOOR(run_duration / 100)) AS AvgDurationMin
,CASE
--If job average less than 5 minutes then limit is avg+10 minutes
WHEN AVG(FLOOR(run_duration / 100)) <= 5
THEN (AVG(FLOOR(run_duration / 100))) + 10
--If job average greater than 5 minutes then limit is avg*limit percentage
ELSE (AVG(FLOOR(run_duration / 100)) * (@JobLimitPercentage / 100))
END AS DurationLimit
,DATEDIFF(MI, act.start_execution_date, GETDATE()) AS [CurrentDuration]
FROM @currently_running_jobs crj
INNER JOIN msdb..sysjobs AS jobs ON crj.job_id = jobs.job_id
INNER JOIN msdb..sysjobactivity AS act ON act.job_id = crj.job_id
AND act.stop_execution_date IS NULL
AND act.start_execution_date IS NOT NULL
AND act.start_execution_date > (GETDATE() - 4)
INNER JOIN msdb..sysjobhistory AS hist ON hist.job_id = crj.job_id
AND hist.step_id = 0
WHERE crj.job_state = 1 and crj.running = 1
GROUP BY jobs.job_ID
,jobs.NAME
,act.start_execution_date
,DATEDIFF(MI, act.start_execution_date, GETDATE())
HAVING CASE
WHEN AVG(FLOOR(run_duration / 100)) <= 5
THEN (AVG(FLOOR(run_duration / 100))) + 10
WHEN jobs.NAME = 'Google Analytics Download' and DATEDIFF(MI, act.start_execution_date, GETDATE()) > 240 then 240
ELSE (AVG(FLOOR(run_duration / 100)) * (@JobLimitPercentage / 100))
END < DATEDIFF(MI, act.start_execution_date, GETDATE())
--Checks to see if a long running job has already been identified so you are not alerted multiple times,
-- as long as it is the same day. If it was already reported, but is still running the next day, then report it again.
IF EXISTS ( SELECT RJ.*
FROM ##RunningJobs RJ
WHERE CHECKSUM(RJ.JobID, RJ.StartExecutionDate) NOT IN (
SELECT CHECKSUM(JobID, StartExecutionDate)
FROM dbo.LongRunningJobs )
or convert(varchar(8),RJ.StartExecutionDate,112) <= convert(varchar(8),getdate()-1,112) )
BEGIN
--Send email with results of long-running jobs
-- select * from LongRunningJobs order by RowInsertDate desc
SET @tableHTML = @BodyText +
N'<H1>Long Running SQL Jobs</H1>' +
N'<table border="1">' +
N'<tr><th>Job Name</th><th>Start_Time</th>' +
N'<th>Avg_Min</th><th>Curr_Min</th></tr>' +
--N'<th>DL_Table</th><th>Records</th></tr>' +
CAST ( ( select td = substring(RJ.JobName,1,35) ,' ',
td = cast(RJ.StartExecutionDate as char(23)), ' ',
td = cast(RJ.AvgDurationMin as char(7)) , ' ' ,
td = cast(RJ.CurrentDuration as char(7)), ' '
FROM ##RunningJobs RJ
WHERE CHECKSUM(RJ.JobID,RJ.StartExecutionDate)
NOT IN (Select CHECKSUM(JobID,StartExecutionDate)
From dbo.LongRunningJobs)
or convert(varchar(8),RJ.StartExecutionDate,112) <= convert(varchar(8),getdate()-1,112)
--order by j.name, run_time desc, step_id
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBA_Alerts',
@recipients= 'MyEmail@Email.com',
@subject = 'Long Running SQL Job XML: Servername',
@body = @tableHTML,
@body_format = 'HTML',
@importance = 'High'
/* --============= OLD
EXEC msdb.dbo.sp_send_dbmail @profile_name = @MailProfile
,@recipients = @MailRecipients
,@query = 'USE DataMaint; Select substring(RJ.JobName,1,35),
RJ.StartExecutionDate as ''start_Time'', RJ.AvgDurationMin as ''Avg_Min'',
RJ.DurationLimit as ''Limit'' , RJ.CurrentDuration as ''Curr_Min''
From ##RunningJobs RJ
WHERE CHECKSUM(RJ.JobID,RJ.StartExecutionDate) NOT IN (Select CHECKSUM(JobID,StartExecutionDate) From dbo.LongRunningJobs) '
,@body = 'View attachment to view long running jobs'
,@subject = 'Long Running SQL Job: ClientviewDB'
,@attach_query_result_as_file = 1;
-- =========== end OLD */
--Populate LongRunningJobs table with jobs exceeding established limits
INSERT INTO DataMaint.[dbo].[LongRunningJobs]
( [JobID]
,[JobName]
,[StartExecutionDate]
,[AvgDurationMin]
,[DurationLimit]
,[CurrentDuration], [RowInsertDate],Servername )
( SELECT RJ.*, GETDATE(), 'ServerName' FROM ##RunningJobs RJ
WHERE CHECKSUM(RJ.JobID, RJ.StartExecutionDate)
NOT IN (SELECT CHECKSUM(JobID, StartExecutionDate)
FROM dbo.LongRunningJobs ) )
-- select top 100 * from DataMaint.[dbo].[LongRunningJobs]
END
DROP TABLE ##RunningJobs