We help IT Professionals succeed at work.
Private
Troubleshooting Question

SQL error when creating a memory optimised table

30 Views
Last Modified: 2020-11-11
CREATE TABLE [MEDIL_DEBUG](
[EntryDateTime] datetime NOT NULL
PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000),
)WITH (MEMORY_OPTIMIZED = ON,  DURABILITY = SCHEMA_AND_DATA  );
Msg 41334, Level 16, State 0, Line 1
The code generation directory cannot be created or set up correctly.

Completion time: 2020-11-10T18:58:03.6199158+00:00


Comment
Watch Question

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Hmm, the only thing I can think of is that the two WITH options seem a bit contradictory.  That is, MEMORY_OPTIMIZED = ON means stored totally in memory, whereas DURABILITY = SCHEMA_AND_DATA says data is stored to disk (you can't keep data durable unless it goes to disk).

Author

Commented:
Tried this :
CREATE TABLE [MEDIL_DEBUG1](
[EntryDateTime] datetime NOT NULL
PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
)WITH (MEMORY_OPTIMIZED = ON );
But, same
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
SCHEMA_AND_DATA is the default.  Maybe try below?  (Btw, I put WITH options in alpha order -- just for my own sake -- not in the order shown in MS docs and/or sample code.)

CREATE TABLE [MEDIL_DEBUG]( 
    [EntryDateTime] datetime NOT NULL 
        PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000)
    )
    WITH ( DURABILITY = SCHEMA_ONLY, MEMORY_OPTIMIZED = ON ); 

Author

Commented:
Same :
Msg 41334, Level 16, State 0, Line 10
The code generation directory cannot be created or set up correctly.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Does the db have a memory optimized filegroup available?

I think SQL needs one to persist the schema!?

Author

Commented:
Yes,

Author

Commented:
Can I delete an create new one?

Author

Commented:
But same error, only ended up having 2 files in the filegroup
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Hmm, interesting, the tables create fine for me, including with just:
...
    WITH ( MEMORY_OPTIMIZED = ON );





Author

Commented:
Yes, it did on my other instance but, failing here. Just pulling my hair 
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK, did a quick Google.

Make sure the default database path(s) is(are) valid.  Memory-optimized tables require creation of some code, and SQL has to create a place to store that code. 

Author

Commented:
default database path(s) is(are) valid.  where to check this?
CERTIFIED EXPERT

Commented:
Look at the database properties and check whether the XTP folder exists in Data folder on the server. Maybe the user has restricted rights to create/access it.

Author

Commented:
Just checking, thanks!

Author

Commented:
Hi,
I just checked and it does exists but, its readonly.
Maybe the user has restricted rights to create/access it. - How do I check that ?


Author

Commented:
If this is resolved, it will be a tremendously helpful.
CERTIFIED EXPERT

Commented:
The Read/Only seems to be OK. More important is the Security tab contents.
Does the user under which SQL Server service is running have full access to this folder and its subfolders?

Author

Commented:
ok, will check
CERTIFIED EXPERT

Commented:
You should look at the XTP subfolder.
Dirty solution would be to allow full access to everybody to Data folder, subfolders and files. It would show whether the issue is caused by access rights.

Author

Commented:
Yes, admin have full rights

Author

Commented:
Ahh ok
You should look at the XTB subfolder. -checking
CERTIFIED EXPERT

Commented:
SQL Server does not run under admin obviously. Please check this in SQL Server Service

Author

Commented:
ys have full rights on XTB

Author

Commented:
SQL Server does not run under admin obviously. Please check this in SQL Server Service - where to check this

Author

Commented:
Check it and it gives me full control

CERTIFIED EXPERT

Commented:
Hmm... If this still did not help we should go back to the "Default database properties".

So Right click on the server in SSMS and look at the Properties.
Server Properties dialog should contain Database Settings tab and this tab shows Data folder name.
Is this Data folder name the one you were looking for access rights in XTP subfolder?
CERTIFIED EXPERT

Commented:

Author

Commented:
yes it is

CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
ok, will do and be right back

Author

Commented:
It worked!!!!

Author

Commented:
pcelba 
You are a Genius!!! Thanks a ton.  
You have no idea how much it will be helpful to me. Can't thank you enough!!
CERTIFIED EXPERT

Commented:
You are welcome!
SQL Server should handle these rights itself. I have no clue why it failed for you.