Azure SQL Database Scale Operation Stuck

Question

We have many deployments of Azure SQL Database that we scale up and down during ETL processing several times a day. We've been doing that successfully for 2+ years.

In the last 2 weeks, we've had 4 instances of the Azure SQL Database Scale Operation getting stuck at 25% and hanging there until we manually login to the Azure Portal and cancel the operation.

We are very familiar with the amount of time the typical scale operations take since we've been performing them for years, so it isn't that we aren't waiting long enough. We have databases less than 50 GB with scale operations stuck at 25% for 17+ hours.

What would cause this sudden change?

Should/could we be detecting this failure case in our ETL processes and working around it somehow (calling another API to cancel the operation, etc.)?

Answer 1

You can monitor the progress of the scale operation using the following query:

SELECT * FROM sys.dm_operation_status  
WHERE major_resource_id = ‘YourDb’  
ORDER BY start_time DESC;  

If you see long time has elapsed since the scale process started then you can trigger a PowerShell script as mentioned here. The only other option available is to use the portal to cancel the scale.