Removing a filestream file group

Question

Has anyone figure out how to remove a filestream file group?
I just want to disable and remove all filestream stuff from my db instance.  I've already removed the varbinary(max) columns that used the filestream.
I've tried to remove the file but I get this error:

ALTER

 

DATABASE [myDB]
REMOVE FILE fs_filestream

Msg 5042, Level 16, State 10, Line 1
The file 'fs_filestream' cannot be removed because it is not empty.

Answer 1

How did you empty your file in first place? Did you use DBCC shrinkfile with EMPTYFILE option?

However,I would think there are still something residing in the file ,Run the following query and figure out what is stopping you from deleting the specific file which you think is empty.

 

select object_name(id) AS TableName, * from dbo.sysindexes where groupid = 'provide id of your filegroup in integer'

 

Then we would have to drop those before removing the file.

 

---

Thanks, Leks

Answer 2

Here's how I remove filestream from the Adentureworks2008 demo database. First drop the table that uses filestream, then the file, then the filegroup:

DROP TABLE Adventureworks2008.Production.ProductDocument
DROP TABLE Adventureworks2008.Production.Document
ALTER DATABASE Adventureworks2008 REMOVE FILE  FileStreamDocuments
ALTER DATABASE Adventureworks2008 REMOVE FILEGROUP  DocumentFileStreamGroup

---

Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi

Answer 3

I had the same problem and did not find all tables with filestream-colums or FK.

Here is the query to find all tables with filestream-columns:

select filestream_data_space_id, * from Adventureworks2008.sys.tables where filestream_data_space_id is not null