CODEWITHSUNDEEP
sqlsql-serversql-server-2019in-memoryin-memory-tables
duhast
duhast

Reputation: 13

SQL Server - Memory quota error during migration to in-memory table

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.

Upvotes: 1

Views: 1020

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

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

Upvotes: 2

Related Questions