Sql-server – How to prevent memory optimized tables from blowing out OPT_MEMORY_OPTIMIZED\$HKv2

memory-optimized-tablessql server

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?

Best Answer

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

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

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

Related Solutions

Sql-server – Simple insert in stored procedure runs twice

I created a test DB and table, I adjusted the schema to make it a bit easier and also commented out the set in the proc.

USE [Test]
GO

/****** Object:  Table [dbo].[news]    Script Date: 2/4/2017 12:21:07 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[news](
    [id_news] [int] NULL,
    [news_owner_id] [int] NULL,
    [news_owner_type] [bit] NULL,
    [news_cat_id] [int] NULL,
    [news_producer] [nvarchar](1000) NULL,
    [news_title] [nvarchar](1000) NULL,
    [news_sutitr] [nvarchar](1000) NULL,
    [news_text] [nvarchar](1000) NULL,
    [news_date] [nvarchar](128) NULL,
    [news_time] [nvarchar](16) NULL,
    [news_link] [nvarchar](512) NULL,
    [img_uri_type] [tinyint] NULL,
    [img_uri] [xml] NULL,
    [newsContentID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

I'm going to answer 2 first. I ran the proc once, it inserted one time. I looked through the execution plan and it shows a straight forward insert. You can see it here: Standard run. I then set your error handling to 0, just to see the plan with that being triggered. Also straight forward. Error handling triggered. I would run SQL Profiler when you see it insert two records or set up sp_whoisactive to catch the plan when it does multiple inserts. My guess is that it's being executed twice.

For question 1, it looks fine to me. You could put in more exact error handling, but overall I would not say it's bad. I really like this template by spaghetti dba.

SQL Server Error – In-Memory Optimized Error: Code Generation Directory Cannot Be Created

Is there a way to define where the XTP directory will be created in the CREATE TABLE syntax or elsewhere?

No, there is not. This always points to the default database path. If the path is removed, it will attempt to be created and have security applied to it a few times before returning an error.

This is true as of build: 13.0.4422.0 and older

If you feel that this should be changed to a configurable value - I'd invite you to create a request in Connect.

Best Answer

Related Solutions

Sql-server – Simple insert in stored procedure runs twice

SQL Server Error – In-Memory Optimized Error: Code Generation Directory Cannot Be Created

Related Question