SQL16: Database in Delete-Only Mode

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

Comments

• Anonymous
  March 23, 2017
  Could use a bit of clarity -- you wrote: "We will mark the database in delete-only mode (DOM) for memory-optimized tables when we are out of storage space." And that your test was specific to when the "disk of In-Memory OLTP checkpoint file pair is full...."But the error message states that this applies to the entire database, and is not specific to memory-optimized tables. When error 41822 occurs, can disk-based tables still be updated and inserted?
  • Anonymous
    March 24, 2017
    The comment has been removed