This Article will guide to setup email alert for
How to send alert for long running SQL job? or Email Alert for SQL Jobs running for longer time
Sometime SQL jobs runs more than its normal time and caused performance issues on SQL Server. This type of issue doesn’t occur frequently but wherever that jobs run longer time it caused performance problems to other SQL jobs and queries.
So, if we can schedule email alert for such long running SQL Job if it runs more than certain duration. Using database email feature, we can send an email as alert.
Below query can help to send an email alert if job runs more than certain limit. In query mentioned duration is 10 minutes but you can edit the time duration if required.
Also, you can edit SQL job name in query for required job alert.
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @servername SYSNAME = (
SELECT @@SERVERNAME
)
DECLARE @sub NVARCHAR(MAX) = 'Long running job(s) detected on : ' + @servername
SELECT jobs.name AS [Job_Name]
,CONVERT(VARCHAR(30), ja.start_execution_date, 121) AS [Start_execution_date]
--, ja.stop_execution_date
,ca1.Duration_min
INTO #temp
FROM msdb.dbo.sysjobs jobs
LEFT JOIN (
SELECT *
FROM msdb.dbo.sysjobactivity
WHERE session_id = (
SELECT MAX(session_id)
FROM msdb.dbo.syssessions
)
AND start_execution_date IS NOT NULL
AND stop_execution_date IS NULL
) AS ja ON ja.job_id = jobs.job_id
CROSS APPLY (
SELECT DATEDIFF(mm, ja.start_execution_date, GETDATE()) AS [Duration_min]
) AS ca1
WHERE ca1.Duration_min > 10
IF EXISTS (
SELECT *
FROM #temp
)
BEGIN
SET @xml = CAST((
SELECT Job_Name AS 'td'
,''
,Start_execution_date AS 'td'
,''
,Duration_min AS 'td'
,''
FROM #Temp
FOR XML PATH('tr')
,ELEMENTS
) AS NVARCHAR(MAX))
SET @body = '<style>
TH, TD, Body{font-family: Arial;font-size:10pt;}
TH{Color: White}</style>
<html><body>
<table border = 1>
<tr>
<TH bgcolor= #0000a0> Job_Name </TH>
<TH bgcolor= #0000a0> Start_execution_date </TH>
<TH bgcolor= #0000a0> Duration_min</tr>'
SET @body = @body + @xml + '</table></body></html>'
EXEC msdb..sp_send_dbmail @recipients = 'emailID'
,@profile_name = 'DBemailprofile'
,@body = @body
,@subject = @sub
,@body_format = 'HTML'
select @body
END
drop table #temp