Musings of a Data professional

Stuart Moore

SQL Server, getting the size of table partitions with T-SQL

Needed to quickly tell someone how large the partitions on a CRM 2011 auditbase table were (neither of us had access to the nice CRM GUI to answer it (ah they joys of not being trusted!)). So if anyone else needs some thing similar this is what I came up with:

SELECT
part.partition_number,
sum(alloc.total_pages/128) AS TotalTableSizeInMB,
sum(alloc.used_pages/128) AS UsedSizeInMB,
sum(alloc.data_pages/128) AS DataSizeInMB
FROM sys.allocation_units AS alloc
INNER JOIN sys.partitions AS part ON alloc.container_id = CASE WHEN alloc.type in(1,3) THEN part.hobt_id ELSE part.partition_id END
LEFT JOIN sys.indexes AS idx ON idx.object_id = part.object_id AND idx.index_id = part.index_id
where part.object_id = object_id('auditbase')
group by partition_number

If you compare the numbers with thos provided by CRM you’ll notice a differece:
crm-part-size-sql

crm-part-size-crm

And that’s just because Dynamics CRM doesn’t include the space the indexes take up.

Previous

New-AzureRmAutomationCredential returning “Input string was not in a correct format”

Next

Deleting Large amounts of data in SQL Server – Part 2

2 Comments

  1. mahesh

    Msg 4104, Level 16, State 1, Line 7
    The multi-part identifier “AU.container_id” could not be bound.

    • Stuart Moore

      wow, thank you for finding a 4 year old typo! Must have gotten confused, it should have been alloc.container_id

      When the code is just for me I keep the aliases short, so I didn’t completely swap them all out before i published

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress & Theme by Anders Norén