Database and server triggers on DDL statements CREATE, ALTER and DROP are not supported with memory optimized tables

Question

I am getting this error when i try to drop a memory optimized table. i am using a sql server 2016 sp1

Database and server triggers on DDL statements CREATE, ALTER and DROP are not supported with memory optimized tables.

I have dropped the sec policy and the 2nd index. I can't drop the spidfilter as you have to have 1 index for the MOT. Just to see if that made a difference. it did not.

code is below:

create table dbo.MOT_tmpTableName
    (
    Col1    int, 
    Col2    int,
    Col3    smalldatetime,
    Col4    smalldatetime,
    Col5    varchar(25),
    Col6    date, 
    Col7    smallint, 
    Col8    smallint,
    Col9    smallint,
    Col10    smallint,

    SpidFilter          smallint    not null   DEFAULT (@@spid),  

    index nIX_SpidFilter nonclustered (SpidFilter),  
    index nIX_Col1_Col2_Col3 nonclustered (Col1, Col2, Col3),

    CONSTRAINT CHK_MOT_tmpTableName_SpidFilter  
        CHECK ( SpidFilter = @@spid ),  
)  
WITH  
    (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);  
go  


create security policy dbo.MOT_tmpTableName_SpidFilter_Policy  
    ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter)  ON dbo.MOT_tmpTableName
    WITH (STATE = ON);  
go

i have asked around and other environments do not have this issue. I can't get the "Database and server triggers..." out of my head. What trigger is firing that is causing an error?

Semiyearly

Answer 1

No, databases with In-Memory OLTP enabled do not support DDL triggers.

Both database-level and server-level DDL triggers are not supported with In-Memory OLTP tables and natively compiled modules.

You cannot create DDL triggers in a database where In-Memory OLTP is enabled since the code path required to run the trigger would interfere with the In-Memory functionality. The workaround is to put all your In-Memory OLTP tables in a single database without a DDL trigger, and the rest of your tables into a separate database without memory-optimized tables, and enable the DDL Trigger there.

Cannoneer

Answer 2

There is probably DDL trigger active in your Database or your Server.

If you are using SQL Server Managment studio, these could be found under "Databases" -> "Database_name" -> "Programmability" -> "Database triggers" folder for database trigger and under "Server objects" -> "Triggers" folders in Object Explorer.

Pure t-SQL search:

SELECT
  *
FROM
  database_name.sys.triggers AS T
WHERE
  T.parent_class = 0;

SELECT
  *
FROM
  sys.server_triggers AS ST;

Phionna

Answer 3

Late to the party, but just solved it by simply disabling the database triggers from SQL server management studio itself, after that it allowed dropping in-memory tables, do re-enable triggers if you need it

Hope it works!

Jodyjoe