The filegroup cannot be removed because it is not empty Error

Question

I have a file group within a database that has no logical files that cannot be removed. The error message states that the file group is not empty.

I have verified that no partitions exist using the queries below.

SELECT * FROM sys.partition_functions
SELECT * FROM SYS.PARTITION_RANGE_VALUES
SELECT * FROM SYS.PARTITION_SCHEMES

I have also run the queries below in a effort to find anything associated with the file group, but found nothing

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
ORDER BY ds.name;

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.allocation_units i
    ON fg.data_space_id = i.data_space_id
WHERE i.data_space_id IS NULL;

select * from sys.allocation_units 
where data_space_id = (select data_space_id from sys.data_spaces where name = 'HL7');

In addition, I have been able to backup the database and restore to a different SQL Server and duplicate this problem.

What else can be checked to find out what is preventing the file group from being removed?

I was able to identify the table that is associated with the file group I am trying to remove with the query below. `SELECT t.name FROM sys.tables t INNER JOIN sys.filegroups f ON t.lob_data_space_id = f.data_space_id WHERE f.name = N'HL7'` The table that was listed one had a varchar(MAX) column, but no longer has this. I have tried a REORGANIZE WITH (LOB_COMPACTION = ON), but it is still associated with the file group. Is there any way to remove the lob data without recreating the table and moving the data to the new table? — Robert, Mar 30 '21 at 12:43

score 1 · Accepted Answer · answered Apr 26 '21 at 13:32

I had a similar situation recently. I ran

DBCC SHRINKFILE (N'myfile', EMPTYFILE)

after which I was able to remove the file.

Frankly I didn't expect it to work, since in my case the filegroup has no other files in it. So even though the operation should

Migrates all data from the specified file to other files in the same filegroup

There was no file to migrate the data to. But it worked! No errors or warnings. Right after I removed the file without issue. Guessing that there was some messed up entries that bothered REMOVE FILE but not SHRINKFILE.

Give it a go and post your results here.

The filegroup cannot be removed because it is not empty Error

1 Answers1