Hi Guys,
It is very important to monitor the long running SQL jobs in production environments and it is cannot miss task of every SQL DBA.
I use below query to catch the long running jobs.
SQL job is created with this query and scheduled to run every 3hrs. which sends notifications of jobs which are running from more than 30mins. You can make it how ever is required to your environment.
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_LongRunningJobNotification]
@Minutes int
AS
BEGIN
set nocount on
declare @ThresholdMinutes int
select @ThresholdMinutes = case when isnull(@Minutes,0) < 1
then 1
when isnull(@Minutes,0) > 120
then 120
else @Minutes
end
if (
select max(datediff(ss,ja.start_execution_date,getdate()))
from msdb.dbo.sysjobactivity ja,msdb.dbo.sysjobs jm
where 1=1
and datediff(ss,ja.start_execution_date,getdate()) > @ThresholdMinutes*60
and jm.job_id = ja.job_id
and jm.category_id = 0
and jm.enabled = 1
and jm.name != 'BlockingHistoryCapture'
and ja.stop_execution_date is null
and ja.start_execution_date is not null
and not exists( select 'x'
from msdb.dbo.sysjobactivity new
where new.job_id = ja.job_id
and new.start_execution_date > ja.start_execution_date)
) > @ThresholdMinutes*60
begin -- If long running job
create table ##LongRunningJobsTemp
( jobname nvarchar(128) not null,
ExecutionDate varchar(19),
MinutesExecuting varchar(8)
)
insert
into ##LongRunningJobsTemp
select rtrim(substring(jm.name,1,64)) Jobname,
convert(varchar(10),ja.start_execution_date,101)+' '+convert(varchar(8),ja.start_execution_date,114) ExecutionDate,
convert(varchar(8),getdate()-ja.start_execution_date,114) MinutesExecuting
from msdb.dbo.sysjobactivity ja,msdb.dbo.sysjobs jm
where 1=1
and datediff(ss,ja.start_execution_date,getdate()) > @ThresholdMinutes*60
and jm.job_id = ja.job_id
and jm.category_id = 0
and jm.enabled = 1
and jm.name != 'BlockingHistoryCapture'
and ja.stop_execution_date is null
and ja.start_execution_date is not null
and not exists( select 'x'
from msdb.dbo.sysjobactivity new
where new.job_id = ja.job_id
and new.start_execution_date > ja.start_execution_date)
order by jm.name
set nocount off
if (select count(*) from ##LongRunningJobsTemp) > 0
begin -- If temp table has
any rows
DECLARE @tableHTML
NVARCHAR(MAX) ;
SET @tableHTML = '<html><head>' +
'<style>' +
'H1 {margin-left:100px;padding-left:7px;font-size:18pt;font-family:calibre;color:Black}' +
'th
{font-family:calibre;background-color:#DB7093;border: solid black
1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:12pt;}
' +
'td
{font-family:calibre;background-color:#DB7093;border: solid black
1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:12pt;}
' +
'</style>' +
'</head>' +
'<Body>'
+
N'<H1>Long
Running Job Alert!</H1>' +
N'<table
border="1">' +
N'<th>JobName</th>' +
N'<th>ExecutionDate</th>' +
N'<th>MinutesExecuting</th>' +
CAST ( ( SELECT td = lr.Jobname, '',
td
= Lr.ExecutionDate, '',
td = Lr.MinutesExecuting, ''
FROM ##LongRunningJobsTemp
as lr
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
declare @email_address varchar(100)
select @email_address =email_address from dbo.sysoperators where name='LongRunningJob_Notification'
exec msdb.dbo.sp_send_dbmail
@profile_name = 'JobMail',
@subject='ServerName: Long
Running Job Alert!',
@recipients= @email_address,
@body=@tableHTML,
@body_format='HTML'
drop table ##LongRunningJobsTemp
end -- If long running job
end
END
Note: Make sure you create your own operator and use it.
