SQL SERVER – How to take InMemory – OLTP FileGroup Offline?

InMemory topics are always interesting and a great learning experience. When I wrote the other post, it was more about how InMemory tables are loaded into memory even if they are not queried. The basics, here is to bring the data for In Memory Tables ahead to memory irrespective of their usage.

After this blog post was published, someone wrote me a mail asking if it is possible to take the InMemory Filegroups offline? And will the table’s data taken offline too? This question was obvious but still needed validation and I was curious to understand why this wouldn’t work in normal circumstances. The script to start working on this post has to start from previous post. Please create the database and then before the cleanup step – start to run the below scripts.

At this stage, your query must return some value for the below query:

USE IM_OLTP;
GO
SELECT CONVERT(CHAR(20), OBJECT_NAME(OBJECT_ID)) AS Name,*
FROM sys.dm_db_xtp_table_memory_stats
WHERE OBJECT_ID> 0

For all practical purposes, InMemory tables and filegroups are just like normal files. We should be able to take them offline using the same way we can do other filegroups.

-- Take InMemory FG offline
ALTER DATABASE IM_OLTP MODIFY FILE (NAME = 'IM_OLTP_mod', OFFLINE)
GO


This should complete successfully and when we run the above DMV query to find the memory consumers, this must return no rows.

SELECT CONVERT(CHAR(20), OBJECT_NAME(OBJECT_ID)) AS Name,*
FROM sys.dm_db_xtp_table_memory_stats
WHERE OBJECT_ID> 0

Now that was pretty simple and easy to figure out. I would like to know if you have ever done anything like this in your systems?

Quick Quiz Question:

Now if taking the filegroup offline was easy, what about bringing the filegroup online next?

ALTER DATABASE IM_OLTP MODIFY FILE (NAME = 'IM_OLTP_mod', ONLINE)
GO

Simple isn’t it? No. You will encounter an error:

Msg 155, Level 15, State 1, Line 1
‘ONLINE’ is not a recognized CREATE/ALTER DATABASE option.

So the quiz question is, how can we bring the filegroup online in SQL Server 2014 version? What are the steps. Let me know via comments.

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