Filegroup cannot be removed because it is not empty error with no files

Question

When I try to remove 'MYFILEGROUP', I get the error that the filegroup cannot be removed because it is not empty. I already removed all tables, indexes and deleted all files associated with the group. To verify I ran the scripts below:

sp_helpfilegroup

returns 0 for the filecount on 'MYFILEGROUP'.

select o.name, s.groupname
from sysobjects o
join sysindexes i on o.id = i.id
join sysfilegroups s on i.groupid = s.groupid
where groupname = 'MYFILEGROUP'

returns no rows ...

I have some more information. When I run

dbcc checkfilegroup('MYFILEGROUP')

The result lists all of the primary keys with a warning stating:

Cannot process rowset ID 72057597605511168 of object "TableName" (ID 2071414),
index "PK_TableName" (ID 1), because it resides on filegroup "PRIMARY" (ID 1),
which was not checked.

Is this expected behavior or does it indicate a problem with the system tables? If it is a problem then how would I fix it? I removed every foriegn key, index and constraint from the database leaving only the tables. It still errors when I try to remove the empty "MYFILEGROUP".

Answer 1

is it possible MYFILEGROUP has a parition object on it? If this is the case then the compatability views your using will not return any results (sql server 2000 did not have partition objects!). The message your getting about skipping the object to check is expected behaviour for 2008 when checkdb checks a paritioned table/index , see this blog post. Do you get anything with this query?

select * from sys.partitions p
inner join sys.allocation_units a on a.container_id = p.hobt_id
inner join sys.filegroups f on f.data_space_id = a.data_space_id
where f.name='myfilegroup'

Answer 2

This can happen if a table that was using the filegroup had statistics defined on that filegroup. You can tell if you have any dangling statistics for that filegroup if you run this query and replace X with your filegroup ID:

select object_name(id) AS TableName, * from dbo.sysindexes where groupid = X

Once you know the table name, you can run DROP STATISTICS and hopefully after that you should be able to remove the filegroup.

Answer 3

Something in the back of my mind tells me that you need to do a backup between removing the file and removing the filegroup. Also, when I've done this before, I've seen a mismatch between sys.database_files and sys.master_files (i.e. one of the views had the file in it, the other didn't), but at least that tells you that the server is still aware of the file in some capacity. Either way, take a backup )or let your regularly scheduled one happen) and then try removing the filegroup again.