Detect when scaling Azure SQL database via async TSQL command has failed

Question

I have a scheduled task which runs each night to copy an existing production Azure SQL db to a dev environment. Once copied, I scale the database down (actually I put it into an elastic pool to share the cost amongst multiple other dbs) to reduce cost as it's no longer in production and only used for dev workloads. This is really easy to do with a 1-liner using TSQL:

ALTER DATABASE [newly-copied-db] MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = [my-elastic-pool] ) );

This command is async. Is comes back straight away with

Commands completed successfully.

In reality, it can take some time to move the db into the elastic pool.

The problem I'm having is that the elastic pool has a finite amount of allocated space. If the scaling operation fails because I'm using too much of that space, I need to know. Also, it would be great to know that the job has completed successfully so I could trigger some notification.

How can I tell when the scaling operation has failed (or, indeed, succeeded)?

Ideally via an event / callback type mechanism rather than polling

Answer 1

Try something like this based upon the dm_operation_status DMV:

while (
    select top 1 state_desc
    from sys.dm_operation_status 
    WHERE resource_type_desc = 'Database'
    AND major_resource_id = @db
    AND operation = 'ALTER DATABASE' 
    order by start_time desc
) in ('PENDING', 'IN_PROGRESS')
begin
    print 'waiting for operation to complete: ' + convert(varchar,getdate(),120);
    waitfor delay '00:00:30';
end

while (
    select top 1 state_desc
    from sys.dm_operation_status 
    WHERE resource_type_desc = 'Database'
    AND major_resource_id = @db
    AND operation = 'ALTER DATABASE' 
    order by start_time desc
) in ('COMPLETE')
begin
    print 'complete!';
end
else
begin
    print 'failed!';
end