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
1 comment:
you can also use azure cmdlets to get the database size and space use details.
I have posted on the same lines. http://www.sqlservercentral.com/articles/Azure/157249/
Post a Comment