June 6, 2017 at 4:46 am
Hey there,
I've come across a specific need for a script (to run i.e. as a sql job hourly) which sends an email if the necessary job has not run for x hours.
I've tried a few things and googled for 2 hours now, but as I'm not that great at t-sql I really need your help here. Someone out there with some kind of help or solution?
Many thanks in advance.
Greetings from germany.
Stefan
June 6, 2017 at 7:38 am
You can run this as a job every x minutes to check for a specific job name, it will also omit any current running version of this job.
DECLARE @jobres nvarchar(10);
set @jobres = (
SELECT top 1 DATEDIFF(hour,sja.stop_execution_date, getdate())
FROM msdb.dbo.sysjobactivity AS sja
LEFT OUTER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE
sja.start_execution_date IS NOT NULL
and sj.name = 'YourJobNameHere'
and sja.start_execution_date IS NULL and sja.stop_execution_date IS NULL
ORDER BY sja.stop_execution_date DESC)
IF @jobres > '1'
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MailProfileName',
@recipients = 'recip@mail.com',
@subject = 'Job Test last completed more than 2 hours ago',
@query_result_header = 0
END
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
June 6, 2017 at 11:16 pm
Hello Henrico,
first of all: thank you very much for the help and the quick response!
Unfortunately the script doesn't seem to work. I've tested it on a job which only runs once a week (last run was two days ago).
Tested it on SQL 2008R2 and 2014, same outcome. If I do print @jobres there is just a blank result.
June 7, 2017 at 3:21 am
shp.shftr - Tuesday, June 6, 2017 11:16 PMUnfortunately the script doesn't seem to work
I think Henrico's got his join the wrong way round. sysjobs should be on the left hand side, so that you list all jobs and then display those that have had no activity in the last x hours. This will be a good learning opportunity for you to take the query as a starting point and have a go yourself. Please post back if you struggle with anything in particular.
John
June 8, 2017 at 12:45 am
Hello, thanks for the input! 🙂
I simplified the whole code to work with a single select which is a lot easier for me to see trough:
USE msdb;
DECLARE @jobres nvarchar(10);
set @jobres = ((
select TOP 1 DATEDIFF (hour, stop_execution_date, GETDATE()) from sysjobactivity
where job_id = 'MY JOB ID'
-- got JOB ID from sysjobs table in msdb
order by stop_execution_date DESC) - 1)
IF @jobres > '1'
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MY PROFILE',
@recipients = 'MY MAIL',
@subject = 'Job Test last completed more than 2 hours ago',
@query_result_header = 0
END
I have no clue why "IF@jobres > '1'" does not work with a value other than one... Thats why I added the -1 to the whole select.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply