THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Roman Rehak

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.


Published Thursday, April 26, 2007 5:19 PM by roman



Brian said:

Any idea what to do when this message is appearing when attempting to remove a file on a partitioned that has just had that partition merged?

January 14, 2008 11:01 AM

yaniv mishan said:

truncate with emptyfile option

May 5, 2009 9:48 AM
New Comments to this post are disabled
Privacy Statement