Grasshopper you are a star! I have had the same problem on our production DB for weeks. Empty file, delete file, empty filegroup, "The filegroup 'UserData' cannot be removed because it is not empty.

"

Thanks to your script I have found a table that claims to have a PK on the old filegroup. Moving that to a new filegroup had no effect. Dropping the PK had no effect. Then (by adding ,* to the end of your script) I noticed that there was still a NULL entry for the table with "LOB_DATA" in the type_desc column.

There was indeed a column defined as nvarchar(max) and checking the maximum datalength in that column for the table, it showed me 2338 as the maximum used, so I changed the column to nvarchar(2400) instead, and without any other changes I was finally able to drop the empty filegroup. 😀

SSC-Journeyman, I hope this helps you too:

SELECT

TableName = OBJECT_NAME(p.object_id),

PartitionNo = p.partition_number,

FileGroup = FILEGROUP_NAME(a.data_space_id)

,IndexName = i.name

, *

FROM sys.allocation_units a

INNER JOIN sys.partitions p ON a.container_id = CASE WHEN a.type in(1,3) THEN p.hobt_id ELSE p.partition_id END AND p.object_id > 1024

LEFT JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id

ORDER BY FileGroup, TableName, p.partition_number

Now that just leaves me one more tricky filegroup to sort out - does anyone know why my empty PRIMARY filegroup is still taking up 18GB of disk space and will not shrink any further?!?

Cheers,

Greenius