Cannot remove unused filegroups

Question

I wanted to remove some unused filegroups/files in a SQL Server Database but am stuck because SQL Server thinks the filegroups/files are still in use.

Some background:

• We had some partioned tables that we converted back to non-partitioned ones
• All partition-functions and -schemes were deleted

• I queried the dm views for unused filegroups like this:

  SELECT 
  *
  FROM sys.filegroups fg
  LEFT OUTER JOIN 
  sysfilegroups sfg
  ON fg.name = sfg.groupname
  LEFT OUTER JOIN 
  sysfiles f
  ON sfg.groupid = f.groupid
  LEFT OUTER JOIN 
  sys.indexes i
  ON fg.data_space_id = i.data_space_id
  WHERE i.object_id IS NULL
  

  This got me a list of filegroups/files i tried to remove from the database. But some of them could not be removed. Example error message:

  The filegroup 'FG_XXXX' cannot be removed because it is not empty.
  

• I was not able to get the connection to tables/indexes for these filegroups via sys.data_spaces and sys.indexes:

  SELECT * FROM  sys.data_spaces ds
  INNER JOIN sys.indexes i
  ON ds.data_space_id = i.data_space_id
  WHERE ds.name = 'FG_XXXX'
  

• I am aware that a filegroup can not be removed if it is referenced in some partition scheme.
  But this cannot be the reason here, as i deleted all partition schemes/functions in the db.

Any hints what i could do to get rid of the filegroups?

Answer 1

I actually found an easy solution to this fix since I had the same issue.

I was trying to remove the filegroup [fg_LMeterDetail_13] but got the "cannot be removed because it is not empty" error. This filegroup's only sin was being associated with a partition scheme. This filegroup had no data.

So after finding there's no opposite to NEXT USED, I experimented and found that you can issue another NEXT USED but pointing it to the previous filegroup:

ALTER PARTITION SCHEME ps_LMeterDetail
NEXT USED [fg_LMeterDetail_12]

After that, I was able to issue the REMOVE FILEGROUP which then automatically removed it from the partition scheme

ALTER DATABASE [WACOE] 
REMOVE FILEGROUP [fg_LMeterDetail_13]

Voila! It worked!

You're welcome everyone.

Answer 2

You may have some LOB data (text/image/varchar(max)/nvarchar(max)) sitting in the filegroup still. I got caught up by this briefly not too long ago. Rebuilding a table/index on a different filegroup/partition does not move any of the LOB data.

SELECT
    au.*,
    ds.name AS [data_space_name],
    ds.type AS [data_space_type],
    p.rows,
    o.name AS [object_name]
FROM sys.allocation_units au
    INNER JOIN sys.data_spaces ds
        ON au.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p
        ON au.container_id = p.partition_id
    INNER JOIN sys.objects o
        ON p.object_id = o.object_id
WHERE au.type_desc = 'LOB_DATA'

If that's indeed what's happening, the simplest way to move the LOB data is by recreating the table. You can move the data either with bcp out/in, or by inserting directly into a new table and renaming afterward (or by any other preferred method of moving the data you have). Make sure you specify the correct filegroups for row and text data when creating the new table.

Answer 3

Try using sys.allocation_units instead of sys.indexes. BOL says it's for internal use only so I wouldn't write any long term code on it but for this purpose it should be ok. It points to the first IAM page of each allocation unit. I believe it will work better for what you are trying to do.

SELECT *
FROM sys.filegroups fg
LEFT OUTER JOIN sysfilegroups sfg
    ON fg.name = sfg.groupname
LEFT OUTER JOIN sysfiles f
    ON sfg.groupid = f.groupid
LEFT OUTER JOIN sys.allocation_units i
    ON fg.data_space_id = i.data_space_id
WHERE i.data_space_id IS NULL

Also as @RobertGannon said you can right click on the name of your database, then go to tasks, shrink, files

You can then select the "Empty file by migrating the data to other files in the same filegroup" to get all of the data off of a given file. This won't help you get rid of a filegroup itself, but it will let you condense it down to a single file.

Answer 4

I was able to drop the filegroups after dropping partition schema and then dropping partition function.

DROP PARTITION SCHEME <<PartitionSchemeName>>;
DROP PARTITION FUNCTION <<PartitoinFunctionName>>;
ALTER DATABASE DBNAME
REMOVE FILEGROUP PartitionFG

Answer 5

The error is generic and misleading. You will also get this error when a PARTITION SCHEME has been assigned to the FileGroup you are trying to remove.

Delete the Partition Scheme that is using the FileGroup fixed the problem for me.

You can get a list of Partition Schemes like this:

SELECT * FROM sys.partition_schemes ps

Answer 6

Shrink the files and make sure they are not the default filegroup. Shrinking should allow you to verify that the file is empty using EMPTYFILE. SQL Server will not allow you to remove the default file group.

Answer 7

If you cannot remove the filegroup there must be an object residing within this filegroup. Objects can reside within a filegroup even when there is no file assigned to that filegroup. So you need to figure out which objects these are. Here are the querys to find the element(s).

select * from sys.data_spaces 
where name = 'DATA' --<--Name of the filegroup you want to delete

Your data space consist out of a number of allocation units:

select * from sys.allocation_units 
where data_space_id = 2 --<-- data_space_id from data_spaces 

All entries showing the "pages" should be 0. If there are pages, there must be a file within that filegroup. There are partitions:

select * from sys.partitions 
where partition_id in (72057597361061888) --<-- container_id(s) from allocation_units 

There are objects:

select * 
from sys.objects 
where object_id in (1754489329) --<-- object_id(s) from partitions 

Of course you can join all together and can save that query. I can never remember the keys so I just remember the hierarchy:

Data_spaces - Allocation_units - Partitions - Objects