CODEWITHSUNDEEP
azureazure-sql-database
Nil Pun
Nil Pun

Reputation: 17373

SQL Azure Database scale operation from P3 Premium to S2 Standard failed

I'm trying to scale down the SQL Azure Database from P3 to S2 but I'm getting error below. 

Database scale operation from P3 Premium to S2 Standard failed for xxDB.
    ErrorCode: undefined 
    ErrorMessage: The edition 'Standard' does not support the database max size '500 GB'.

What's the best way to scale in?

Upvotes: 0

Views: 1340

Answers (2)

Bill Gibson - MSFT
Bill Gibson - MSFT

Reputation: 131

You should be able to scale down a database and change edition (e.g. from Premium to Standard) without first changing the MaxSize property. Can you share how you did this (e.g. T-SQL, Azure portal, PowerShell, REST API call, C# client SDK). Is it possible that in your code you took the existing database MaxSize property and submitted that in your database update request - because that would cause the error you saw (Standard doesn't support 500GB). The answer indicating that you need to separately scale and change MaxSize is correct currently when you scale up from Standard to Premium and want to get the benefit of the greater storage allocation with Premium, but that apparently wasn't what you were doing.

We are looking to make the behavior more intelligent here, but in the meantime it's possible you hit a bug.

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28900

it seems you have to first change db size .. and then do your degradation of tiers.below is the entire syntax for changing,i dont have azure DB to play with..

ALTER DATABASE database_name 
{
    MODIFY NAME =new_database_name
  | MODIFY ( <edition_options> [, ... n] ) 
  | COLLATE collation_name
  | SET { <db_update_options> } 
  | ADD SECONDARY ON SERVER <partner_server_name>
      [WITH (<add-secondary-option>::= [, ... n] ) ]
  | REMOVE SECONDARY ON SERVER <partner_server_name>
  | FAILOVER
  | FORCE_FAILOVER_ALLOW_DATA_LOSS
}

<edition_options> ::= 
{
      MAXSIZE = { 100 MB | 500 MB |1 | 5 | 10 | 20 | 30 … 150 … 500 } GB  
    | EDITION = { 'web' | 'business' | 'basic' | 'standard' | 'premium' } 
    | SERVICE_OBJECTIVE = 
                 {  'S0' | 'S1' | 'S2' | 'S3'| 'P1' | 'P2' | 'P3' | 'P4'| 'P6' | 'P11' 
                         { ELASTIC_POOL (name = <elastic_pool_name>) } 
                 } 
}

Then try degrading

Refereences: https://azure.microsoft.com/en-us/documentation/articles/sql-database-scale-up/

in the above link look for below section:

NOTE: Changing your database pricing tier does not change the max database size. To change your database max size use Transact-SQL (T-SQL) or PowerShell.

Upvotes: 2

Related Questions