This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(275.2, 61%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAS%3C/text%3E%3C/svg%3E)
I want to run my stored procedure async using T-SQL. Can we do it ?
Thanks @Oury Ba-MSFT for the reply.
Do you know how we can autoscale elastic jobs ?
If I want to scale it up to JA200 if we reach 50 concurrent jobs what should I do. Is this feature available ?
@Ayush Shrivastava Checking if you have additional queries or need more clarification.
Please don't forget to mark as accept answer wherever information provided were helpful.
Regards,
Oury
@Ayush Shrivastava Checking if you have additional queries or need more clarification.
Please don't forget to mark as accept answer wherever information provided were helpful.
Regards,
Oury
Azure Automation allows you to create runbooks that can execute PowerShell scripts or other scripts to run your stored procedures asynchronously
or
You can use Azure Functions to create a serverless solution that executes your stored procedure based on a timer schedule
and another method is to use service broker
If I use elastic jobs does it run the procedure in a fire and forget Manner ?
Example -
I have 10 jobs and all of them will have one procedure to call. I want to understand the concurrent request mentioned here.
Until all those jobs are done. Will it acquire the concurrent pool ?
If I have 100 Concurrent request allowed as per JA100 tier will it acquire 10 and it will be 90 ?
or when it does Exec procedure_name it releases that thread ?
Basically will It hold the concurrent pool till the job is finished or is it just about executing the procedure and we are good.
If I use Elastic Jobs, does it run the procedure in a fire-and-forget manner?
No, Elastic Jobs holds the concurrent request slots until the stored procedure is completed. The slots are not released immediately after starting the execution; they remain occupied for the entire duration of the procedure.
Does it hold the concurrent pool till the job is finished, or is it just about executing the procedure and we are good?
Elastic Jobs will hold the concurrent pool slots until each job is finished. If you have 100 concurrent requests available and run 10 jobs, those 10 jobs will occupy 10 slots until they are complete, leaving you with 90 available slots during their execution. The slots are released only after the jobs finish.
@Ayush Shrivastava Thank you for reaching out.
In addition to Sedat SALMAN's answer, you could also use ADF and schedule the execution of the procedure. You can create a pipeline with one or more Stored Procedure activities and add a trigger to schedule the pipeline running. see this https://stackoverflow.com/questions/62417858/run-azure-sql-server-stored-procedure-async
https://learn.microsoft.com/en-us/azure/azure-sql/database/job-automation-overview?view=azuresql
Regarding the second question,
By default, job agents are created at JA100, allowing up to 100 elastic job executions concurrently. Initiating a service level change is an asynchronous operation and the new service level will be made available after a short provisioning delay.
If you need more than 100 concurrent executions of elastic job agents, higher service levels are available, see Concurrent capacity tiers. You can currently change the service level of a job agent via the Azure portal, PowerShell, or REST API.
Exceeding the service level with concurrent jobs will create queuing delays before jobs start in excess of the service level's concurrent jobs limit.
Scale up/down can be done through portal as well as PowerShell/REST APIs - https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-tutorial?view=azuresql#scale-the-elastic-job-agent-by-using-powershell
But we don't have an auto scaling feature for Job Agent based on the number of concurrent jobs at this point. Feel free to file a feature request through elastic job · Community (azure.com) for a future release.
Hope that helps.
Regards,
Oury