First published on MSDN on Sep 21, 2016
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:
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';