13

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!

James Alexander
  • 6,132
  • 10
  • 42
  • 56

5 Answers5

13

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")
Pawel Marciniak
  • 2,208
  • 14
  • 17
  • You are an amazing man. After days of MS forums, talking to folks at MS, and SO, you're the first one to provide something useful and the answer. I had a table that at one point did have a file column but I removed it. That was why I was running into issues. Thank you so much. – James Alexander Feb 16 '10 at 19:57
  • FWIW, I used this to remove filestream from all tables. It threw an error listing any tables that still had filestream fields, which was useful for purging deprecated filestream fields. – Brent Keller Jan 17 '14 at 17:26
  • This got left out of my above comment: EXEC sp_msforeachtable "Alter Table ? Set (FILESTREAM_ON='NULL')" – Brent Keller Jan 17 '14 at 17:36
2

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
Kye
  • 5,919
  • 10
  • 49
  • 84
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?

gbn
  • 422,506
  • 82
  • 585
  • 676
0

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.

GDP
  • 8,109
  • 6
  • 45
  • 82
Jacob
  • 1
  • Jacob, AFAIK the data need not to be garbage collected in order to drop the filestream file. What's necessary is that the file is not being used by any table (see my reply). Also, note that CHECKPOINT will force GC only under a simple recovery model and it will still only force a single invocation of GC, which may not collect all eligible files. – Pawel Marciniak Feb 17 '10 at 17:47
0

@inam - my situation was a bit different; I created a second filegroup and moved a clustered index to it. Thereafter I deleted the clustered index in the secondary file group. After that I tried to delete the filegroup, but I kept getting the error, the file group is not empty.

I saw some other posts that indicated to move the data to another file within the same filegroup. The problem here was I only had one file in the file group, and I wanted to delete the filegroup and file in it.

The workaround I came up with is as follows. I copied all the records from table1 into table2 (select * into table2 from table1)

I then deleted table1; thereafter I was able to delete the filegroup2 and the data file in it.

Last I renamed table2 to table1.

inam
  • 1