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.

  • If I'm not mistaken doing a backup against the database while in full should have caused the garbage process to clear your file stream so it could be dropped. This would prevent you having to break your backup chain. – user507 Jun 29 '15 at 8:23
  • @ShawnMelton That may well be so. But I can't test that theory now... :) – Shaul Behr Jun 29 '15 at 8:27
  • Was having this same issue. I had tried the backup while in full and it didn't clear the file steam at all. Only setting to simple worked. Thanks for this! had been searching high and low for a solution :) – Balah Jul 29 '16 at 12:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.