1

We are currently migrating to in-memory tables on SQL Server 2019 Standard Edition. The disk based table is 55GB data + 54Gb of indexes (71M records). RAM is 900 GB. But during data migration (INSERT statement) we get an error message:

Msg 41823, Level 16, State 109, Line 150 Could not perform the operation because the database has reached its quota for in-memory tables. This error may be transient. Please retry the operation.

The in-memory file is “unlimited”, so it looks strange since SQL Server 2019 should not have any size restrictions for in-memory tables.

Mercury
  • 594
  • 1
  • 12
  • 28
duhast
  • 13
  • 3

1 Answers1

2

Why do you think in-memory data size in a single mem-opt table is unlimited on standard edition?

From Memory Limits in SQL Server 2016 SP1 (all of which still applies according to 2019 docs):

Each user database on the instance can have an additional 32GB allocated to memory-optimized tables, over and above the buffer pool limit.

So, you can do what you want, I suppose, but you'll have to spread it across multiple databases. You won't be able to store more than 32GB in a single mem-opt table or even in multiple mem-opt tables in a single database.

Cropped and probably inappropriately-scaled screenshot from the 2019 docs:

enter image description here

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thank you! This is my fault. I just paid my attention to a general sentence on one blog: "The maximum size of memory recommended by Microsoft to store the durable Memory-Optimized tables, that will be used during the recovery process, is increased in SQL Server 2016 to be 2 TB, instead of the 256 GB recommendation in SQL Server 2014." – duhast Nov 01 '21 at 13:23