Determining Database Size in Azure SQL Database V12

  • Article

A question that is frequently asked by customers using Azure SQL Database is “How can I determine the size of my database programmatically?” Interestingly, different people may be talking about different things when asking this question. Is it the size of all database files on disk? Is it the size of just the data files? Is it the size of used space in the database? Is it the total size of allocated and empty space in the database? Depending on the context, all these things may be the right answer to the question.

Today, if you do a web search on this topic, the most frequent answer to this question will point you to querying the sys.dm_db_partition_stats DMV, and looking at the reserved_page_count column. Other solutions involve querying sys.allocation_units and sys.resource_stats DMVs, or using sp_spaceused stored procedure.

In the context of Azure SQL Database, the measurement that most customers would be interested in is the size used by the Azure SQL Database service to govern the size of the database, i.e. the 161.29 GB that is shown in Azure Portal in this example:

clip_image002

This value is the total size of allocated extents in data files.

However, none of the methods mentioned earlier will accurately provide that measurement for V12 databases. sys.dm_db_partition_stats and sys.allocation_units report at partition and allocation unit level, rather than data file level. sys.resource_stats averages database size over five minute intervals, and therefore does not consider the most recent changes in space usage. sp_spaceused returns several size values, however the total size of allocated extents in data files, which is used by the service, is not one of them.

For V12 databases, the measurement we are interested in is determined using the sys.database_files DMV and the FILEPROPERTY function with the 'SpaceUsed' argument. Only ROWS files are considered. Log and XTP files are excluded for the purposes of determining database size.

The following statement is an example of the correct way to determine the size of an Azure SQL Database V12 database programmatically:

 SELECT SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) AS DatabaseSizeInBytes,
       SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 AS DatabaseSizeInMB,
       SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 / 1024 AS DatabaseSizeInGB
FROM sys.database_files
WHERE type_desc = 'ROWS';