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

In addition to removing the FileStream columns and FileStream attribute from the table, I need to set the database recovery mode to Simple.

ALTER DATABASE [MyDatabase] SET RECOVERY Simple 
GO

EXEC SP_FILESTREAM_FORCE_GARBAGE_COLLECTION 

ALTER DATABASE [MyDatabase] REMOVE FILE [MyFile]
GO
ALTER DATABASE [MyDatabase] REMOVE FILEGROUP [MyFileGroup]
GO

ALTER DATABASE [MyDatabase] SET RECOVERY FULL
GO

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

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?