Get largest tables (by size in pages/mb) in use by AX (2012 R2)

Recently we saw a huge growth on our database overnight and started to get curious what caused it. By using the query below we were table to track it to a specific table thus tracking it to a specific feature within AX. The following query will show you the largest tables in your database for AX which can then help you track down what might need to do be done.

*Note: this should be ran under a query within your data-side database for AX.


SELECT
tables.NAME AS TableName,
indexes.name as IndexName,
sum(partitions.rows) as NumberOfRows,
sum(allocation_units.total_pages) as TotalPages,
sum(allocation_units.used_pages) as UsedPages,
sum(allocation_units.data_pages) as DataPages,
(sum(allocation_units.total_pages) * 8) / 1024 as TotalSizeMB,
(sum(allocation_units.used_pages) * 8) / 1024 as UsedSizeMB,
(sum(allocation_units.data_pages) * 8) / 1024 as DataSizeMB
FROM
sys.tables
INNER JOIN     
sys.indexes ON tables.OBJECT_ID = indexes.object_id
INNER JOIN
sys.partitions ON indexes.object_id = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
INNER JOIN
sys.allocation_units ON partitions.partition_id = allocation_units.container_id
GROUP BY
tables.NAME, indexes.object_id, indexes.index_id, indexes.name
ORDER BY
TotalSizeMB DESC