트리거가 적용된 메모리 최적화 테이블에서 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 사용하려면 트리거를 제거  작업을 진행그리고 다시 트리거를 생성하여 사용할  있다.

 

[참고자료]

https://blogs.msdn.microsoft.com/psssql/2017/05/10/unable-to-issue-alter-table-for-a-memory-optimized-tables/

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
No. Subject Author Date Views
Notice 2023년 1월 - SQLER의 업데이트 강좌 리스트 코난(김대우) 2023.01.02 527
2106 SQL Server 2016 쿼리 실행에 대한 각 스레드(오퍼레이터) 성능 통계 jevida(강성욱) 2017.09.13 7107
2105 Multisubnet환경의 AG 그룹에서 링크드 서버 사용시 주의점 jevida(강성욱) 2017.09.13 5968
2104 SQL Linux에서 Job Agent 설치 jevida(강성욱) 2017.09.13 6005
2103 SQL Linux에서 Windows SQL 백업 파일 복원 jevida(강성욱) 2017.09.13 5988
2102 Linux에서 Network I/O 확인 jevida(강성욱) 2017.09.13 6584
2101 SQL Linux에서traceflag 활성화 jevida(강성욱) 2017.09.13 5662
2100 SQL Linux에서 dump file 위치 변경 jevida(강성욱) 2017.09.13 5444
2099 SQL Linux에서 Port 변경 jevida(강성욱) 2017.09.13 6348
2098 Linux에서 DISK 공간 확인 jevida(강성욱) 2017.09.13 5636
2097 SQL Linux에서collation 변경 jevida(강성욱) 2017.09.13 3578
2096 SQL Linux에서 데이터 및 로그 파일의 기본 디렉토리 변경 jevida(강성욱) 2017.09.13 3909
2095 SQL Linux 기본Configure 명령 jevida(강성욱) 2017.09.13 3441
2094 SQL Linux에서 기본 백업 디렉토리 변경 jevida(강성욱) 2017.09.13 3885
2093 Linux에서 DISK I/O 사용량 확인 jevida(강성욱) 2017.09.13 3862
2092 Linux에서 CPU 사용량 확인 jevida(강성욱) 2017.09.13 2256
2091 SQL Linux에서 SQL Server 시작, 중지, 활성, 비활성 jevida(강성욱) 2017.09.13 1493
2090 SQL Server DBA 체크리스트 jevida(강성욱) 2017.05.31 7352
2089 SQL Server 데이터베이스 백업이 성공적인지 확인하는 방법 jevida(강성욱) 2017.05.31 3778
» 트리거가 적용된 메모리 최적화 테이블에서 alter table 실패 jevida(강성욱) 2017.05.31 3318
2087 메모리 최적화 테이블 변수 및 예상 행수 jevida(강성욱) 2017.05.31 2984





XE Login