Return to Answer

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'smoochies';

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
    OR i.data_space_id IN 
    (
      SELECT partition_scheme_id
        FROM sys.destination_data_spaces AS ds
        WHERE 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;

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'smoochies';

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
    OR i.data_space_id IN 
    (
      SELECT partition_scheme_id
        FROM sys.destination_data_spaces AS ds
        WHERE 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;

When you find the thing (it could be an index on an empty table, an empty heap, or just a partition scheme still referencing the filegroup you want to remove), you'll have to drop / rebuild / shift objects to a new partition scheme. If the partition currently referenced is the "permanent" partition (first in RANGE RIGHT or last in RANGE LEFT), see this answer.

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;

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'smoochies';

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
    OR i.data_space_id IN 
    (
      SELECT partition_scheme_id
        FROM sys.destination_data_spaces AS ds
        WHERE 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;

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.destination_id = @fgid;

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;