Calculate SQL Azure Database Size

I was looking out for a correct way to programmatically determine the size of a SQL Azure database. After searching many solutions, I finally found one shared by Dimitri Furman of the SQL Server team.

His solution involves using the sys.database_files dmv and the FILEPROPERTY function with the ‘SpaceUsed’ argument.

To those new to sys.database_files, this system catalog view stores information and properties about each file for a database. Since it is a db-level view, it gives information about files in the current database only. Five properties that could be of interest are: logical filename, physical filename, initial size, maximum size and a growth increment.

To determine how much space is used in a file, you can use FILEPROPERTY with SpaceUsed.

Here’s an example: SELECT FILEPROPERTY(‘SomeFile’, ‘SpaceUsed’); I have often used the FILEPROPERTY function in the past while monitoring the progress of a SHRINK operation.

Query for calculating Size of a SQL Azure Database

Here’s how to combine sys.database_files with FILEPROPERTY to programmatically calculate the size of a SQL Azure database.

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

Let’s understand this query.

FILEPROPERTY() returns an int value for a file name stored within sys.database_files. Since sys.database_files is a db-level view, it gives information about files in the current database only. If a file is not present, null value is returned.

Since SpaceUsed represents "pages" and a page is 8 KB in SQL Server, so multiplying by 8192 gets the total bytes. Then dividing two times by 1024 converts the output to MB, and dividing by three times by 1024 converts the output to GB.

CAST is for casting the value to type bigint

If anybody is wondering about the dot (.) after an 8192, then it is to convert the result implicitly to a decimal value.

Please note that logs are excluded for the purposes of determining database size.

To know about Azure SQL Database resource limits, check https://docs.microsoft.com/en-in/azure/sql-database/sql-database-resource-limits


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

No comments: