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?