TSQL Tip of the day

Proactively raise an alert if my SQL agent job runs beyond certain time. For Querying Jobs and status you can use below query. From the job running time we can raise alert accordingly

SELECT DATEDIFF(MINUTE,ja.start_execution_date,GETDATE())
FROM msdb.dbo.sysjobactivity ja
INNER JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
WHERE j.name like '%JOBNAME%'
AND last_executed_step_date IS NULL

Related Read - Determine the current status of a SQL Server Agent job

Happy Reading!!