Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
Even if you fill the database, you'll just get pushed to the next bigger size for billing. The only time this should really be a problem is if you are at the largest size database. – mrdenny Jul 6 '14 at 4:25

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

SELECT, 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

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

share|improve this answer

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:

  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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.