Share via

Azure SQL DB MAXSIZE - what can I do with that?

  • Article

Information:  

Azure SQL DB max size is limited by the pricing tier for each DB.

for instance Basic is limited by 2GB, Standard by 250GB, and Premium by 500GB per database.

you can also set your own limit while it's lower or equal to the level max size and if this is in the list of available sizes.

this can be done by running the T-SQL Alter command

 ALTER DATABASE { database_name | CURRENT } MODIFY MAXSIZE = { 100 MB | 500 MB |1 | 5 | 10 | 20 | 30 … 150 … 500 } GB

for instance:

ALTER DATABASE CURRENT MODIFY MAXSIZE=100MB

OK, That's nice... but why would I choose to set limit at lower size other then the max size for my tier?

especially because this does not affect the cost.

Possible answer:  

let say that I want to downscale my DB to Basic every night to save some money.

this means that my DB size should always be lower than 2GB, even when I upscale the database to higher tier.

so to make sure my DB will not exceed the 2GB size limit I can set that as custom max size.

this is also why the database max size is not changed when you upscale or downscale the database is the maxsize have custom value.

if you have any other idea, please write it on the comments below...

Conclusions:

  • Database size can be set to lower limit than the tier max size
  • if custom size has been set, it will not be changed when you upscale / downscale the database and you must change that manually.

Keep enjoying using Azure SQL DB.