SQL Azure: Calculate Database Usage Percentage of Max Size

Mar 10, 2013  • Database

Some times it can be useful to programmatically monitor your SQL Azure database usage statistics. Luckily, there is some short SQL code that can be run on the database to check how much disk space is currently used and what the currently allotted max database size is. The following script does just that.

DECLARE @dbName nvarchar(255) = '{database_name}';

DECLARE @Max BIGINT = CONVERT(BIGINT,
    (SELECT DATABASEPROPERTYEX(@dbName , 'MaxSizeInBytes')));
DECLARE @Used BIGINT = (
    SELECT TOP 1 SUM(reserved_page_count) * 8192
    FROM sys.dm_db_partition_stats);

SELECT @Used AS [Currently Used],
       @Max AS [Max Alloted],
       (CONVERT(FLOAT, @Used) / CONVERT(FLOAT, @Max) * 100) AS [Percent Used]

To use the above script, just change the string “{database_name}” to be the name of the database your running this on. Also, this script must be run directly on the database you are checking.

Also, here’s a sample of the output from this query in SQL Management Studio:

SQLAzure_CalcDBUsagePercentage