August 11, 2020 at 1:26 pm
Hi
I have a job that I need to be notified/emailed if it runs longer than 30 minutes.
What is the best way to do that?
August 11, 2020 at 2:27 pm
Not sure if it is the best way, but I just set up a second job that starts at the same time as the one I wanted to monitor and it does a wait for 30 minutes and after the 30 minute window, it checks to see if the monitored job is still running. if so, send an email, otherwise complete and run again the next time the first job is being run.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 11, 2020 at 2:50 pm
Have the job you want to monitor immediately start a second job that will notify you if the run time is exceeded. You could even have the main job pass a different test time interval (if for whatever reason you want something other than 30 mins for that run) and/or a different list of people notify if the job runs too long (someone's on vacation, etc.) to the second job if you ever needed to.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 11, 2020 at 5:23 pm
Okay gotcha..
This is my work in progress....I was hoping to create the below in the step before the job starts. But it isn't working...
Declare @time as int
SELECT @time=DATEDIFF(MINUTE,aj.start_execution_date,GetDate())
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.stop_execution_date IS NULL -- job hasn't stopped running
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND sj.name = 'Automated Claim Closing'
and not exists( -- make sure this is the most recent run
select 1
from msdb..sysjobactivity new
where new.job_id = aj.job_id
and new.start_execution_date > aj.start_execution_date
)
If @time>1 BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = '123@a.com',
@body = 'The Automated Job is running long ',
@subject = 'The Automated Job is running long ' ;
END
August 11, 2020 at 5:38 pm
If you are going with Scott's approach, step 1 of the job would be to start the job you want to monitor. Next, you wait for some period of time (30 minutes in your case), and then you look to see if the job is running.
Using what you had BEFORE the job starts will give you NULL as @time which will return FALSE for your IF statement and thus do nothing as the job is not running.
My steps are similar to Scott's listed above of:
1 - start the job you want to monitor
2 - wait for your self-set timeout
3 - send alert if job is still running
Scott's approach is a lot more modular having a stored procedure handle steps 2 and 3 and allows for code reuse, so I do like their approach better than mine. My approach ended up having 2 jobs that do work and 2 jobs that watch for the specific long running job. I also like Scott's approach of having a the monitor job start up the work job. my approach breaks if someone changes a schedule.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 11, 2020 at 5:49 pm
Oh I see....now. Thanks! Yeah I like it also.
August 11, 2020 at 9:25 pm
Not exactly.
The first step in the main job is to start another job that will check on the first job, like this:
EXEC msdb..sp_start_job 'Automated Claim Closing Monitor'
The main job then goes on claim closing or whatever processing it needs to do.
The first step of the monitor job is to WAIT DELAY for whatever time is needed, then check if the job is still executing, something like this:
--Step_1
WAITFOR DELAY '00:30:00' --step_1
--Step_2
DECLARE @body nvarchar(max)
DECLARE @job_name nvarchar(128)
DECLARE @job_exec_mins int
DECLARE @recipients nvarchar(max)
DECLARE @subject nvarchar(255)
SET @job_name = 'Automated Claim Closing'
SET @recipients = '123@a.com'
SELECT @job_exec_mins=CEILING(DATEDIFF(SECOND,aj.start_execution_date,GetDate()) / 60.0)
FROM msdb..sysjobactivity aj
JOIN msdb..sysjobs sj on sj.job_id = aj.job_id
WHERE aj.session_id = (SELECT MAX(session_id) FROM msdb..syssessions) -- make sure this is a current run
AND sj.name = @job_name
AND aj.start_execution_date IS NOT NULL -- job is currently running
AND aj.stop_execution_date IS NULL -- job hasn''t stopped running
If @job_exec_mins>1 BEGIN
SET @subject = 'The Automated Job is running long, now for ' + CAST(@job_exec_mins AS varchar(5)) + ' minutes.'
SET @body = 'The Automated Job is running long, now for ' + CAST(@job_exec_mins AS varchar(5)) + ' minutes.'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = @recipients,
@body = @body,
@subject = @subject
END /*IF*/
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 11, 2020 at 9:33 pm
As I noted earlier, you could also pass values to the monitoring job to control the check time interval and/or the options for how many times to check on the job and/or whether or not to kill the orig job at a certain point, etc..
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply