InMemory topics are always interesting and provide a great learning experience. In a previous blog post, we discussed how InMemory tables are loaded into memory even if they are not queried. Today, we will explore the possibility of taking InMemory filegroups offline and the implications it has on the table’s data.

Before we dive into the details, let’s quickly recap the basics. InMemory tables and filegroups in SQL Server are similar to normal files and filegroups. They can be managed using the same techniques as regular filegroups.

So, is it possible to take InMemory filegroups offline? The answer is yes. We can use the same method we use for other filegroups:

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

Running this script will successfully take the InMemory filegroup offline. If we query the memory consumers using the following DMV query:

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

We will find that the query returns no rows, indicating that the InMemory filegroup is offline and its data is no longer in memory.

Now, let’s move on to the next question. How can we bring the InMemory filegroup back online? In SQL Server 2014 version, the straightforward method of using the “ONLINE” option will not work:

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

This command will result in an error: “Msg 155, Level 15, State 1, Line 1 ‘ONLINE’ is not a recognized CREATE/ALTER DATABASE option.”

So, how can we bring the InMemory filegroup online in SQL Server 2014? Unfortunately, there is no direct method to do so. However, there is a workaround. We can create a new filegroup and move the tables from the offline InMemory filegroup to the new filegroup. This will effectively bring the tables back online.

Here are the steps to bring the InMemory filegroup online:

1. Create a new filegroup using the “ALTER DATABASE” statement.
2. Move the tables from the offline InMemory filegroup to the new filegroup using the “ALTER TABLE” statement.
3. Drop the offline InMemory filegroup using the “ALTER DATABASE” statement.

By following these steps, you can successfully bring the InMemory filegroup back online in SQL Server 2014.

Have you ever encountered a situation where you needed to take an InMemory filegroup offline? How did you handle it? Let us know in the comments!

That’s all for today’s blog post. Stay tuned for more interesting SQL Server topics!