SQL Error during Lazy Loop awaiting Azure DB resize

Question

I want to automate some DB scaling in my Azure SQL database.

This can be easily initiated using this:

ALTER DATABASE [myDatabase] 
MODIFY (EDITION ='Standard', SERVICE_OBJECTIVE = 'S3',  MAXSIZE = 250 GB);

But that command returns instantly, whilst the resize takes a few 10s of seconds to complete.

We can check the actual current size using the following, which doesn't update until the change is complete:

SELECT DATABASEPROPERTYEX('myDatabase', 'ServiceObjective')

So naturally I wanted to combine this with a WHILE loop and a WAITFOR DELAY, in order to create a stored procedure that will change the DB size, and not return until the change has completed.

But when I wrote that stored procedure (script below) and ran it, I get the following error every time (at about the same time that the size change completes):

A severe error occurred on the current command. The results, if any, should be discarded.

The resize succeeds, but I get errors instead of a cleanly finishing stored procedure call.

Various things I've already tested:

• If I separate the "Initiate" and the "WaitLoop" sections, and start the WaitLoop in a separate connection, after initiation but before completion, then that also gives the same error.
• Adding a TRY...CATCH block doesn't help either.
• Removing the stored procedure aspect, and just running the code directly doesn't fix it either

---

My interpretation is that the Resize isn't quite as transparent as one might hope, and that connections created before the resize completes get corrupted in some sense.

Whatever the exact cause, it seems to me that this stored procedure just isn't achievable at all; I'll have to do the polling from my external process - opening new connections each time. It's not an awful solution, but it is less pleasant than being able to encapsulate the whole thing in a single stored procedure. Ah well, such is life.

---

Question:

Before I give up on this entirely ... does anyone have an alternative explanation or solution for this error, which would thus allow a single stored procedure call to change the size and then not return until that sizeChange actually completed?

---

Initial stored procedure code (simplified to remove parameterisation complexity):

CREATE PROCEDURE [trusted].[sp_ResizeAzureDbToS3AndWaitForCompletion]
AS
    ALTER DATABASE [myDatabase] 
    MODIFY (EDITION ='Standard', SERVICE_OBJECTIVE = 'S3',  MAXSIZE = 250 GB);

    WHILE ((SELECT DATABASEPROPERTYEX('myDatabase', 'ServiceObjective')) != 'S3')
    BEGIN
        WAITFOR DELAY '00:00:05'
    END

    RETURN 0

Answer 1

Whatever the exact cause, it seems to me that this stored procedure just isn't achievable at all; I'll have to do the polling from my external process - opening new connections each time.

Yes this is correct. As described here when you change the service objective of a database

A new compute instance is created with the requested service tier and compute size... the database remains online during this step, and connections continue to be directed to the database in the original compute instance ... [then] existing connections to the database in the original compute instance are dropped. Any new connections are established to the database in the new compute instance.

The bolded text will kill your stored procedure execution. You need to do this check externally