Why is PRIMARY Filegroup growing?

Question

Running SQL Server 2019 Enterprise. I have a PRIMARY Filegroup, but also added four ndf files in a Data1 Filegroup I created. As expected, I've had to increase available space on the Data1 Filegroup as more data gets added, but the mdf PRIMARY Filegroup has stayed relatively static in size. In the last couple of months I've had to increase space in the PRIMARY Filegroup. It started at 5GB and I expanded to 10GB a few weeks ago. Ran out of room again and just bumped to 15GB. The Data1 Filegroup is the default filegroup. Why would the PRIMARY filegroup suddenly be growing now when it is not the default? Each of the Data1 files are set to 160GB so PRIMARY is still considerably smaller. Just wondering if there is a way to determine why it is unexpectedly growing when it was stagnant in the past.

Answer 1

There are several potential steps you can take to determine why the PRIMARY filegroup in your SQL Server setup might be growing unexpectedly:

• Confirm that all new tables or large objects haven’t been unintentionally created in the PRIMARY filegroup. You can run this query:

SELECT
    o.name AS ObjectName,
    i.name AS IndexName,
    fg.name AS FileGroupName
FROM
    sys.indexes i
INNER JOIN
    sys.filegroups fg ON i.data_space_id = fg.data_space_id
INNER JOIN
    sys.objects o ON i.object_id = o.object_id
WHERE
    o.type = 'U'
ORDER BY
    FileGroupName, ObjectName;

• Confirm that all indexes haven’t been created in the PRIMARY filegroup. You can run this query:

SELECT
    i.name AS IndexName,
    o.name AS TableName,
    fg.name AS FileGroupName
FROM
    sys.indexes i
INNER JOIN
    sys.filegroups fg ON i.data_space_id = fg.data_space_id
INNER JOIN
    sys.objects o ON i.object_id = o.object_id
WHERE
    i.type_desc = 'NONCLUSTERED' -- or 'CLUSTERED', depending on the index type
ORDER BY
    FileGroupName, TableName, IndexName;

• Check on the growth of the system and internal tables. Use the following query to check that:

SELECT
    t.name AS TableName,
    s.name AS SchemaName,
    p.rows AS RowCounts,
    fg.name AS FileGroupName
FROM
    sys.tables t
INNER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN
    sys.partitions p ON t.object_id = p.object_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN
    sys.filegroups fg ON a.data_space_id = fg.data_space_id
WHERE
    fg.name = 'PRIMARY'
ORDER BY
    p.rows DESC;

• Review any recent changes to the schema, as schema changes can grow some system metadata tables. You can check using this query:

SELECT
    OBJECT_NAME(object_id) AS TableName,
    SUM(reserved_page_count) * 8.0 / 1024 AS SpaceUsedMB
FROM
    sys.dm_db_partition_stats
WHERE
    OBJECT_NAME(object_id) LIKE 'sys%'
GROUP BY
    OBJECT_NAME(object_id)
ORDER BY
    SpaceUsedMB DESC;

You could also check the growth of full-text indexes due to querying. This is if you have full-text indexing enabled.

You can also keep an eye on large transactions and transaction log growth.

If this doesn’t shed any light, you might need to perform a more in-depth analysis of your database's activity and growth patterns.