How to prevent memory optimized tables from blowing out OPT_MEMORY_OPTIMIZED\$HKv2

Question

Microsoft SQL Server 2017 database, Developer edition. Just updated to latest version (RTM-CU17) (KB4515579) - 14.0.3238.1 (X64).

It uses memory optimized tables. When I run a very large stored procedure which deletes many records in many tables, I find that the database creates more and more big files in the directory C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OPT_MEMORY_OPTIMIZED\$HKv2, this until the disk is full.

I use CHECKPOINT in strategic places in the stored procedure. But this doesn't stop the growth of that directory.

I tried to solve this problem by setting a limit to size of the OPT_MEMORY_OPTIMIZED database file. However, after the limit was reached, SQL Server then started reporting

"Cannot continue the execution because the session is in the kill state."

The only way I found to get out of this situation was to delete the database and restore a backup.

How can I stop the growth of the OPT_MEMORY_OPTIMIZED\$HKv2 directory?

Answer 1

It turned out the prolem was caused by the fact that:

1. My database used recovery mode Full; and
2. I wasn't backing up my logs.

Once I set my database to use recovery mode Simple the problem went away.

Answer 2

This behavior is by design (unless of course you're running into a bug....). You must have enough free space on the volume that containers reside on, for the data/delta files to grow.

Deleting many records in many tables will also create row versions that consume memory, possibly leading to OOM. Gargabe Collection will be aggressive, but the delta files still need to grow.

You should never attempt to set a limit for containers associated with memory-optimized data -- see my post here:

http://nedotter.com/archive/2018/07/dangerous-moves-setting-max-size-for-in-memory-oltp-containers/