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

You have to run DBCC SHRINKFILE (CMS_JobInstanceFiles, EMPTYFILE)

This will flag the file as "empty" and allow it to be dropped.

Of course, ALTER DATABASE does not mention this, but DBCC SHRINKFILE does... obvious, eh?

Answer 2

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")

Answer 3

After you drop the table, the garbage collector takes a while to clean up the data. That could be the reason for this happening. You can force garbage collection by issuing a CHECKPOINT.

You can verify whether the FILESTREAM data is cleaned up by going to the Filestream data container. If the FILESTREAM data is deleted an "ALTER DATABASE dbname REMOVE FILE" will usually succeed. Once it is done, you can issue "ALTER DATABASE dbname REMOVE FILEGROUP".