In-Memory OLTP - allow removal of Filegroup for memory_optimized_data
It should be possible to remove the memoryoptimizeddata Filegroup and files, if all in-memory tables and procedures have been deleted.
Currently you cannot do this "by design", however this is a potential blocker to people using this technology due to the "no-going-back once it's enabled". It is now several years since Hekaton was first released - support for this feature is long overdue!
At present you get errors such as:
Cannot drop the last memory-optimized container
The filegroup ... cannot be removed because it is not empty.
Upvotes: 3<=-=Jan 8 2018 12:16PM=-=>
Thanks for the suggestion.
We’ll consider it for a future release.
Are there specific limitations imposed when you have the filegroup, e.g., no database snapshot, that hold you back from creating the filegroup?
Or is it only the feeling of not being able to turn back?
Jos de Bruijn – Database Systems PM
Ryan Loken commented
Not being able to remove this feature means we cannot migrate our databases to an Azure Manged Instance. This is causing a lot of frustration for me, not to mention it's costing MSFT over $8K a month in lost revenue. This is blocker to our migration. Please fix this.
Too risky for me. Not using in-memory OLTP with 2019, too. Staying tune for the next release. Fingers crossed!
The biggest reason for removal is that you cannot run dbcc checkdb to check corruption, this becomes painfully obvious when trying to encrypt with tde and the db goes suspect
Daniel Adeniji commented
I spoke way too early.
What triggers the no go back is actually creating memory optimized objects inside the database.
Daniel Adeniji commented
It appears that the issue of not been able to remove empty MEMORY_OPTIMIZED_DATA filegroups has been addressed.
I tested against :-
Version :- Microsoft SQL Server 2019 (CTP3.2) - 15.0.1800.32 (X64)
Release Date :- 2019-July-17
Adam Oien commented
Not being able to remove this feature means that as we attempt to migrate our DBs to an Azure Manged Instance, we must run on the Business Critical pricing tier, even though we aren't actually using this feature. It was turned on as a test and is a blocker to our migration.
It is not possible to run RDS databases multi-az in AWS with this feature enabled - and it's not possible to remove it once enabled which (unfortunately) it is on our production databases. Completely unbelievable situation to be in.
At Summit 2018, the CAT team said that memory-optimized system tables in TempDB would be "opt-in" for SQL 2019.
When I probed further, it seems that TempDB can also opt-out of having a memory-optimized filegroup, but it would require a SQL Service restart. Obviously user databases are different than system databases, but it shows that it can be done.
The community has been waiting a long time for In-Memory OLTP to mature. It's a really great feature, but even with all of the limitations that were removed in SQL 2016, it's still a tall order to get management buy-in, due to issues like this.
Michael J Swart commented
It's not just a feeling. Safety is a huge concern for us so we’re giving up on the feature for now.
We've seen memory and disk impacts when adding the file group so adding the filegroup carries real risks.
In general, it seems like Microsoft is reluctant to invest further in In Memory OLTP because of low adoption by customers. But low adoption may stem from Microsoft not investing in removing these sorts of limitations.
Kendra Little commented
I believe the lack of an "uninstall" is a reason that has contributed to the lack of adoption of in-memory OLTP across the industry.
For good reasons, IT folks are very hesitant to enable a feature which they can't remove. The limitations around cross-database queries combine with the lack of an uninstall to make it less appealing to use the feature.
Enabled it for testing purposes and now I can't take database snapshots, can't use change tracking.... A feature that block other features forever it's not a feature it's a shame!
We cannot implement database mirroring with MO filegroup enabled. Also we cannot use Change Tracking.
When will we be able to remove these memory only filegroups without having to drop the whole database?
This currently seems like a very irritating bug/limitation.
I can't do database snapshots with in-memory enabled. I would very much like to remove in-memory to be able to use database snapshots.
Yair Sageev commented
"Are there specific limitations imposed when you have the filegroup, e.g., no database snapshot, that hold you back from creating the filegroup?"
Most importantly, you cannot rebuild a transaction log if you have MO Filegroup. A number of commands simply don't work. For example: DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS) will ****.
Yair Sageev commented
Please let us remove the mem optimized filegroup. I also decided to play around with it and now cannot utilize change tracking and have restore headaches. Why can we not get rid of it like any other filegroup?
Wagner Ignacio Pinto Junior commented
Any news? When will we be able to remove the empty container?
For me this is a bug, the container is empty after all, to not let us remove an empty container is a really strange "design" decision.
Andrew Tobin commented
I've enabled it for testing and I now find myself wanting to remove it, but I cannot. It filled up the drive and I was unable to perform a checkpoint and it almost took out my test server.
I'd like to be able to remove the components, or at least create a new file and empty the old and remove it.
Preferably though, I'd like to be able to remove the filegroup.
Jos, one problem we have at present is being unable to restore database backups onto AWS RDS - getting errors about FileStream.