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
Best Answer
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.