Monitor and Alert for the Long Running Agent Jobs on SQL Server

Stored Procedure that runs at every 1 Hr to identify the Long running jobs:

Use the DBA Specific database to create this stored procedure on your SQL Server where you are interested in finding the long running jobs on.   

Usp_LongRunningJobAlert:

USE [DBA_Specific_DB] -- Our DBA Specific DB for DBA related stuff
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--*********************************************************************************************
--Author: SrikanthReddy Anugu
--Create Date: 
--Description: Indentifies and alerts the DBA Team for Long Running jobs which running longer than
--             @thresholdduration (We have to choose a value for @thresholdduration)
--    @thresholdduration is in minutes.
--    @thresholdduration is the time in minutes we use to decide whether a job long running or not
--    @thresholdduration -- we need to pass it when we run this procedure using a job
--*********************************************************************************************
CREATE PROCEDURE [dbo].[usp_LongRunningJobAlert] @thresholdduration INT
AS
-- Declaring the Temporary table to store all job details
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
    ,request_source INT NOT NULL
    ,request_source_id SYSNAME NULL
    ,running INT NOT NULL
    ,current_step INT NOT NULL
    ,current_retry_attempt INT NOT NULL
    ,job_state INT NOT NULL
    ) 
 
--loading\capturing details on jobs in to temporary table
INSERT INTO @currently_running_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''
 
DECLARE @CurentRJob UNIQUEIDENTIFIER -- This variable is to read one job at a time from CurrentRunJobss and Process the IF stmt below
DECLARE CurrentRunJobs CURSOR FOR  
SELECT job_id FROM @currently_running_jobs
WHERE running = '1';  
OPEN CurrentRunJobs;  
FETCH NEXT FROM CurrentRunJobs INTO @CurentRJob  
WHILE @@FETCH_STATUS = 0  
BEGIN
  IF (SELECT DATEDIFF(MINUTE,MAX(run_requested_date),GETDATE()) FROM msdb..sysjobactivity WHERE  job_id=@CurentRJob) > @thresholdduration
  BEGIN
  DECLARE @TimeSpent INT
  DECLARE @JobName NVARCHAR(400)
  DECLARE @ServerName NVARCHAR(20)
  DECLARE @Mail_Subject NVARCHAR(400)
  DECLARE @Mail_Body NVARCHAR(400)
  SET @TimeSpent = (SELECT DATEDIFF(MINUTE,MAX(run_requested_date),GETDATE()) FROM msdb..sysjobactivity WHERE  job_id=@CurentRJob)
  SET @ServerName = (SELECT @@SERVERNAME)
  SET @JobName = (SELECT name FROM msdb..sysjobs WHERE job_id=@CurentRJob)
  SET @Mail_Subject= 'Long Running Job on ' + @ServerName
  SET @Mail_Body= 'The Job '+ @JobName + ' is running for more than '+ CAST(@thresholdduration AS NVARCHAR(100)) +' minutes. Time spent on current run is ' + CAST(@TimeSpent AS NVARCHAR(100)) +' Minutes.'
  EXEC msdb..sp_send_dbmail  
    @profile_name = 'DBA_Profile', -- Database mail profile  
   @recipients = 'DBA_Inbox@YourDomain.com',   
    @body = @Mail_Body,  
 @subject =  @Mail_Subject; 
  END
  FETCH NEXT FROM CurrentRunJobs INTO @CurentRJob 
  END
CLOSE CurrentRunJobs;  
DEALLOCATE CurrentRunJobs;  

GO

Job - [Long Running Jobs - Catch and Alert]: 

Create a job with below script to run the above created stored procedure at every one hour depending on your requirement.   

USE [DBA_Specific_DB]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[usp_LongRunningJobAlert]
  @thresholdduration = 360 -- Here @thresholdduration set to 360 minutes (6 hrs)
SELECT 'Return Value' = @return_value
GO

Hope it helps, please comment below if you have any questions.