Take the 2-minute tour ×
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.

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
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

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.