Skip to main content
3 of 7
deleted 100 characters in body
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619

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 name
  FROM sys.partition_schemes 
  WHERE data_space_id = @fgid;
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619