다음을 통해 공유


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

twitter| pssdiag |Sql Nexus