Sep 22, 2013

The MEMORY_OPTIMIZED_DATA filegroup does not exist or is empty. Memory optimized tables cannot be created for database until it has one MEMORY_OPTIMIZED_DATA filegroup that is not empty.

In sql server 2014, if we will try to create a memory optimized table by following script:

CREATE TABLEtblEmployee(
     ntEmpID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED HASH (ntEmpID) WITH (BUCKET_COUNT=1024),
     vcName VARCHAR(50),
     moSalary MONEY
)
WITH(MEMORY_OPTIMIZED =ON) 

Or

CREATE TABLEtblEmployee(
     ntEmpID BIGINT NOT NULL,
     vcName VARCHAR(50),
     moSalary MONEY,
     CONSTRAINT PK_sample_memoryoptimizedtable PRIMARY KEY NONCLUSTERED HASH (ntEmpID)
     WITH (BUCKET_COUNT=1024)
)
WITH(MEMORY_OPTIMIZED =ON)

We may get error message something like this:

Msg 41337, Level 16, State 0, Line 1
The MEMORY_OPTIMIZED_DATA filegroup does not exist or is empty. Memory optimized tables cannot be created for database until it has one MEMORY_OPTIMIZED_DATA filegroup that is not empty.

Cause: To create memory optimized tables in the any database, database must have MEMORY_OPTIMIZED_DATA filegroup.
Solution: Add MEMORY_OPTIMIZED_DATA filegroup in your database. For example:

ALTER DATABASEExact_Help
ADD FILEGROUP  sample_database_filegroup CONTAINS MEMORY_OPTIMIZED_DATA

ALTER DATABASEExact_Help
ADD FILE
  (NAME = sample_database_filegroup_file,
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\Exact_Help_1')
TO FILEGROUPsample_database_filegroup

Now create your memory optimized table.


Note: Here Exact_Help is my database name, sample_database_filegroup is name of my file group, sample_database_filegroup_file is name of the file and C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\Exact_Help_1 is the path of file where I am physically storing in the hard disk.

No comments:

Post a Comment