How to get Dynamics 365 Finance and Operations table sizes ? How big is my table in F&O DB?
Rather easy question to answer in good old Ax - from MS SQL Server Management Studio > right click on DB > Reports > Standard Reports > Disk Usage by Top Tables.
But not so fast in Dynamics 365 F&O!
MS nicely allow access to a Tier-2 DB in non-productive environments (request access in LCS) and we can work with it using the MS SQL Server Management Studio. However, DB now is Azure SQL DB and it does not allow MSSMS reports.
You can retrieve information about table row count and size with such query:
select S.name+ '.'+ T.name as [table] , (select SUM(rows) from sys.partitions where object_id = tmp.object_id and index_id in (1, 0) ) AS [rows] , data_MB, index_MB, allocated_MB, unused_MB from (select part.object_id ,cast(sum(alloc.data_pages* 8)/ 1024.00 as numeric(16, 2)) as data_MB ,cast(sum((alloc.used_pages- alloc.data_pages)* 8)/ 1024.00 as numeric(16, 2)) as index_MB ,cast(sum(alloc.total_pages* 8)/ 1024.00 as numeric(16, 2)) as allocated_MB ,cast(sum((alloc.total_pages - alloc.used_pages)* 8)/ 1024.00 as numeric(16, 2)) as unused_MB from sys.partitions part join sys.allocation_units alloc on part.partition_id = alloc.container_id group by part.object_id) as tmp join sys.tables T on T.object_id = tmp.object_id join sys.schemas AS S on S.schema_id = T.schema_id where T.type = 'U' --not counting system tables --and T.name like '%ledger%' --table name filter --and S.name <> 'dbo' --checking for non DBO schema order by allocated_MB desc
Note table name filter in "where" conditions.
Another note. That this unfortunately does not work for Dataverse DB connections (TDS feature) - it simply does not expose required system tables.