Unable to drop file group because it is not empty

Question

I am unable to delete a filegroup. I am getting the standard error: "The filegroup '~~~~' cannot be removed because it is not empty." I have been searching the internet, and still cannot find the answer. The server version is Microsoft SQL Server 2019.

I have gone through both of these very thoroughly:

• Finding Data in File/Filegroup
• Cannot remove unused filegroups

Things that I have scanned to see if there is any association to the filegroup:

• files : 0
• allocation_units : 0
• indexes : 0
• partition schemes : 0
• partition functions : 0
• partitions : 0

I have also run DBCC CHECKFILEGROUP and it shows nothing as well. I am really very confused. Can someone help me figure out what else could be the thing keeping me from dropping this filegroup?

Full disclosure, I am using a module I wrote:

https://github.com/tcartwright/tcdbtools/blob/main/docs/Invoke-DBSafeShrink.md

The module performs the following steps:

• creates a temporary file and filegroup
• moves clustered indexes, non-clustered indexes, LOBS, and heaps to the new filegroup
• shrinks the original filegroup
• moves all of the original objects back to the origin filegroup
• removes the temporary file group

As part of moving the LOBs I am using this trick from Kimberly Tripp to move them: https://www.sqlskills.com/blogs/kimberly/understanding-lob-data-20082008r2-2012/

EDIT: I think I have figured out what is blocking the drop, but I cannot figure out how to fix it. This query:

SELECT * FROM sys.[tables] AS [t] WHERE [t].[lob_data_space_id] IN (
    SELECT [ds].[data_space_id] FROM sys.[data_spaces] AS [ds] WHERE [ds].[name] <> 'PRIMARY'
)

returns a table that does not have any associated LOB data in the allocation units. Nor can I figure out how to get rid of this. I have:

• dropped and recreated the PK for this table
• rebuilt all of the indexes on the table and

None of which helped.

Answer 1

I think there is an objects in that file group for sure, so, you may first want to know what it is, this script may help

after that changing location or dropping those objects will let you drop the FG

Answer 2

Ok, I finally figured it out. Before I go into the fix, let me theorize on what I think might have happened. I am using the partition trick to move LOB data from one FG to another. Combined with the fact that the part of the move process using CREATE INDEX (DROP_EXISTING = ON) causes the index to rebuild.

I think when the index rebuilt on the new FG that possibly all of the LOB data moved to in row eliminating the LOB_DATA allocation unit. However, the lob_dataspace_id remained set to the new FG after the move. Causing me not to be able to delete it.

When the code went to move it back, it did not see any LOB_DATA allocated, and did not use the partition trick to move the index back.

To fix this, I needed to move the index back to PRIMARY using the same partition trick.

I modified the code to also inspect the tables lob_dataspace_id to determine if the FG I am moving to is not the same. Then it will utilize the partition trick to move the index back, and caused the lob_dataspace_id to reset.

Once I moved the index back and forth to PRIMARY using the partition trick I was able to successfully delete the filegroup.