Trouble removing filestream file / filegroup My database was originally using filestreams in one table.  For reasons that I won't go into here, we decided to stop using filestreams.  I therefore wrote a migration to copy the filestreamed table into a new table that doesn't use filestream, I dropped the old table and renamed the new table.  All good so far.

Now I want to drop the filestream file and filegroup.  And SQL server won't let me:


  The file 'MyFilestream' cannot be removed because it is not empty.


I ran the following query:

select * from sys.tables t 
join sys.data_spaces ds on t.filestream_data_space_id = ds.data_space_id 
-- and ds.name = 'MyFSGroup'


Even commenting out the ds.name filter, I get zero records back.

Like this old, unanswered question, I ran this query:


  exec sp_helpfilegroup 'MyFSGroup'


and got back:

groupname    groupid    filecount
---------    -------    ---------
MyFSGroup    2          0


I've Googled all over for solutions and workarounds.  So far as I can tell, there is no table anywhere in the database that is still using this file - but I still cannot drop it.  I have tried restarting the SQL service, too, but it's not to be fooled.

How do I find out what is still using the filestream file, and how do I drop it?

EDIT: ooh, here's a thought: the database has "Full" recovery model.  Could that have something to do with it?
Indeed, that was the answer.  I set the db recovery model to "Simple", waited a minute for the filestream data to clear up, and then I could remove the filestream file and filegroup.