We have data retention jobs that hang sometimes and decided to do something similar; automatically kill them at a certain point.
SELECT * INTO #JobInfo
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job')
--Don't let this accidently run between 7pm and 4am, legitimate times for data retention
IF DATEPART(hh, GETDATE()) >= 19 OR DATEPART(hh, GETDATE()) <= 4
DECLARE cRetentionJobs CURSOR FOR
where current_execution_status <> 4 --anything not idle
AND name LIKE '%DataRetention%' AND [enabled] = 1
We'll run thru this cursor and exec msdb..sp_stop_job for each.
But we have the convenience knowing that there's only one schedule for each of these jobs.
Thanks for the article.