ken.trock (4/25/2013)

---

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

RETURN

DECLARE cRetentionJobs CURSOR FOR

select name

from #JobInfo

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.

Ken