GET THE SIZE OF DYNAMICS 365 TABLES FOR O PREMISES ORGANIZATION

Published Sunday, September 4, 2022

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

In the Management Studio, click New Query or expand the Databases list and right click the needed database > New Query
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
Once the query is executed, you will get the list of tables sorted by its used space
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