There was a recent forum post by a user who had noticed that his Dynamics GP system database was growing, and was apparently looking to understand how to monitor the growth.
I have dealt with this question before, but never remember the query, so I wanted to post it here for future reference.
My approach is to run a query that shows the number of records and the size of each database table in the database. Usually, most of the database is utilized by a handful of tables, so once you find your top 5 or 10 tables, you'll quickly understand what type of data is consuming the space in your database.
I found this Stack Overflow thread that addresses the issue and offers a few queries. I prefer the query that was most upvoted because its result set is in a single clean set that can be copied and pasted into Excel.
The query lists I have modified the Order By clause to sort the largest tables to the top.
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
sys.indexes i ON t.OBJECT_ID = i.object_id
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
t.Name, s.Name, p.Rows
And the results look like this: