question

jlj avatar image
0 Votes"
jlj asked CarrinWu-MSFT commented

Removed Memory optimized FG but unable to rebuild log as it still thinks there are tables in use

So I managed to recreate the database by repointing the data files and then dropping the memory optimised filegroup.
Everything seemed ok, except the loginfo showed that entries were all at status 2 and the log would not free up.
I switched to simple recovery but no change to the log.
No open transactions and no log_reuse_wait_desc.
When I attempted to rebuild the log, it would not let me saying it does not support memory optimized tables.
Why does it still think there are memory optimized tables?
Is there a config file somewhere I can edit?
What else can I try to deal with log issue?
I might look into copying the data into a new db by table but would rather not
Any advise would be welcome.
TIA

sql-server-generalsql-server-migration
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @jlj, any updata for this thread?

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered jlj commented

Why does it still think there are memory optimized tables?

That's one od the documented limitations, you can not drop a memory optimized file group see The Memory Optimized Filegroup
"The following limitations apply to a memory-optimized filegroup: Once you use a memory-optimized filegroup, you can only remove it by dropping the database. "


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Well I know that now, hence the workaround creating a new database and attaching the old files.

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered OlafHelper-2800 commented

Hi @jlj,

Welcome to Microsoft Q&A!

As Olaf mentioned, there have some limitations for a memory-optimized filegroup. But you could delete it as following:

That being said, there is one sliver of sunshine I would like to show you. The memory-optimized filegroup is truly forever and ever as long as your database still exists, but you CAN remove a memory-optimized file on one condition: you create another file in the filegroup to take it’s place. Watch this.

 ALTER DATABASE InMemTest ADD FILE ( NAME = N'InMemFile2', FILENAME = N'C:\Data\InMemFile2' ) TO FILEGROUP InMemFileGroup;
 GO

Commands completed successfully.


 ALTER DATABASE InMemTest REMOVE FILE InMemFile1;
 GO

The file ‘InMemFile1’ has been removed.


Quote from this blog, and also you could refer to this similar thread.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the welcome and feedback.
Not sure this helps me, as I need to get rid of the memory optimized feature all together as its breaking the bulk backup.

0 Votes 0 ·

Hi @jlj, thanks for your reply. Did your workaround worked? Please let us know if you need any help.

0 Votes 0 ·

breaking the bulk backup.

What's a "bulk backup" and why does it break by memory optimized feature?



0 Votes 0 ·