Azure SQL Database – Fixing My Scaling Failure

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).

27percent

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

eleonlibe

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

 

100per

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.

 

 

Advertisements

2 thoughts on “Azure SQL Database – Fixing My Scaling Failure

  1. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s