sql find longest running job step

1/*============================================= 
2  Variables: 
3    @MinHistExecutions - Minimum number of job step executions we want to consider 
4    @MinAvgSecsDuration - Threshold for minimum job step duration we care to monitor 
5    @HistoryStartDate - Start date for historical average 
6    @HistoryEndDate - End date for historical average 
7  
8  These variables allow for us to control a couple of factors. First 
9  we can focus on job steps that are running long enough on average for 
10  us to be concerned with (say, 30 seconds or more). Second, we can 
11  avoid being alerted by job steps that have run so few times that the 
12  average and standard deviations are not quite stable yet. This script 
13  leaves these variables at 1.0, but I would advise you alter them 
14  upwards after testing. 
15  
16  Returns: One result set containing a list of job steps that 
17  are currently running and are running longer than two standard deviations 
18  away from their historical average. The "Min Threshold" column 
19  represents the average plus two standard deviations. 
20  
21  note [1] - comment this line and note [2] line if you want to report on all history for job steps 
22  note [2] - comment just this line is you want to report on running and non-running job steps 
23 =============================================*/ 
24  
25DECLARE   @HistoryStartDate datetime 
26  ,@HistoryEndDate datetime  
27  ,@MinHistExecutions int   
28  ,@MinAvgSecsDuration int  
29  
30SET @HistoryStartDate = '19000101' 
31SET @HistoryEndDate = GETDATE() 
32SET @MinHistExecutions = 1.0 
33SET @MinAvgSecsDuration = 1.0 
34  
35DECLARE @currently_running_jobs TABLE ( 
36    job_id UNIQUEIDENTIFIER NOT NULL 
37    ,last_run_date INT NOT NULL 
38    ,last_run_time INT NOT NULL 
39    ,next_run_date INT NOT NULL 
40    ,next_run_time INT NOT NULL 
41    ,next_run_schedule_id INT NOT NULL 
42    ,requested_to_run INT NOT NULL 
43    ,request_source INT NOT NULL 
44    ,request_source_id SYSNAME NULL 
45    ,running INT NOT NULL 
46    ,current_step INT NOT NULL 
47    ,current_retry_attempt INT NOT NULL 
48    ,job_state INT NOT NULL 
49    ) 
50  
51--capture details on jobs 
52INSERT INTO @currently_running_jobs 
53EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'' 
54  
55;WITH JobStepsHistData AS 
56( 
57  SELECT job_id, step_id 
58,date_executed=msdb.dbo.agent_datetime(run_date, run_time) 
59,secs_duration=run_duration/10000*3600 
60                      +run_duration%10000/100*60 
61                      +run_duration%100 
62  FROM msdb.dbo.sysjobhistory 
63  WHERE run_status = 1 -- Succeeded 
64) 
65,JobHistStats AS 
66( 
67  SELECT job_id, step_id 
68        ,AvgDuration = AVG(secs_duration*1.) 
69        ,AvgPlus2StDev = AVG(secs_duration*1.) + 2*stdevp(secs_duration) 
70  FROM JobStepsHistData 
71  WHERE date_executed >= DATEADD(day, DATEDIFF(day,'19000101',@HistoryStartDate),'19000101') 
72  AND date_executed < DATEADD(day, 1 + DATEDIFF(day,'19000101',@HistoryEndDate),'19000101')   
73  GROUP BY job_id, step_id 
74  HAVING COUNT(*) >= @MinHistExecutions 
75  AND AVG(secs_duration*1.) >= @MinAvgSecsDuration 
76) 
77-- need to select from the CTE's, and join to msdb for final result 
78SELECT jd.job_id 
79      ,j.name AS [JobName] 
80      ,sjs.step_id 
81    ,sjs.step_name 
82      ,MAX(act.start_execution_date) AS [ExecutionDate] 
83      ,AvgDuration AS [Historical Avg Duration (secs)] 
84      ,AvgPlus2StDev AS [Min Threshhold (secs)] 
85FROM JobStepsHistData jd 
86JOIN JobHistStats jhs on jd.job_id = jhs.job_id AND jd.step_id = jhs.step_id 
87JOIN msdb..sysjobs j on jd.job_id = j.job_id 
88JOIN msdb..sysjobsteps sjs on jd.job_id = sjs.job_id AND jd.step_id = sjs.step_id 
89JOIN @currently_running_jobs crj ON crj.job_id = jd.job_id --see note [1] above 
90JOIN msdb..sysjobactivity AS act ON act.job_id = jd.job_id 
91AND act.stop_execution_date IS NULL 
92AND act.start_execution_date IS NOT NULL 
93WHERE DATEDIFF(SS, act.start_execution_date, GETDATE()) > AvgPlus2StDev 
94AND crj.job_state = 1 --see note [2] above 
95GROUP BY jd.job_id, j.name, sjs.step_id, sjs.step_name, AvgDuration, AvgPlus2StDev