We have migrated all our varbinary(max) column data to Azure Blob Storage and so we want to remove the old filestream columns that remain in our Sql 2008 database and the filestream filegroup but when we try to we are getting the error:

Msg 5042, Level 16, State 11, Line 2
The filegroup 'FileStreamGroup' cannot be removed because it is not empty.

However when we run this:

exec sp_helpfilegroup 'FileStreamGroup'

It is returning this:

groupname           groupid    filecount
FileStreamGroup     2          0

So the file count is 0 but it won't let us remove it, has anybody else had this problem and how on earth do you completely remove the filestream from the database.

In addition this query:

select * from sys.tables t 
join sys.data_spaces ds on t.filestream_data_space_id = ds.data_space_id 

Returns 0 rows so no tables are using any filestream data if I understand this correctly.


I had the same problem. Eventually I worked out that I needed to change the db recovery model from "Full" to "Simple", and then I could delete my filestream files.

| improve this answer | |