Triggers And Memory-Optimized Table Modifications

Jack Li troubleshoots a customer issue when trying to modify a memory-optimized table:

Recently I assisted on a customer issue where customer wasn’t able to alter a memory optimized table with the following error

Msg 41317, Level 16, State 3, Procedure ddl, Line 4 [Batch Start Line 35]
A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.

If you access a memory optimized table, you can’t span database or access model or msdb.  The alter statement doesn’t involve any other database.

It turns out there was a DDL trigger defined on the instance that wrote data to msdb.  Click through for Jack’s repro script.  I’d be able to use memory-optimized tables a lot more frequently (to the chagrin of company DBAs, perhaps) if they supported cross-database operations.

Related Posts

Data File Migration With Minimal Downtime

Nate Johnson weaves a yarn around moving from one storage system to another with minimal downtime: Our ERP database has been chosen by the IT gods to get moved to the shiny new flash storage array, off the old spinning-rust SAN.  This is fantastic news for the business users.  But lo, the executives warn us, […]

Read More

Failed To Open Loopback Connection

Arun Sirpal diagnoses a connection problem: I could not read my error log on one of my local SQL Servers, when I executed the following code: EXEC sp_readerrorlog I received the below: Msg 22004, Level 16, State 1, Line 2 Failed to open loopback connection. Please see event log for more information. Msg 22004, Level 16, State […]

Read More

Categories