How can I drop a Memory Optimized Table?

Question

Running SQL Server Standard 2016 SP2-CU1. I am not as concerned with the In memory Filegroup, but I must be able to drop the table objects? Or possibly rename them?

Running:

DROP TABLE [REF].[Work_xxyyzz]

Gives:

Msg 12332, Level 16, State 111, Line 1
Database and server triggers on DDL statements CREATE, ALTER and DROP are not supported with memory optimized tables.

There are no user triggers or events on my In Memory Tables. 5 simple tables with primary keys.

There are no dependencies or references to these tables. They are like temp tables, we name them Work_xxxyyzz tables. However, they do belong to a database schema.

I am fine to keep the filegroup in place for now. I want to drop these tables in order to create default objects of the same name.

Answer 1

In addition to the restrictions pointed out in Evan's answer, there are a number of other things that can prevent dropping tables. Most of them are similar to the restrictions on normal disk-based tables, and you should see them called out in your error message.

For instance, if I have an in-memory table referenced by a natively-compiled (schemabound) stored procedure, I'll get this error if I drop the table:

Cannot DROP TABLE 'YourTable' because it is being referenced by object 'sp_YourStoredProc'.

The solution in that case is to drop the stored proc first, and then drop the table.

---

The error message you're getting indicates that there is a server or database level DDL trigger. You'll need to disable or drop those triggers in order to drop the in-memory table. You can find any such triggers by running these queries:

USE [master];
GO
SELECT * FROM sys.server_triggers;

USE [YourDatabaseName];
GO
SELECT * FROM sys.triggers;

You should look into what these triggers are doing (auditing, perhaps?). If it's safe to do so, consider disabling them with the DISABLE TRIGGER statement, then dropping your table, then using ENABLE TRIGGER to turn the triggers back on.

Note that server and database-scoped DDL triggers are different from object level DML triggers (which you mentioned that you checked for and there are none).

Answer 2

It was a transactional replication trigger that was causing my problem.

Evan Carroll's answer provided the direction to look, jadarnel27's answer provided syntax, and they both helped point me to DDL triggers as the root cause. I needed to execute in the proper database context.

ANSWER:

USE [userdb]
GO
SELECT * FROM sys.triggers;

tr_MStran_droptable 18203215    0   DATABASE    0   TR  SQL_TRIGGER 2018-07-19 16:14:05.537 2018-09-21 10:32:34.580 1   1   0   0
....

DISABLE TRIGGER tr_MStran_droptable ON DATABASE;

DROP TABLE [REF].[Work_xxyyzz];

Answer 3

Just use DROP TABLE, there is one caveat,

Memory-optimized tables and natively compiled stored procedures cannot be created or dropped if there is a server or database trigger for that DDL operation. Remove the server and database triggers on CREATE/DROP TABLE and CREATE/DROP PROCEDURE.

Make sure there are no DDL event notifications,

Memory-optimized tables and natively compiled stored procedures cannot be created or dropped if there is a server or database event notification for that DDL operation. Remove the server and database event notifications on CREATE TABLE or DROP TABLE and CREATE PROCEDURE or DROP PROCEDURE.