Thursday, January 28, 2010 8:01 PMHas 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:
REMOVE FILE fs_filestream
Msg 5042, Level 16, State 10, Line 1
The file 'fs_filestream' cannot be removed because it is not empty.
Thursday, January 28, 2010 11:01 PMAnswerer
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.
Friday, January 29, 2010 7:59 AMModeratorHere'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
- Marked As Answer by Xiao-Min Tan – MSFTModerator Friday, February 05, 2010 3:09 AM
Friday, April 27, 2012 2:21 PM
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
- Edited by chris1r123 Friday, April 27, 2012 2:21 PM