Cannot remove unused filegroups

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.

Some background:

• 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 sys.data_spaces and sys.indexes:

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?

You may have some LOB data (text/image/varchar(max)/nvarchar(max)) sitting in the filegroup still. I got caught up by this briefly not too long ago. Rebuilding a table/index on a different filegroup/partition does not move any of the LOB data.

SELECT
    au.*,
    ds.name AS [data_space_name],
    ds.type AS [data_space_type],
    p.rows,
    o.name AS [object_name]
FROM sys.allocation_units au
    INNER JOIN sys.data_spaces ds
        ON au.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p
        ON au.container_id = p.partition_id
    INNER JOIN sys.objects o
        ON p.object_id = o.object_id
WHERE au.type_desc = 'LOB_DATA'

If that's indeed what's happening, the simplest way to move the LOB data is by recreating the table. You can move the data either with bcp out/in, or by inserting directly into a new table and renaming afterward (or by any other preferred method of moving the data you have). Make sure you specify the correct filegroups for row and text data when creating the new table.

StackExchange Database Administrators Q#47217, answer score: 9