Setup Long-Running Jobs Alert
In my organization, we have many SQL Agent jobs that are expected to execute within a particular time window. But there are times when some of them take longer than expected.
I wrote below TSQL script which when executed within a SQL Agent job at the hourly schedule, will send a notification mail to DBAs for long-running jobs.
Code is designed to consider the Expected Duration of job, and report number of times it has finished at various time threshold. Also, exception can be added for any job that should not be there in ‘Long Running Jobs’ mailer. To add this exception, simply set [Ignore] to 1 against [JobName] in table [dbo].[SqlAgentJobs] created for this alert.
Below is one sample mail:-
Hope this helps. Happy Coding 🙂