2

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.

3

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

3
  • Thanks. I actually got the link i am refering from the (i) mark. I have just read the quote you have posted "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." from the doc, and I am more peaceful now :) Thanks for the good answer. As you may have felt, I needed it.
    – mslot
    Sep 11 '19 at 18:14
  • @mslot Oh sorry I missed that! No problem! Glad to have helped :) Sep 11 '19 at 18:15
  • 1
    And thanks for the properties hint. I completly forgot about that I could see it there!
    – mslot
    Sep 11 '19 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.