Check SQL Azure Database Size

  I was looking at my SQL Azure Database and suddenly I saw that I had to clean some space because it was already beyond the maximum quota that I had defined for the Database, but I needed to know exactly where the most space was being used in order to better understand not only what was the size, but why and where was the space being used.

  So my first action was, Ok so I’ll use the storeprocedure sp_spaceused.

Exec sp_spaceused

  So with this I know exactly the space that my database is using, and if I use this:

Exec sp_spaceused <TableName>

   I’ll get the space that is being used by the Table that I specify.

   Ok. So I tried this on my local SQL Server and all was good, and so off to SQL Azure, and I get the following error:

Could not find stored procedure 'sp_spaceused'.

   So. Not good. How will I get the needed results? So I used the following statement:

select sum(reserved_page_count) * 8.0 / 1024
from sys.dm_db_partition_stats

  And this statement gives me the exact size that I have in my SQLAzure Database, but I need to know exactly how much space I have in each table, so I use the following statement:

select sys.objects.name, sum(reserved_page_count) * 8.0 / 1024
from sys.dm_db_partition_stats,
     sys.objects
where sys.dm_db_partition_stats.object_id = sys.objects.object_id
group by sys.objects.name

   This last statement gives me a list of all tables that exist in the specified SQL Azure Database and the exact size that they have.

   I used this based on Ryan Dunn’s blog post available here.

   Hope this helps you.