데이터베이스 개발자 Tip & 강좌
트리거가 적용된 메모리 최적화 테이블에서 alter table 실패
· Version : SQL Server 2014, 2016
SQL Server 메모리 최적화 테이블에 트리거가 적용되어 있는경우 alter table 작업이 아래 오류와 함께 실패한다.
Msg 41317, Level 16, State 3, Procedure ddl_trigger, Line 5 [Batch Start Line 28] 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 문은 다른 데이터베이스를 포함하지 않는다.
아래 코드는 메모리 최적화 테이블에서 DDL 트리거를 적용한 후 alter table 작업시 오류가 발생하는 내용을 확인할 수 있다.
트리거된 데이터를 저장하기 위해 msdb에 테이블을 생성한다. (사용자 DB 라도 상관 없음)
use msdb go
create table tblTrack (DBname nvarchaR(50), Event_Data nvarchar(max)) go |
테스트용 데이터베이스를 생성한다.
CREATE DATABASE imoltp CONTAINMENT = NONE ON PRIMARY ( NAME = N'imoltp', FILENAME = N'C:\_SQL_Data\imoltp.mdf' ), FILEGROUP [InMemory] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT ( NAME = N'imoltpfg', FILENAME = N'C:\_SQL_Data\imoltpfg' , MAXSIZE = UNLIMITED) LOG ON ( NAME = N'imoltp_log', FILENAME = N'C:\_SQL_Data\imoltp_log.ldf' , SIZE = 25600KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO |
DDL 트리거를 생성하고 테이블을 생성한다.
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 |
DDL트리거가 적용된 상태에서 메모리 최적화 테이블에 alter table 작업을 실행 한다. 오류가 발생하는 것을 확인할 수 있다.
alter table t add c2 int not null DEFAULT 1 WITH VALUES go |
메모리 최적화 테이블에서 alter table을 사용하려면 트리거를 제거 후 작업을 진행, 그리고 다시 트리거를 생성하여 사용할 수 있다.
[참고자료]
