cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Symptoms:

When FNMS is working with Microsoft Server SQL Server 2019 version, they might receive the follow error message:

System.InvalidOperationException: Memory optimized tables and natively compiled modules cannot be accessed from within SQLCLR stored procedures.

Analysis:

SQL Server 2019 (15.x) introduces a new feature that's part of the in-memory database feature family: memory-optimized tempdb metadata. When this feature is enabled, the system tables involved in managing temporary table metadata can be moved into latch-free, non-durable, memory-optimized tables. However any memory optimized tables and natively compiled modules cannot be accessed from within SQL CLR stored procedure, and therefore causing compatible issue with FNMS.

User may see the error message listed above during some data activities such as during data warehouse export process.

Solution:

  • When using SQL Server 2019, please ensure the cumulative update package 5 for SQL Server 2019 or later is installed.
  • Disable TempDB_Metadata using any of the following commands and then restart the SQL Server.
--Option 1:
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF GO
--Option 2: EXEC sp_configure 'tempdb metadata memory-optimized', 0 GO RECONFIGURE GO

 

Was this article helpful? Yes No
No ratings
Comments
mfranz
By Level 17 Champion
Level 17 Champion
  • Are there any details about what CU5 exactly fixes?
  • Also, what about users running GDR instead of CU on their database servers? Is build version 15.0.2080.9 fine?
Cemson
By Level 3 Flexeran
Level 3 Flexeran

Hi mfranz,

For the details of CU5, please find the document from Microsoft:

https://support.microsoft.com/en-us/topic/kb4552255-cumulative-update-5-for-sql-server-2019-084c602b-dc4a-d599-c857-5f18cec950fa

I didn't test on each revision but I believe the memory optimized tempdb has to be switch off no matter which revision of SQL Server 2019 is.

Regards,

Cemson

Version history
Last update:
‎Feb 07, 2021 05:54 AM
Updated by: