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.