GET THE SIZE OF DYNAMICS 365 TABLES FOR ON PREMISES ORGANIZATION

In previous post, we saw how to get the size of Dynamics 365 / Dataverse tables for an online environment

In this post, we will see how to get the size of Dynamics 365 tables for an on premises organization.

In order to check the tables size, you need to open the SQL server Management Studio and execute the below query

1. In the Management Studio, click New Query or expand the Databases list and right click the needed database > New Query
2. In the query editor, execute the below query by putting the correct database name
   
   USE <DatabaseName>
   GO
   
   SELECT
   t.Name AS TableName,
   s.Name AS SchemaName,
   p.Rows AS RowCounts,
   SUM(a.total_pages) * 8 AS TotalSpaceKB,
   SUM(a.used_pages) * 8 AS UsedSpaceKB,
   (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
   FROM
   sys.tables t
   INNER JOIN sys.indexes i ON t.object_id = i.object_id
   INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
   INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
   LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
   WHERE t.Name NOT LIKE 'dt%'
   AND t.is_ms_shipped = 0
   AND i.object_id > 255
   GROUP BY t.Name, s.Name, p.Rows
   ORDER BY SUM(a.used_pages) * 8 DESC;
   GO
   
   
3. Once the query is executed, you will get the list of tables sorted by its used space
   
   
   
4. This query can give you insights in case you are facing some performance issues in your environment where it might be related to the tables size

Hope This Helps!