Problems when removing a filegroup
This week we ran into a weird problem. We were not able to remove an empty filegroup, SQL Server was throwing the 5042 error “The filegroup 'FG18' cannot be removed because it is not empty”. There were definitely no files in that filegroup so the whole thing appeared to be more like a bug. As it turns out, 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.
Cross-posted from SQLBlog! -
http://www.sqlblog.com