How to get Dynamics 365 Finance and Operations table sizes

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.

 

How to get Dynamics 365 Finance and Operations table sizes