is there a way (clean or unclean) to eliminate a filestream data file on a production SQL Server database?

Question

Is there a way (clean or unclean) to eliminate a filestream data file on a production database?

I forced a garbage collection on an empty filestream :

sp_filestream_force_garbage_collection @dbname = N'DB' ,   @filename = N'db_mod';  

I tried this and memory opt and filestream is't actually enabled on db

SELECT name, description FROM sys.dm_os_loaded_modules WHERE description = 'XTP Native DLL'

this script gives the files in db:

select sdf.name AS [FileName], size/128 AS [Size_in_MB], fg.name AS [File_Group_Name] FROM sys.database_files sdf INNER JOIN sys.filegroups fg ON sdf.data_space_id=fg.data_space_id

FileName Size_in_MB File_Group_Name

db 7282 PRIMARY db_mod 1528 db_mod

Can I manage to eliminate this empty file and filegroup that prevents me from using db mirroring?

of course I tried

-- Remove the File

ALTER DATABASE db REMOVE FILE db_mod1 ; -- Remove the Filegroup ALTER DATABASE db REMOVE FILEGROUP db_mod ;

I get the error:

Msg 41802, Level 16, State 1, Line 1 Cannot drop the last memory-optimized container 'db_mod1'.

Answer 1

To be clear, the FILESTREAM and In-Memory OLTP features are completely separate, and not compatible. Even though In-Memory OLTP uses some components of FILESTREAM, the FILESTREAM feature does not have to be enabled to use In-Memory OLTP, and memory-optimized tables are not able to be used with the FILESTREAM feature.

According to the error you received, you're trying to remove the memory-optimized filegroup, which is not possible.

What does the following query return?

 SELECT *
FROM [InMemTest2].sys.database_files
INNER JOIN [InMemTest2].sys.filegroups ON database_files.data_space_id = filegroups.data_space_id
WHERE filegroups.type = 'FX'