3

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: Memory optimized table

Sizes situation with disk based table: Disk based table

  • 1
    @AlbertoCigliutti Please add new information to the question instead of in comments. There is an edit link that allows you to do so. – Tom V Sep 7 '18 at 11:22
5

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.

  • 1
    I would open a ticket with Microsoft, and/or post on the MSDN In-Memory forum – NedOtter Sep 12 '18 at 19:52
  • 1
    @jadarnel27 is correct - I should have written: "If I was you, I would open a ticket with Microsoft, and/or post on the MSDN In-Memory forum". – NedOtter Sep 14 '18 at 18:31

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.