If it's saying the file is not empty, it's not empty. You need to figure out what's there and either rebuild it on another filegroup manually, or drop it. EMPTYFILE
only works on the last file if there are no objects at all, and of course this is not possible on the PRIMARY
filegroup.
My first guess is there's a table, index, or partition scheme still associated with this filegroup:
DECLARE @fgid int;
SELECT @fgid = data_space_id
FROM sys.filegroups
WHERE name = N'the_filegroup_name';
SELECT [object] = o.name, [index] = i.name
FROM sys.objects AS o
INNER JOIN sys.indexes AS i
ON o.[object_id] = i.[object_id]
WHERE i.data_space_id = @fgid;
SELECT ps.name
FROM sys.destination_data_spaces AS ds
INNER JOIN sys.partition_schemes AS ps
ON ds.partition_scheme_id = ps.data_space_id
WHERE ds.data_space_id = @fgid
GROUP BY ps.name;