What is the expected behavior when SQL agent is restarted while SQL Job running

[John O'Mullane]

Hi,
I noticed a situation today where one of my SQL Jobs was running at a moment in time when I restarted my SQL agents.
This caused the SQL Job (Part of a workflow) to fail with status 

"ENDED_LOST - ended undefined (host terminated prematurely)."

I also noticed in the messages the following

"U02000198 Restore of Jobs is not implemented in this Agent"

So, I took a look at my SQL .ini and can see that there are 2 "retry" entries.

[SQL]

connect=60

retention_time=180

retry=15

type=ORACLE

useOraClient=0

retry=0

I would have expected this job not to fail but try to run again but it looks like the second "retry" entry is overwriting the first one.

If I remove the second entry, will my Job then wait and retry or is restarting my SQL agent actually causing the Job to break and so automatic recovery not possible?

I'm not sure if this "retry" setting will only retry if it cannot connect to DB or will retry if agent is down.

Thanks,
John.

[Anon Anon]

seems that you should remove the line :-)

retry=                                    

Number of connection attempts to the database.

                   

The number that is specified in this parameter determines how often the agent tries to connect to the database. After the nth failure, the job ends with status ENDED_NOT_OK.

                   

The agent continues to connect to the database if this parameter is not specified in the INI file. The job remains active until the database is available again or until it is canceled.

https://docs.automic.com/documentation/webhelp/english/ALL/components/AE/11.2/All%20Guides/help.htm#ucackg.htm

I'm not sure if this "retry" setting will only retry if it cannot connect to DB or will retry if agent is down.

If you shutdown the agent it won't be able to to anything, even rerying something :-)

cheers, Wolfgang

[John O'Mullane]

FrankMuffke

Thanks, I did see this explained in the documentation..

I guess what I am really asking here is... If I have a SQL job running and the SQL agent goes down, will my Job fail, like it did in my case?

If so, the message mentions that a restore is not possible due to a setting in Agent.
Do you know what this setting is?

[Anon Anon]

I guess what I am really asking here is... If I have a SQL job running and the SQL agent goes down, will my Job fail, like it did in my case?
=> Yes it will.

If so, the message mentions that a restore is not possible due to a setting in Agent.
Do you know what this setting is?
=> I would say thats a feature that is not yet available in the agent itself.

cheers, Wolfgang

[John O'Mullane]

FrankMuffke
I just did a few test myself regarding behavior when SQL agent is restarted.

SQL jobs running at the moment the SQL agent is restarted will fail if standalone job and will block if part of a workflow, even if the dependencies are set to "ANY_OK" else  "Abort"

I'm not surprised Jobs fail when SQL agent is restarted but I am surprised that any SQL jobs that are part of WF's will block, regardless of setting in dependencies in properties of preceding Job.

Is the behavior different when Engine is brought down in a controlled manner via SM?

[Anon Anon]

John O'Mullane said:

but I am surprised that any SQL jobs that are part of WF's will block, regardless of setting in dependencies in properties of preceding Job.

Is the behavior different when Engine is brought down in a controlled manner via SM?

Honestly I don't know, sorry.

You should ask support or test it yourself :-)

cheers, Wolfagng

[John O'Mullane]

Thanks, will test myself.

[Punit Mishra]

Hi,
Could you test this out and identify a solution to rerun the failed SQL job again and then workflow moves on to the next step after workflow completes