SQL Server 2008: Unable to remove a FileStream File and Filegroup

Question

With SQL Server 2008 SP1, I've removed the only table that had a Filestream associated with it but each time I attempt to do the following:

alter database ConsumerMarketingStore remove file CMS_JobInstanceFiles

alter database ConsumerMarketingStore remove filegroup JobInstanceFiles

I get the following exception:

Msg 5042, Level 16, State 10, Line 2 The file 'CMS_JobInstanceFiles' cannot be removed because it is not empty. Msg 5042, Level 16, State 11, Line 3 The filegroup 'JobInstanceFiles' cannot be removed because it is not empty.

How in the world do I get rid of the Filestream file and filegroup? Thanks!

Answer 1

Make sure the table you dropped is in fact the only table that's using that filestream file:

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

The result of the above query should be empty. If you had other tables with Filestream columns and say you dropped the columns, the table will still use the Filestream file. The way to get rid of this usage is to set table's Filestream filegroup to NULL:

alter table t1 set (filestream_on = "NULL")