SQL Server 2017 log file growth with memory optimized table

I'm start to using the memory optimized table, the problem that I have is the log file growth also with the database put in simple mode.

On my maintenance schedule I checkpoint and shrink, but still has the issue. If I revert back the tables to normal disk table, everything looks fine.

Did somebody know the reason?

The database has the column LOG_REUSE_WAIT_DESC to 'nothing' in sys.databases.

All the mem opt tables are in SCHEMA_ONLY modality, but still full logging.

SQL Server 2017 version 14.0.3037

I also see these messages in the SQL Server error log:


spid38s,Unknown,[INFO] Database ID: [5]. Deleting unrecoverable checkpoint table row (id: 669)

spid102,Unknown,[INFO] HkHostFreezeCkptTrimming(). Database ID: [5]. HkTrimLSN Frozen: 1

spid102,Unknown,[INFO] getMaxUnrecoverableCheckpointId(). Database ID: [5]. Start of Log LSN: 00001714:000004E0:0002 used to trim unrecoverable checkpoint files tables during full backup

Sizes situation with memory optimized tables:

Sizes situation with disk based table:

First of all, if you use durable memory-optimized tables, everything from the memory-optimized side will be fully logged - the database recovery setting is not respected.

Next, is your 2017 install fully patched? There have been a number of fixes to the In-Memory engine since RTM.

In order for the log to be cleared of memory-optimized transactions, they must also be written to the checkpoint file pairs. If the CFPs needed to be expanded/added, and the drive/volume that hosts them can't do that, then the transaction log cannot be cleared.

StackExchange Database Administrators Q#216982, answer score: 5