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

  • What version of SQL is this? Mirroring has log since been deprecated. – clifton_h Dec 1 at 14:44
  • sql server 2017 CU 12 , what would you suggest instead of mirroring? – Gabriele D'Onufrio Dec 3 at 8:50
  • Please add this label of this Version in your question. Also look up The official Microsoft documentation for replication transaction log shipping, replication and High availability alwaysOn – clifton_h Dec 3 at 13:57
up vote 1 down vote accepted

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'
  • this:file_id file_guid type type_desc data_space_id name physical_name state state_desc size max_size growth is_media_read_only 65537 1F0FEE91-0EB8-498D-AD49-03500FFD5388 2 FILESTREAM 2 db_mod1 E:\Database\SQLServer\DATA\db_mod1 0 ONLINE 195593 -1 0 0 – Gabriele D'Onufrio Nov 30 at 13:49
  • 1
    If you have a filegroup of type 'FX', then you have a memory-optimized filegroup for that database, and as I said, it's not possible to remove it. – NedOtter Dec 1 at 2:07
  • what would you suggest instead of mirroring? I have sql server 2017 CU 12 – Gabriele D'Onufrio Dec 3 at 8:51
  • 1
    Why not use AGs? They are supported with memory-optimized databases, but they are potentially more complex. But you could do Basic AG, and even one (maybe) that is "clusterless". But you'd have to research it to see if it would work in your environment, and BAGs are not readable on the secondary. – NedOtter Dec 4 at 5:49

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.