question

JJKilduff-9365 avatar image
0 Votes"
JJKilduff-9365 asked NavtejSaini-MSFT commented

Can you start an ElasticJob at a specific step in Azure SQL

I have an elastic job agent set up for an Azure SQL database. The job has three steps (job.jobsteps) and has run correctly in the past.

If the job fails on the second step, I would like to restart it on the third step and not do the first two steps again. Is there a way to accomplish this? It doesn't look like there is.

An example (pseudo-code)

In this example - this is my Job X. These are Job X's jobsteps (in real life they are T-SQL procedures). The job runs - it does something like this:

step 1 - Create Tables - I create table A - it is empty
step 2 - Populate local Tables from extended tables - I use my extended table B to populate table A
step 3 - do some data manipulation on the local tables and write them to new tables - I do some data manipulation and write my results to a new Table C

Now - let us say there is a problem with the data manipulation and the results in Table C are wrong or need some new logic added. I change the logic in Step 3. I don't need to recreate table A or repopulate it from B - I just want to start Job X at step 3.

All job steps have a @retry_attempts set to 0 because I don't want them to try again if they fail. If step 3 fails or gives me a questionable result - I will adjust the T-SQL procedure in step 3 and then want to start job X from Step 3.

Can that be done?

Please note - this is not actually what I am doing - just an illustrative example to explain the problem I am having. Please do not answer with workarounds for creating or populating tables. I simply need to know if Job X can be started from Step 3 in ElasticJobs preview on SQL Azure and if yes – how?

Any advice much appreciated.



azure-sql-database
· 4
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.

@JJKilduff-9365

We checked and found some of the support issues and here are some of the commands mentioned that you can try for this. Run the following query to get the job status.

SELECT *
FROM jobs.job_executions
WHERE job_name = 'demo123'
ORDER BY start_time DESC

And then check the document for Start job here and try to start with the step name.

Please try and let us know the result.

Regards
Navtej S


0 Votes 0 ·

Thank you for your comment. The documentation you pointed me to is for the SQL Agent which DOES NOT EXIST in Azure SQL. Azure SQL has ElasticJobs which is in preview. It only has instructions to start the job from the beginning. There does not appear to be an option to start it from a different step. Is this the case? I did try the following command to see if it worked but was undocumented and it did not.

exec jobs.sp_start_job 'Job X', 'Step 3'





0 Votes 0 ·

Thanks for checking @JJKilduff-9365. And confirming it didnt work.

Let me check with our team and get back to you.

Regards
Navtej S

0 Votes 0 ·
Show more comments

1 Answer

srinia avatar image
0 Votes"
srinia answered NavtejSaini-MSFT commented

Restarting/executing a job will start at the steps in the order specified. They always start at the first step and continue till they complete or hit an error. They cannot be restarted from the middle. But, if the job steps are written in an idempotent way, re-executing the job should complete and result in the correct behavior. There is brief blurb on this in the documentation.
https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-overview#best-practices-for-creating-jobs.
In your case if your step 1 and 2 check for the existence of the table and data, re-executing the job will succeed. Make sure that your job steps are written in this way wherever possible.


· 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.

Hi there - I did suspect that was the case but needed official confirmation. I will accept this as the answer. The ability to start from a different step would be quite useful as it would mimic the behaviour of the SQL Agent which ElasticJobs seems to be trying to imitate. Would you know if there were any plans to introduce that functionality at all? Or if there is a way to request it for a subsequent release? Many thanks!

0 Votes 0 ·

@JJKilduff-9365

You can raise your feedback on Uservoice for the Product team to check.

Regards
Navtej S


0 Votes 0 ·