April 19, 2020 at 6:35 pm
Hi,
I am trying to send notifications to myself when a articular job that longer than 8 minutes to execute. For that , I have created JOB 2 ( Report Issue Job ) as shown below as a SQL AGENT JOB to send notification. Database configuration was setup and was also able to send myself test email .
Report Issue Job runs every 30 minutes (5,5:30,6,6:30,..)
PROD JOB runs every 2 hours (5:21,7:21,9:21,...)
Both jobs are executing successfully and job 2 is always over 8 minutes but I haven't received any single notification so far. Please help.
USE [msdb]
IF (
SELECT DATEDIFF(SECOND, aj.start_execution_date, GETDATE())
FROM msdb..sysjobactivity aj
INNER JOIN msdb..sysjobs sj ON sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- condition: job hasn't stopped running
AND aj.start_execution_date IS NOT NULL AND-- condition: job is currently running
sj.name = 'PROD SQL JOB' -- TODO: fill in the exact job name to monitor
AND NOT EXISTS (
SELECT 1
FROM msdb.dbo.sysjobactivity new
WHERE new.job_id = aj.job_id
AND new.start_execution_date > aj.start_execution_date
) -- TODO: change 3600 below to desired treshold value in seconds
) >= 300--1200
EXEC dbo.sp_notify_operator @profile_name = N'MailAlert'
,-- TODO
@name = N'abc@xyz.com'
,-- TODO
@subject = N'Warning: Database refresh job overdue'
,-- TODO
@body = N'WARNING! Daily job to refresh data from PWA on xyz server took longer than expected!. ';-- TODO
ELSE
PRINT 'Job not running or not (yet) exceeding treshold';
GO
April 19, 2020 at 8:53 pm
With >13,000 points, you should know how to format code snippets for presentation to others.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 20, 2020 at 10:44 am
What happens if you run the SELECT statement on its own? What number does it return? What happens if you run from EXEC to the end? Does it send an e-mail?
John
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply