SQL Server – Stop Agent Job After Threshold Time and Log the Query Metrics

Spread the love

Being a DBA, I know the pain of a long running job creating performance issues for other jobs and user sessions.

For my current organization, I have created below TSQL procedure that accepts job name to monitor and threshold in minutes after which the job will be stopped. Once the job is stopped, a mail will be sent to recepients through default mail profile. Apart from stopping the job, it is also logging the job session details like spid, logical_reads, cpu etc into table [dbo].[whatIsRunning].

Code for table used for Logging Job session details is below:-

Friends, Feel free to use and distribute the code. If you liked the codes then do share.
Happy Coding 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.