THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement