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.