Azure SQL “Scale up and Scale down operation of database stuck”
We do have a Azure SQL Database and we did a schedule automation to resizes the DB core from 4 to 8 and back to 4 every day.
We build this automation, to Save some cost. During business pick time, when we gets the traffic, we scale up the DB so that it can take the load and post business hour, we would like to scale down our DB as no traffic to our application.
Our automation was working as expected but one day our automation failed and DB scale up operation got stuck.
Since we are using a PaaS service, we don’t have an option in backend to check why the operation is stuck.
To resolve my issue, I used below steps to cancel my stuck operation
- open Azure cloud shell or use your local Azure CLI installation
2) edit the following command with your database details and execute the script
[code language=”PowerShell”]$ResourceGroupName = “”
$ServerName = “”
$DatabaseName = “”
# — — — — — — — — — — — — — — — — — — — —
$OperationName = (az sql db op list — resource-group $ResourceGroupName — server $ServerName — database $DatabaseName — query “[?state==’InProgress’].name” — out tsv)
if(-not [string]::IsNullOrEmpty($OperationName))
{
(az sql db op cancel — resource-group $ResourceGroupName — server $ServerName — database $DatabaseName — name $OperationName)
“Operation “ + $OperationName + “ has been canceled”
}
else
{
“No in progress operation found”
}
[/code]
3) review the output to confirm operation has been canceled.
Incase if still your operation is stuck state, you can open a support case with Microsoft to kill that operation from backend.
Microsoft came up with a Chat support and its an addition to their Phone and Email support.
If the Support Engineer available in the Queue, you will get the chat option in the portal while raising the ticket.
Once you open a chat request, a support Engineer get assign to it and they can help you in resolving the case. Try to gentle with them, give them some time to understand your issue. You must be familiar with your environment but they are not.
You must be frustrated with the issue or product but its not something they build that product, they are there to just perform their job to support product and customer like you.
Be kind and nice, you will get the same response in return :)