Our current configuration consists of a database for the primary GP system tables, and one for all company data. This system has worked well in that past but lately the System DB has slowly began to grow. I can expand the partition but I would rather make sure the DB is "rightsized."
*This post is locked for comments
For future reference, in addition to the row count query that Richard provided, you can check the space used by each table in a database, perhaps giving you a better gauge of which table is consuming disk space.
Here is a thread that discusses two possible SQL queries to get a list of table sizes in a database:
stackoverflow.com/.../get-size-of-all-tables-in-database
I prefer the first solution:
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.total_pages) DESC
Thanks for the help. I was able to migrate the DB to another partition and resolve the issue.
How big is the DYNAMICS database now and how much has it grown in the past couple of months? Are you using Business Portal or any third party products that writes to the DYNAMICS database? You could start by downloading a SQL script that counts records in al tables and run it now and then in a couple of weeks and find which tables are growing. You can give this one a try.
DECLARE @sql nvarchar(MAX)
SELECT
@sql = COALESCE(@sql + ' UNION ALL ', '') +
'SELECT
''' + s.name + ''' AS ''Schema'',
''' + t.name + ''' AS ''Table'',
COUNT(*) AS Count
FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
FROM sys.schemas s
INNER JOIN sys.tables t ON t.schema_id = s.schema_id
ORDER BY
s.name,
t.name
EXEC(@sql)
#1
Almas Mahfooz
3
User Group Leader
Share
Report
All responses (
Answers (
