SQL SERVER – InMemory Table Creation Error Message: Msg 41337

I had recently gone to my hometown to visit my parents as it was holidays for my daughter because of festival. These are relaxing times and hardly try to get in front of my computer during these 2-3 days of family time. But who said that happens always. In this recent trip, I had one of my cousins visit our place who was incidentally studying his engineering degree. We suddenly got into a conversation and I started talking about InMemory databases and how they can be made resilient. Though this topic becomes a great conversation starter to college students, he was baffled with the fact that we can have InMemory databases that are ACID compliant.

He got to his home and said he would play around with this concept because he had a SQL Server 2016 evaluation version. I said there are many more enhancements to talk but wanted him to do his bit of search and homework before I taught him some really cool tricks. That night he sent me a mail stating he was getting some error while creating an InMemory table and was not sure what to do. I asked him to send me the error to take a first look:

Msg 41337, Level 16, State 100, Line 2
Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.

It was clear from the message the source of the problem. He had created a normal database without the InMemory filegroup.

I sent him the script to get rid of the error message and is shown below:

-- Create the Database
-- Note the filegroup of type MEMORY_OPTIMIZED_DATA
USE MASTER
GO
IF DB_ID('InMem_OLTP') IS NOT NULL
BEGIN
ALTER DATABASE [InMem_OLTP] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [InMem_OLTP] END
CREATE DATABASE [InMem_OLTP] ON  PRIMARY
( NAME = N'InMem_OLTP_data', FILENAME = N'C:\DATA\InMem_OLTP_data.mdf',size = 50MB)
LOG ON
( NAME = N'InMem_OLTP_log', FILENAME = N'C:\DATA\InMem_OLTP_log.ldf',size = 10MB)
GO
--- Step 2 - Can we create this table?
USE InMem_OLTP
GO
CREATE TABLE [Customer](
[CustomerID] INT NOT NULL
PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000),
)WITH (MEMORY_OPTIMIZED = ON,  DURABILITY = SCHEMA_AND_DATA  );
GO


As you can see we will get the exact error as mentioned earlier.

Msg 41337, Level 16, State 100, Line 2
Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.

As mentioned, let us add the InMemory Filegroup so that we can create the tables without a glitch:

-- Step 3 -- Add a Filegroup that is MEMORY_OPTIMIZED.
USE InMem_OLTP
GO
ALTER DATABASE InMem_OLTP
ADD Filegroup [InMem_OLTP_InMemory] CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE InMem_OLTP
ADD FILE (NAME = InMem_OLTP_InMemory, FILENAME = N'C:\Data\InMem_OLTP_mopt')
TO FILEGROUP [InMem_OLTP_InMemory]

Now if we create the table as mentioned in Step 2 above, there will not be any error. I don’t blame my cousin for this because he was a novice and was not able to understand what a container is when it comes to InMemory tables. Though this was a simple error that most of you would easily be able to solve, it can surely be a great source troubleshooting for people like my cousin.

Reference: Pinal Dave (https://blog.sqlauthority.com)