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.
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.
report at partition and allocation unit level, rather than data file level.
averages database size over five minute intervals, and therefore does not consider the most recent changes in space usage.
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
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
WHERE type_desc = 'ROWS';