Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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".

share|improve this question

3 Answers 3

up vote 2 down vote accepted

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'
share|improve this answer
    
Yes, I get one record when running this query. It says: type_desc: LOB_DATA, total_pages: 0, type: FG, type_desc: ROWS_FILEGROUP, is_default: 0, rows:128016.... so on. Is there a way to get rid of this? –  Mindy Jan 18 '12 at 16:59
    
There are no Partition Schemes or Partition Functions listed for this database in SQL Server manager. When I select OBJECT_DEFINITION for the partition referenced in this query (by id) I get NULL in return. The same result occurs for the sa login. There have never been any partitions created for the database that I know of... How do I find this object to get rid of it? –  Mindy Jan 18 '12 at 22:11
    
I found the object by calling OBJECT_NAME(id) with the id returned by sys.partitions for the filegroup. It referenced the name of a table. When I looked at the table I found that the "Text/Image Filegroup" was set to "MYFILEGROUP". I modified this value and I was able to delete the filegroup!! Thank you so much for your help! –  Mindy Jan 18 '12 at 22:38
    
so was it a parition, or was it something else? –  Nick Kavadias Jan 20 '12 at 5:28

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.

share|improve this answer
    
Thanks for the suggestion. Unfortunatley, the query doesn't return any records for the filegroup. –  Mindy Jan 14 '12 at 5:08

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.

share|improve this answer
    
I ran a full backup and then tried to remove the filegroup and I got the same error. I tried to restore the backup and remove the filegroup from the restored database but that did not work either. –  Mindy Jan 14 '12 at 16:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.