This is kind of a follow up from my last blog post about a scale down request issue. (https://blobeater.blog/2018/11/07/azure-sql-database-aborting-scale-request/) I was confused, so confused that I ended up logging a support request with Microsoft. The issue was I wanted to scale down a database from S1 to Basic however it would take hours for a 1GB database. Obviously something was up, but what?
Apparently there was an issue on the recovery process where I just ended up in a hung state – I have been told it will be addressed (I don’t know what exactly, I am guessing the recovery portion of the scale) and I actually have no idea how to replicate the issue or what set of circumstances are needed to replicate it.
Just to confirm what I was seeing via sys.dm_operation_status. In progress and stuck at 27% for hours (as shown below).
The database with the issue had the setting ELEVATE_ONLINE set to FAIL_UNSUPPORTED, meaning with this value it elevates all supported DDL operations to ONLINE. Operations that do not support online execution will fail and throw a warning.
SELECT * FROM sys.database_scoped_configurations
I was told to turn this off.
ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = OFF ;
I then issued the scale request and it completed within seconds rather than hanging for hours.
SELECT * FROM sys.dm_operation_status
This is good to know in case you are having issues too. Now I know some will say that if the process can’t go online it will error anyways but in my case it would just hang for hours. To be honest, I would rather have an error.
This is great information. I ported almost 50 client db’s to Azure SQL and have had scaling nightmares multiple times over this year to the point I have just kept everything up at the higher rate so I done cause pain for my clients and myself. Inexcusable that MS hasn’t resolved this. I have had thousands of users down for 4-5 hours at times.
That sounds very frustrating out of interest did those databases have ELEVATE_ONLINE set to FAIL_UNSUPPORTED ?