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';

Comments

  • Anonymous
    December 12, 2016
    How does this method compare with 'EXEC sp_helpfile'?For example, I have an Azure SQL database for which the above query reports 242GB, but sp_helpfile reports the data file as 950GB(!). There was a lot of data in archive tables etc. which has been removed and the reported size reduced markedly, and this is the number shown in the Azure Portal.It appears however that Azure looks at the 950GB sizing when adjusting pricing tiers, as we could not drop the tier to a level with a max size of 500GB as the DB was apparently larger than that.
  • Anonymous
    December 19, 2016
    sp_helpfile reports the size of each file on disk, while the query above reports the size of allocated extents in each file.The most likely reason for not being able to scale down in this scenario is deferred deallocation. Even though the data has been logically removed, physical deallocation of extents can take a significant amount of time after the drop/truncate transaction has completed. See https://msdn.microsoft.com/en-us/library/ms177495(v=sql.105).aspx. You can monitor the deferred deallocation process by looking at the allocated_extent_page_count column in sys.dm_db_file_space_usage DMV, which should be going down over time.
  • Anonymous
    March 12, 2017
    Another way to get thi informations, if querying the database is not an option, would be to use Azure Api Management REST api, especially the following endpoint:https://management.azure.com/subscriptions//resourceGroups//providers/Microsoft.Sql/servers//databases//usages?api-version=2014-04-01-previewThe result is a json that looks like the following:name : database_sizeresourceName : displayName : Database SizecurrentValue : 2324692992.0limit : 268435456000.0unit : BytesnextResetTime :
  • Anonymous
    April 11, 2017
    hi, is there a way to get a database size using powershell ?
    • Anonymous
      April 20, 2017
      Not directly. You can use PowerShell to invoke the REST API as mentioned by @Louliloul. A walkthough for using REST API in PowerShell is here. Or you can use .Net code in PowerShell to open an ADO.NET connection to the database and query sys.database_files as described in the blog.