SQL Server 2019 feature: Memory optimized tables compatible issue with FNMS

SQL Server 2019 feature: Memory optimized tables compatible issue with FNMS

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
  • 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?

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...

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
Revision #:
1 of 1
Last update:
‎Feb 07, 2021 05:54 AM
Updated by:
 
Contributors