How big is my Azure SQL database?

  • Article

First Let me explain how the portal calculating the database size

Assuming you have S3 database you will be limited to maximum of 250GB data size.

But if you will take a look at the database size on the portal you will see that you have credit of 268GB

This is not because we happy to give free bytes, this is because of the way we calculate the size.

The reason for that is in calculation of the number of bytes in Kilobyte(KB) vs Kibibyte(KiB)

To keep it simple this is the difference when 1KB is 1000B vs 1024B (B=Byte)

The difference caused by using different base (decimal vs binary) for the calculation

10^3 = 1000

2^10=1024

250GB -> 250 * 1024MB -> 250 * 1024 * 1024KB -> 250 * 1024 * 1024 * 1024Bytes

= 268,435,456,000

So 268,435,456,000 / 1000 / 1000 / 1000 = 268.435456 which is exactly the size shown on the portal.

This is also affect the representation of the current size that shown on the portal.

Update: 2016-08-04

I'm happy to share that the calculation is now fixed to use multiply of 1024 and shown as we expected:

Back to the original question:

My demo database set to max limit as 100MB

I created a table and fill it up with data.

The portal shows that I have reached the maximum size:

But if I am using the sp_spaceused I see that the database size is 220MB

Explanation:

220MB is the sum of data and log sizes.

You can see the sized with select [type_desc],[size] from
sys.database_files

For my 100MB database this is the results:

While 12800 is the number of 8K pages, which means 12800 * 8K = 102,400K

102,400K / 1024 = 100MB

For the log file size… while I took this screenshot the background process backup and shrink the log file so now you can see the size = 745 pages

745 * 8K = 5,960K -> 5,960K / 1024 = 5.8MB which is the initial size.

  • Note that you may be able to reduce the data size if you activate page compression.
  • It's recommended to maintain indexes and stats, unmaintained index might take more storage space because of non-full pages and fragmentation. (see out maintenance procedure here)
  • Do not use individual table size to sum the whole database size – you may get incorrect results if you have not maintained your indexes and you will not be calculating some internal object spaces.

Conclusions:

  • The portal shows only the data size.
  • Sp_Spaceused show the total size including the log file size.
  • You can use the reserved space shown on sp_spaceused to measure the current size.
  • The log file size is not calculated in the total limit for the database.

Keep enjoy using Azure SQL DB