I have a database on a SQL Server 2014 SP2, CU5 Enterprise edition.

The database has a memory optimized filegroup. There are two tables in it, both are set to DURABILITY = SCHEMA_ONLY and have a low bucket count (e.g. 512).

I don't remember what the initial disk space of this file was, but some time ago it allocated 4 GB and has since grown to ~7 GB. When I look in the filestream files with a hex editor, it seems that they contain data from a (partitioned) datawarehouse table in the same database, but this could be due to instant file initialisation.

  • Is there a way to shrink the filegroup?
  • Why does the in-memory filegroup take up so much space?
  • How can I verify, if (and which) other tables are using the space, since SELECT * FROM sys.indexes AS i WHERE i.data_space_id = 8 -- = the in_memory filegroup does not return anything
  • Just found mssqltips.com/sqlservertip/3339/… for my third question, but sys.dm_db_xtp_table_memory_stats shows only my two tables with only a few hundred KB... – Thomas Franz Aug 16 '17 at 7:10
  • Not possible to control the size of a memory-optimized file group, and that includes the size of any containers and/or checkpoint file pairs within those containers. Even if you have schema-only memory-optimized tables, the engine pre-allocates some files on disk. Are you doing tlog backups for this db? It's not possible for non-memory-optimized tables to be placed on data/delta files. – NedOtter Aug 19 '17 at 4:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.