How full is my Windows Azure SQL Database?

While the level of compatibility of Windows Azure SQL Databases is high, there are a number of things that need to be dealt with differently, compared with how they are done with on-premises SQL Server.

An example of this, today I needed to know how full one of my databases was. I wanted to know how much space I had used but also to know what the limit was. My first attempt was the usual system views such as:

SELECT * FROM sys.database_files;

But in the Azure environment, that returns:

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.database_files’.

So that wasn’t going to help. I had also tried the new sys.resource_stats and sys.resource_usage views also to no avail. There is a good article that provides details of which views do and don’t work on which versions here. Conor Cunningham also posted early last year about how some of these are supposed to work. (For more info on Conor and Windows Azure SQL Database in general, see the podcast that I recorded with him recently).

Michael Wood pointed me to a post from Ryan Dunn that showed how to get the used size in total, and for each database object. (For more info on Ryan, see the SQL Down Under podcast that I recorded with him back when SQL Data Services first appeared). Tom LaRock also explained that in his post here. The view that I needed to use was the sys.dm_db_partition_stats view.

For an overall space usage total, the following query helps:

SELECT SUM(reserved_page_count) * 8.0/1024 AS DatabaseMB
FROM sys.dm_db_partition_stats;

For a breakdown by individual object, the following query (a tidied up version of what I’ve found in the posts) helps:

SELECT o.name AS ObjectName,        SUM(reserved_page_count) * 8.0 / 1024 AS SizeinMB
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.sysobjects AS o
ON ps.object_id = o.id
GROUP BY o.name
ORDER BY SizeinMB DESC;

There is also a really good Azure article here that describes the use of these along with info on bandwidth monitoring, etc.

But the final thing I wanted to know is what the limit was for a given database. Sanjay Nagamangalam (from the SQL Server team) came to the rescue by pointing out a post from Walter Berry. It mentions that you can get the maximum size from an extended database property. The query below that I’ve adapted from it, shows the current limit:

SELECT CAST(DATABASEPROPERTYEX(‘PopkornKraze_DW’ , ‘MaxSizeInBytes’) AS integer)        / 1024 / 1024 AS DatabaseLimitInMB;

(Note that PopkornKraze_DW was the name of my database in this test). The final thing that I might need is a query that shows how full my database is as a percentage, so if we combine them, it’s just:

SELECT CAST((SELECT SUM(reserved_page_count) * 8.0 / 1024
             FROM sys.dm_db_partition_stats) * 100
            / (SELECT CAST(DATABASEPROPERTYEX(‘PopkornKraze_DW’ , ‘MaxSizeInBytes’) AS integer)
            / 1024 / 1024) AS decimal(10,2)) AS PercentageUsage;

If you do happen to exceed the size limit, SQL Exception 40544 is thrown.

You can modify the database size (and edition) by executing:

ALTER DATABASE PopkornKraze_DW MODIFY (EDITION=‘BUSINESS’, MAXSIZE=10GB);

You appear to be able to change the size while others are connected but changing the edition terminates existing connections.

Hope that helps someone.

2013-01-03