Unable to issue alter table for a memory optimized tables
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.
To cut to the chase, we got a SQL Server userdump when the error is raised. From analyzing the dump, we discovered that a DDL trigger is defined for alter table. In that DDL trigger, it inserts events into MSDB.
The solution is to disable DDL trigger and then issue alter
Demo
CREATE DATABASE imoltp
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'imoltp', FILENAME = N'C:\SQLData\SQL16A\imoltp.mdf' ),
FILEGROUP [InMemory] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT
( NAME = N'imoltpfg', FILENAME = N'C:\SQLData\SQL16A\imoltpfg' , MAXSIZE = UNLIMITED)
LOG ON
( NAME = N'imoltp_log', FILENAME = N'C:\SQLData\SQL16A\imoltp_log.ldf' , SIZE = 25600KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
go
use IMOLTP
go
CREATE TRIGGER ddl_trigger ON DATABASE
FOR ALTER_TABLE AS set nocount on
begin insert into msdb.dbo.tblTrack values(DB_NAME(), EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(max)') )
end
go
CREATE TABLE [dbo].t
(
[id] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [pk_id] PRIMARY KEY NONCLUSTERED HASH
(
[id]
)WITH ( BUCKET_COUNT = 16777216)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
go
--the following alter will cause 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.
alter table t add c2 int not null DEFAULT 1 WITH VALUES
go
--
disable trigger ddl_trigger on database
--this will succeed as ddl trigger is disabled
alter table t add c2 int not null DEFAULT 1 WITH VALUES
--reenable
enable trigger ddl_trigger on database
Jack Li |Senior Escalation Engineer | Microsoft SQL Server