I wanted to remove some unused filegroups/files in a SQL Server Database but am stuck because SQL Server thinks the filegroups/files are still in use.
- We had some partioned tables that we converted back to non-partitioned ones
- All partition-functions and -schemes were deleted
I queried the dm views for unused filegroups like this:
SELECT * FROM sys.filegroups fg LEFT OUTER JOIN sysfilegroups sfg ON fg.name = sfg.groupname LEFT OUTER JOIN sysfiles f ON sfg.groupid = f.groupid LEFT OUTER JOIN sys.indexes i ON fg.data_space_id = i.data_space_id WHERE i.object_id IS NULL
This got me a list of filegroups/files i tried to remove from the database. But some of them could not be removed. Example error message:
The filegroup 'FG_XXXX' cannot be removed because it is not empty.
I was not able to get the connection to tables/indexes for these filegroups via
SELECT * FROM sys.data_spaces ds INNER JOIN sys.indexes i ON ds.data_space_id = i.data_space_id WHERE ds.name = 'FG_XXXX'
I am aware that a filegroup can not be removed if it is referenced in some partition scheme.
But this cannot be the reason here, as i deleted all partition schemes/functions in the db.
Any hints what i could do to get rid of the filegroups?