Unable to Remove Filestream Filegroup and Associated Database File
Hi folks, I had 5 FileTables which I have since converted to regular tables with varbinary(max) columns instead, migrated all the data from the filetables to the new tables and dropped the filetables completely. I ran the sp_filestream_force_garbage_collection process and dbcc ShrinkFile (file_stream_name, EmptyFile) for the filestream, the filestream and file associated is currently showing 0 mb in size, however I still cannot remove the files associated with the filestream. The error message I receive is: "The file 'file_stream_name' cannot be removed because it is not empty." when I attempt to drop the filestream's filegroup and/or the file associated with the filegroup. The filesize currently shows 0 MB in size, however it still complains about it not being empty. I'm using Microsoft SQL Server 2012 Standard Edition, however the error happens on Enterprise Edition also. Any help would be greatly appreciated. Kind regards, Michael Murray
Thank you for your time and effort, unfortunately the following: > EXEC sp_msforeachtable "Alter Table ? Set (FILESTREAM_ON='NULL')" resulted in "Command(s) completed successfully." and I still cannot remove the files :(
> Are you sure that there are no other tables using the filestream files? Let's try: select * from [databarn_FStream].sys.tables t join [databarn_FStream].sys.data_spaces ds on t.filestream_data_space_id = ds.data_space_id and ds.name = 'JobInstanceFiles' Empty resultset ![alt text] : /storage/temp/3368-query-4.png
> ALTER TABLE SET (FILESTREAM_ON = "NULL") go The filetables have been completely removed so there isn't anything to run this on now > ALTER DATABASE Databarn REMOVE FILE [databarn_FStream] I get: Msg 5042, Level 16, State 13, Line 21 The file 'databarn_FStream' cannot be removed because it is not empty. > ALTER DATABASE Databarn REMOVE FILEGROUP [DataBarn_FSGroup] I get: Msg 5042, Level 16, State 11, Line 23 The filegroup 'DataBarn_FSGroup' cannot be removed because it is not empty.
Try the following: ALTER TABLE SET (FILESTREAM_ON = "NULL") go ALTER DATABASE Remove file _FILE; ALTER DATABASE REMOVE FILEGROUP ; Also, do not coun't on just looking at the filestream directory to make sure that it is empty. It may contain a .hdr file which could be 0 bytes.
OK - let's start at the beginning. All of the files in the file stream directory have been deleted/moved elsewhere to a non-filestream directory. All data has been removed from the filestream table(s) Now run the following and show the results: SELECT * FROM sys.database_files WHERE type_desc = 'FILESTREAM' GO SELECT * FROM sys.filegroups WHERE type = 'FD' AND type_desc = 'FILESTREAM_DATA_FILEGROUP' GO SELECT * FROM sys.data_spaces WHERE type = 'FD' GO SELECT * FROM sys.tables GO