SQL Agent Job Status

Question

Good day,

I have an automation sql agent job that triggers another 2 jobs on certain steps, i had an incident where the server was restarted and i got no fialure notifications
i have created another job to check services restarted

the auomtion job has notification for onfailure etc, i would like to go one step further.
for example if second job did not start @4am send notification etc so that i know there's other issues (job hanging, failure didnt trigger etc)

i have a step that executes a send email but i need someting that can check status of steps within jobs or job not completed by x time sonething has failed

Any ideas please?

regards

Answer 1

I'm not sure that I follow exactly here, but if you have one step that triggers another, and you want that to happen even if SQL Server is restarted, maybe should rather build something on Service Broker? True, if you have never worked with Service Broker, there is a learning curve.

On the other hand, if you are going to rely entirely on Agent, it's difficult to make it foolproof. What if some joker stops Agent? You could do stuff from Task Scheduler that's true, but that's one more thing to keep track of.

Answer 2

Hi, thank you for helping
I have an agent step to notify on services but you are correct I'm solely dependent on agent job to notify me of job or step failure

Is there other options to check if job or steps or job triggered as expected and notify any inconsistency or failure?

Service Broker?

Answer 3

It's hard to say more with the few details that I have.

Service Broker is something completely different. It's an asynchronous messaging service and can be used for a number of things. But there are situations where Service Broker can be an alternative to Agent. Say that when a row is updated in a table, you want some data to be recalculated, but you don't want that to happen directly inside the transaction, as it was take to much time. One solution is to have an Agent job that runs periodically and scans for changes. Another is to post a message on a Service Broker queue, and this fires an activation procedure which runs separately and performs the recalculation.

Service Broker is not a scheduler, but you can set up conversation timers, so that an activation procedures fires periodically to perform an action. Since everything is stored in the database is internal tables, you will not miss actions because something was down at the time.

If you want to learn about Service Broker, this book is a good start:
https://www.amazon.com/Rational-Server-Service-Broker-Guides/dp/1932577270/ref=sr_1_1?dchild=1&keywords=Roger+Wolter+Service+Broker&qid=1626856717&s=books&sr=1-1. Roger Wolter was the Program Manager for Service Broker when it was originally developed, so he knows what he is talking about. And it is a very neat book - it's only just over 200 pages.

Answer 4

Thank you very much

I don't going service broker is shat I require, I need something to check if jobs activated successfully or running accordingly

Any other options please
We are busy migrating yo sql19 and I'm hoping there's others options yo check