SQL Server – How to Eliminate a Filestream Data File on Production Database?

availability-groupsin-memory-databasemirroringreplicationsql-server-2017

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'.

Best Answer

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'
Related Question