Is there a way to set up an alert that will tell me when a scheduled maintenance job (Like transaction log backup) is running too long or that it has not completed. I had a transaction log backup run for over 15 hours one night, thus all my other jobs would not run. Any suggestions would be great.
select @minutes_running = DATEDIFF(minute,last_batch, getdate())
where program_name LIKE ('%0x' + @process_id +'%')
if @minutes_running > @minutes_allowed
select @message_text = ('Job '
+ ' has been running for '
+ SUBSTRING(CAST(@minutes_running AS char(5)),1,LEN(CAST(@minutes_running AS char(5))))
+ ' minutes, which is over the allowed run time of '
+ SUBSTRING(CAST(@minutes_allowed AS char(5)),1,LEN(CAST(@minutes_allowed AS char(5))))
+ ' minutes.')
@recipients = @person_to_notify,
@message = @message_text,
@subject = 'Long-Running Job to Check'