Keep an eye on your SQL Agent jobs using T-SQL!

CREATE PROCEDURE [dbo].[dbi_jobruntimeinfo]
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Parameters
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(

--Input
@SensitivityPercentage float = 0.3,
@SensivityTotalMinutes int = 20,
@granularity int = 5,
@JobId uniqueidentifier,

--Output
@AverageJobRunDuration float OUTPUT,
@RunDurationLastExecution float OUTPUT,
@Factor float OUTPUT,
@IncreasionPercentageString NVARCHAR(50) OUTPUT,
@IncreasionTotalMinutes int OUTPUT,
@OutputMessage NVARCHAR(max) OUTPUT,
@ThresholdExceeded BIT OUTPUT

)

AS

Begin

   SET NOCOUNT ON;

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Gather average runtime information
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
		declare @IncreasionPercentage float
       --@StepCount selects how many stps a specific Job has
       declare @StepCount Int
       Set @StepCount = (select count(*) from msdb.dbo.sysjobsteps where job_id = @JobId)

       --@RowCount dynamically sets how many rows of informations must be queried from job history --> its calculated between granularity and the amount of job steps for a specific job
       declare @RowCount Int
       Set @RowCount = @granularity * @StepCount

	   declare @TotalAvailableRows int
	   set @TotalAvailableRows = (
	   select count(*)
	   From msdb.dbo.sysjobs j 
	   INNER JOIN msdb.dbo.sysjobhistory h 
	   ON j.job_id = h.job_id 
	   where j.enabled = 1  --Only Enabled Job
	   and j.job_id = @JobId
	   and h.step_id != 0)

	   if @TotalAvailableRows <= @RowCount
		Begin 
			if @TotalAvailableRows <= @StepCount
				Begin
				Declare @MessageReturn NVARCHAR(max)
				Set @MessageReturn= 'Not enough History Data for Job with ID: ' +cast(@JobId as nvarchar(50))+ ' There is only: ' + cast((@TotalAvailableRows/@StepCount) as nvarchar (1)) + ' Runtime entrie in the history.'
				Print @MessageReturn
				set @AverageJobRunDuration = NULL
				set @RunDurationLastExecution = NULL
				set @Factor = NULL
				set @IncreasionPercentageString = NULL
				set @IncreasionTotalMinutes = NULL
				set @OutputMessage = @MessageReturn
				set @ThresholdExceeded = NULL
				Return
				End

			Set @RowCount = @TotalAvailableRows
			set @granularity = (@RowCount - @StepCount) / @StepCount
		End
			else
				Begin
					set @RowCount = @RowCount + @StepCount
				End
			print cast(@RowCount as NVARCHAR(max)) + ' Rowcount'
			print cast(@granularity as nvarchar(max)) + ' Granularity'

       --@SumRunDuration is for summing the total run Duration of all job executions defined in @granularity
       declare @SumRunDuration int

       Set @SumRunDuration = (Select SUM(RunDurationMinutes) from
       (select Top (@RowCount) j.name as 'JobName', h.step_id as 'StepID',
       msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
       ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60)
                     as 'RunDurationMinutes'
       From msdb.dbo.sysjobs j 
       INNER JOIN msdb.dbo.sysjobhistory h 
       ON j.job_id = h.job_id 
       where j.enabled = 1  --Only Enabled Job
       and j.job_id = @JobId
       and h.step_id != 0
       Order by run_date desc, run_time desc) SubSelect)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Gather last runtime information
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

       declare @RowCountLastExecution int
       set @RowCountLastExecution = 1 * @StepCount

       set @RunDurationLastExecution = (Select SUM(RunDurationMinutes) from
       (select Top (@RowCountLastExecution) j.name as 'JobName', h.step_id as 'StepID',
       msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
       ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60)
                     as 'RunDurationMinutes'
       From msdb.dbo.sysjobs j 
       INNER JOIN msdb.dbo.sysjobhistory h 
       ON j.job_id = h.job_id 
       where j.enabled = 1  --Only Enabled Job
       and j.job_id = @JobId
       and h.step_id != 0
       Order by run_date desc, run_time desc) SubSelect)

       --@AverageJobRunDuration calculates the avarage runtime for a single job execution. Calculateion is made through @SumRunDuration and the @granularity
       set @AverageJobRunDuration = (@SumRunDuration - @RunDurationLastExecution) / @granularity
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Calculate Factor
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       
       Begin try
              set @Factor = (1/@AverageJobRunDuration)*@RunDurationLastExecution
       End try
       Begin catch
              set @Factor = NULL
       End catch

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Check if sensitivity threshold is exceeded
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

       if @Factor != 0 and @Factor !< 0 and @Factor Is Not NULL
       Begin
              set @IncreasionPercentage = @Factor -1
              set @IncreasionTotalMinutes = @RunDurationLastExecution - @AverageJobRunDuration

				if @IncreasionPercentage > 0
				Begin
					set @IncreasionPercentageString = '+' + convert(nvarchar(max), @IncreasionPercentage*100) + '%'
				End
					Else
					Begin
					set @IncreasionPercentageString = convert(nvarchar(max), @IncreasionPercentage*100) + '%'
					End

       End 
              Else 
              Begin
              set @IncreasionPercentage = NULL
			  set @IncreasionPercentageString = NULL
              set @IncreasionTotalMinutes = NULL
              End

       If @IncreasionPercentage Is Not Null and @IncreasionTotalMinutes is not null and @IncreasionPercentage >= @SensitivityPercentage and @IncreasionTotalMinutes >= @SensivityTotalMinutes
       Begin
              Set @OutputMessage = 'Job with ID: ' + cast(@JobId as nvarchar(200)) + ' has exceed the defined thresholds. ' + 'The duration has increased by factor: ' +  cast(@Factor as nvarchar(200)) + ' Which is in total ' + cast(@IncreasionTotalMinutes as nvarchar(200)) + ' minutes.'
              Set @ThresholdExceeded = 1
       End
       Else
              Begin
              Set @OutputMessage = 'Job with ID: ' + cast(@JobId as nvarchar(200)) + ' has not exceeded the defined threshold'
              Set @ThresholdExceeded = 0
              End

