question

IsmailCassiem-3967 avatar image
0 Votes"
IsmailCassiem-3967 asked IsmailCassiem-3967 commented

SQL Agent Job Status

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

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IsmailCassiem-3967 avatar image
0 Votes"
IsmailCassiem-3967 answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IsmailCassiem-3967 avatar image
0 Votes"
IsmailCassiem-3967 answered IsmailCassiem-3967 commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You can query the job tables in msdb, but you need to find something that you know always does this, or have something that check that this ran etc.

But as I said, you could schedule something from Windows Task Scheduler. Or an instance of SQL Server on another server. Or write your own service. There is no magic bullet here.

0 Votes 0 ·

Thank You for helping me
I have no other solution but to depend on sql agent, its just there's task that goes into a loop or does not complete or runs much longer than its expected time which throws out the entire automation refreshes

0 Votes 0 ·