Azure SQL – Dataspace Used vs Max Dataspace

azureazure-sql-databasesql server

I have a single Azure SQL database in Azure with the following database data space
database data space

As I read the documentation: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-file-space-management the "Used space" will keep growing, and I myself need to shrink it. That is perfectly fine, and as expected. I can also see that the allocated space is a bit larger than the used space. This is how i hope it works:

  1. when used space is nearing the allocated space, some more space is allocated
  2. when used space is at max size we have no more space available thus we run out of space

I am a bit scared that I have misunderstood something and my database (and site) will stop working when used space is at allocated space. I am kind of new into the world of Azure.

It was something i just noticed today. I have a monitor set up to monitor data space used (in percent), and i will be alerted when it hit max. But i will never hit max if it doesn't grow beyond allocated space.

Best Answer

First of all, no need to worry as this is normal behaviour.

The link in your answer From msdocs on Data space allocated gives us the info needed:

The amount of space allocated grows automatically, but never decreases after deletes. This behavior ensures that future inserts are faster since space does not need to be reformatted.

This space allocated grows automatically and will stay the same way when it grows out.

If the database keeps on growing, eventually the allocated space will be the same size as the maximum storage size. This is comparable to the autogrowth behaviour of regular sql server databases.

A useful part of the azure portal is that there are these little (i) marks on many settings.

enter image description here

These should point you in the right direction, the learn more points us to above linked page.

In short

when used space is nearing the allocated space, some more space is allocated

yes (as long as you are not at the database limit)

when used space is at max size we have no more space available thus we run out of space

and yes

Another way to get the size is checking the space available in the properties of the azure sql db with ssms.

enter image description here

This could mean that even sql server itself does not know that a mechanism like allocated space is used.

Proof

growing out the previously mentioned database a bit:

enter image description here

increases the used space & allocated space:

enter image description here

Afterwards I deleted the data & shrinked the database (Which you shouldn't ;)), also not with dbcc shrinkdatabase, double foul there.

dbcc shrinkdatabase([databasename])

Results in the same allocated space, but less used space:

enter image description here

Related Question