6

I ran into a problem yesterday trying to create the first memory_optimized table on a SQL Server 2016 SP1 Enterprise Edition.

I created the database and filegroup as so

CREATE DATABASE imoltp   --  Transact-SQL  
 CONTAINMENT = NONE
 ON  PRIMARY 
( 
    NAME = N'imoltp', 
    FILENAME = N'F:\UNREFRESHED_DB\imoltp.mdf' , 
    SIZE = 5120KB , 
    FILEGROWTH = 1024KB 
)
 LOG ON 
    ( 
        NAME = N'imoltp_log', 
        FILENAME = N'F:\UNREFRESHED_DB\imoltp_log.ldf' , 
        SIZE = 2048KB , FILEGROWTH = 10%
    )
GO

ALTER DATABASE imoltp ADD FILEGROUP [imoltp_mod]  
    CONTAINS MEMORY_OPTIMIZED_DATA;  

ALTER DATABASE imoltp ADD FILE  
    (name = [imoltp_dir], filename= 'F:\UNREFRESHED_DB\imoltp_dir')  
    TO FILEGROUP imoltp_mod;  
go  

Then I created the table

USE imoltp
GO

CREATE TABLE imoltp.[dbo].[T1] (  
  [TempID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),  
  [object_ids] NVARCHAR(255) NOT NULL,
  [names] NVARCHAR(255) NOT NULL,
  [comment] NVARCHAR(50)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_ONLY);  

GO

Which results in this error:

Msg 41334, Level 16, State 0, Line 8 The code generation directory cannot be created or set up correctly.

I finally figured out that SQL was trying to create a directory (XTP) at the default database location defined in the server properties which no longer existed in this situation. This directory is where it wants to store dll files that in-memory needs.

https://blogs.msdn.microsoft.com/bobsql/2016/08/23/create-table-disk-vs-in-memory-optimized/

So my questions is this.

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

Thanks, Craig

  • This is not possible as far as I know! – Stijn Wynants Mar 29 '17 at 12:25
2

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.

  • And on the last 1.4 CTP build in Linux, the same problem is occuring. but there I apparently have no control over where the default database directory is. Perhaps one day. – Craig Efrein Mar 31 '17 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.