SQL Server 2016 – Memory Optimized Table Precreated Files Constantly Growing

memory-optimized-tablessql server

I have a memory optimised table in SQL Server 2016, with file durability enabled. The table is only 100MB.
SQL Server is constantly creating new files on disk, which start off at a few hundred MB, but then grow constantly until the drive runs out of space then they clear down and the process starts again.
The files are growing at about 900MB an hour.
Running the SQL below shows that all the space is in Precreated Data – so it's not even actually storing large amounts of data, just allocating space to use later.

SELECT state_desc, file_type_desc, COUNT(*) AS [count], 
  SUM(file_size_in_bytes) / 1024 / 1024 AS [on-disk size MB]   
FROM sys.dm_db_xtp_checkpoint_files  
GROUP BY state, state_desc, file_type, file_type_desc  
ORDER BY state, file_type

Results of query

File growth on the file group is set to unlimited, so I'm assuming I can set a value on this and stop it growing, but I can't find anything to say why it's allocating so much space in the first place. The data in the table does change very rapidly (a lot of inserts and deletes as the data is constantly processed, but it the data that isn't Precreated never gets over 4GB.

Is there a setting in SQL Server that controls this file precreation?
Is it safe to set a limit on the autogrowth for the file group?

Best Answer

Question title says SQL 2014, but question text says SQL 2016. This issue is not necessarily a bug in either version.

Assuming db is in full recovery, in order for file merge to occur, transaction log backups and checkpoints must occur. If running simple, checkpoint alone will take care of file merge and clearing the log. Please see this blog post:

https://blogs.msdn.microsoft.com/psssql/2017/03/06/why-am-i-getting-so-many-checkpoint-files-when-i-have-in-memory-oltp-enabled/

But @Slowry is also correct, in that you must be fully patched.

There is no knob you can turn to affect file precreation, although the required storage footprint is less in SQL 2016. There is no way to control the size of checkpoint file pairs, containers, or the memory optimized file group (in any version of SQL Server that currently supports In-Memory OLTP).

Related Question