How do I track why my SQL Azure occupied space pulsates?

This is inspired by this StackOverflow question. I have this code:

SELECT CONVERT(INT, SUM(reserved_page_count) * 8 / 1024) FROM sys.dm_db_partition_stats

for detecting database space consumption and it returns exactly the same result as SQL Azure management portal pie chart. The problem is the number fluctuates greatly without apparent reasons – sometimes it’s around 35% and sometimes it’s around 54%.

This is kinda worrying – I don’t get what happens and the major concern is that we suddenly consume all the space and our service comes to a halt.

I seriously tried to look into sys.dm_db_partition_stats and sys.partitions but they look like a pile of magic numbers to me.

What data should I use to find the root cause of the fluctuations?

I’d have you look at tables and indexes with the following code:

SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as [SizeinMB]
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
ORDER BY [SizeinMB] DESC

You will want to check the size of the objects at regular intervals to see if you can determine which ones are in flux.

I would start by looking at the tables individually to see if this is caused by a single table or if it is a global phenomenon. You can use this query for that:

SELECT 
  OBJECT_SCHEMA_NAME(ps.object_id)+'.'+OBJECT_NAME(ps.object_id) Tbl,
  SUM(ps.used_page_count) AS used_page_count,
  SUM(ps.reserved_page_count) AS reserved_page_count,
  SUM(CASE WHEN ps.index_id<2 THEN ps.row_count ELSE 0 END) AS row_count
FROM sys.dm_db_partition_stats ps
JOIN sys.tables t
ON ps.object_id = t.object_id
GROUP BY ps.object_id
ORDER BY reserved_page_count DESC;

Store the results either in a new table or in a spreadsheet and compare over time.

If it is caused by a single table (or a small group of tables) you should check if this is expected behavior. This could happen for example if besides of inserts that are also deletes (think regular purging) executed against that table.