CODEWITHSUNDEEP
azure
Monzie
Monzie

Reputation: 41

Please wait until the service objective assignment state for the database is marked as 'Completed' error message

We have three databases in Azure SQL that appear to be locked. We run the query

ALTER DATABASE [DBName] MODIFY(EDITION='basic',SERVICE_OBJECTED='basic')

or 

ALTER DATABASE [DBName] MODIFY(EDITION='standard',SERVICE_OBJECTED='S2')

and end up with the error message; "A service objective assignment on server '[ServerName]' and database '[DBName]' is already in progress. Please wait until the service objective assignment state for the database is marked as 'Completed'."

They have been sitting in this state, with a little clock next to the Edition for several days now. Any help would be greatly appreciated!

Upvotes: 1

Views: 2451

Answers (2)

Jan Hebnes
Jan Hebnes

Reputation: 160

I have just been in the same state, and managed to get access again after running a succesfull export of the database.

I have marked a database to be scaled from S2 to S3, from the web interface it just showed as upgrade in progress for over 40min.

When using powershell i could see the CurrentServiceObjectiveId did not match the RequestedServiceObjectiveId meaning the request was made. 

Get-AzureRmSqlDatabase -ResourceGroupName "ResourceGroup_WestEurope" -ServerName "Server-WestEurope" -DatabaseName "TestingAutomatedScaling"

When trying to Set the database back of forth in state using powershell 

Set-AzureRmSqlDatabase -ResourceGroupName "ResourceGroup_WestEurope"
-ServerName "server-westeurope" -DatabaseName "TestingAutomatedScaling" -Edition "Standard"
-RequestedServiceObjectiveName "S2"

and -RequestedServiceObjectiveName "S3"

I got

A service objective assignment on server '[ServerName]' and database '[DBName]' is already in progress. Please wait until the service objective assignment state for the database is marked as 'Completed'

But I could not see the progress state anywhere. The database was only 15Gb in size... so a 40min+ worktime seems like a blocked state.

Now for the magic part... After doing an export of the database, first attempt got me an error. 

serviceRequestId:47881ba1-5a56-492f-8307-455dbb95c18d statusMessage:{"code":"0","message":"There was an error that occurred during this operation : '<string xmlns=\"http://schemas.microsoft.com/2003/10/Serialization/\">Error encountered during the service operation. ; Exception Microsoft.SqlServer.Management.Dac.Services.ServiceException:Unable to authenticate request; </string>'","target":null,"details":[{"code":"0","message":"There was an error that occurred during this operation : '<string xmlns=\"http://schemas.microsoft.com/2003/10/Serialization/\">Error encountered during the service operation. ; Exception Microsoft.SqlServer.Management.Dac.Services.ServiceException:Unable to authenticate request; </string>'","target":null,"severity":"16"}],"innererror":[]}

Doing it again, the export happened to work.

After the export was successfull the database came back online and was marked as S3. Until further testing I wont know if i just needed to wait or if exporting the database helped unblock. But information might be helpful to anyone else finding the topic.

Upvotes: 0

Balvinder Singh
Balvinder Singh

Reputation: 41

You can check the database state using ServiceObjectiveAssignmentState Property before performing any db changes

Rest Api: https://msdn.microsoft.com/en-us/library/azure/dn505708.aspx

Powershell: https://msdn.microsoft.com/library/azure/dn546735.aspx

Upvotes: 1

Related Questions