SQL16: Database in Delete-Only Mode

  • Article

What will happen when the disk of In-memory OLTP checkpoint file pair is full in SQL Server 2016? Here is the learning from a recent test:

#1 Change in SQL 2016
This is a change in SQL Server 2016. We will mark the database in delete-only mode (DOM) for memory-optimized tables when we are out of storage space. In SQL Server 2014, the user database will be suspended, and now it could still be online. SQL Error 41822 will be raised when new insert is triggered.

Msg 41822, Level 17, State 1, line 5
There is insufficient disk space to generate checkpoint files and as a result the database is in delete-only mode. In this mode, only deletes and drops are allowed.
#2 How to tell database is in a delete-only mode
In current version of SQL Server, you can check by sending a non delete statement to user database.
#3 How to resume the insert
After you deleting records or freeing more disk space, you can run a manual CHECKPOINT command to bring database back to normal from Delete-Only Mode. Or it will be back to normal by next auto CHECKPOINT.

 

Posted by Shiyang Qiu, Mar 23, 2017