End
GO

declare @Jobs NVARCHAR(max)
Set @Jobs = ''

Declare @Job_Id uniqueidentifier
Declare @JobName Nvarchar(max)

--Load Jobs i cursor
declare jobs_cursor cursor
       for select value from STRING_SPLIT(@Jobs, ';')

open jobs_cursor
fetch next from jobs_cursor into @JobName

While @@FETCH_STATUS = 0
Begin

       --set JobId from Jobname
       set @Job_Id = (select j.job_id from msdb.dbo.sysjobs j where j.name = @JobName)

       --Exec SP
       declare @AverageJobRunDurationOut float
       declare @RunDurationLastExecutionOut int
       declare @FactorOut float
	   declare @IncreasionPercentageStringOut NVARCHAR(50)
       declare @IncreasionTotalMinutesOut int
       declare @OutputMessageOut NVARCHAR(max)
       declare @ThresholdExceededOut BIT

       exec dbo.dbi_jobruntimeinfo @SensivityTotalMinutes = 1,
       @JobId = @Job_Id,
       @AverageJobRunDuration = @AverageJobRunDurationOut Output,
       @RunDurationLastExecution = @RunDurationLastExecutionOut Output,
       @Factor = @FactorOut Output,
       @IncreasionTotalMinutes = @IncreasionTotalMinutesOut Output,
       @OutputMessage = @OutputMessageOut Output,
       @ThresholdExceeded = @ThresholdExceededOut Output,
	   @IncreasionPercentageString = @IncreasionPercentageStringOut Output;

       Select 
	   @JobName as 'Job',
	   @AverageJobRunDurationOut as 'AverageDuration',
	   @RunDurationLastExecutionOut as 'DurationLastExecution',
	   @IncreasionPercentageStringOut as 'IncreasionInPercent',
	   @IncreasionTotalMinutesOut as 'IncreasionMinutes',
       @ThresholdExceededOut as 'Threshold exceeded',
	   @FactorOut as 'Factor',
	   @OutputMessageOut as 'Message'

	   

	   if @ThresholdExceededOut = 1
	   Begin

		declare @bodycontent nvarchar(max)
		set @bodycontent = 'Job Name = '+@JobName+'; Average duration in minutes = '+cast(@AverageJobRunDurationOut as nvarchar(max))+'; Duration of last execution in minutes = '
		+cast(@RunDurationLastExecutionOut as nvarchar(max))+'; Slow down in percent = '+
		@IncreasionPercentageStringOut+'; Slow down in minutes = '+cast(@IncreasionTotalMinutesOut as nvarchar(max))
		
		declare @subjectcontent nvarchar(100)
		set @subjectcontent = 'SQL Agent Job: '+@JobName+' has slowed down'

		exec msdb.dbo.sp_send_dbmail
			@profile_name = '',
			@recipients  = '',
			@subject = @subjectcontent,
			@body = @bodycontent
       End

       fetch next from jobs_cursor into @JobName

End
CLOSE jobs_cursor;
DEALLOCATE jobs_cursor;