2

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

Improve this question

3 Answers 3

Reset to default
4

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'
Improve this answer
4
  • 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? Commented Jan 18, 2012 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? Commented Jan 18, 2012 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! Commented Jan 18, 2012 at 22:38
  • so was it a parition, or was it something else? Commented Jan 20, 2012 at 5:28
0

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.

Improve this answer
1
  • Thanks for the suggestion. Unfortunatley, the query doesn't return any records for the filegroup. Commented Jan 14, 2012 at 5:08
0

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.

Improve this answer
1
  • 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. Commented Jan 14, 2012 at 16:42

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.