This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 40%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
Please correct me if I am wrong
the status of the checkpoint file
DATA UNDER CONSTRUCTION - Means this file is open for inserting new rows
ACTIVE - The rows from UNDER CONSTRUCTION will put into files in ACTIVE checkpoint files .
MERGE_TARGET - Initially these checkpoint file are empty, until manually issue CHECKPOINT, and the rows from current ACTIVE checkpoint files will be insert to this MERGE_TARGET, and than mark ACTIVE .
WAITING FOR LOG TRUNCATION - Means these files can be truncate (or delete/ or reuse ) after doing log backup ? I am quick confuse on this type checkpoint files.
Good day
The following post present the process and the steps using simple demo and clear explanation:
https://dbafromthecold.com/2015/04/20/in-memory-oltp-part-3-checkpoints/
thanks for the link, and this is the link I have read last night and start playing with the checkpoint files.
and it didn't mention what's WAITING FOR LOG TRUNCATION .
And the blob has MERGE_SOURCE , while I have MERGE_TARGET ..
And it mention 
processes, while I found I have to manually do a checkpoint .
and what's the background processes for merge ? how frequent it run ?
Hi @sakuraime
WAITING FOR LOG TRUNCATION - Means these files can be truncate (or delete/ or reuse ) after doing log backup ?
Yes. You are right, if the "files can truncate" means files can be deleted and not in transaction. However, the statement 'TRUNCATE TABLE' is not supported with memory optimized tables. And TRUNCATE TABLE cannot be ran inside of a transaction.
If you need the operation to be truly metadata-only, you can drop and recreate the table.
Backup it, delete it and then restore it. It can be reused. There is no difference as doing the operations as usual.
If you want to use truncate statement in future, you can vote here: 32904484-truncate-table-support-for-memory-optimized-tables
More information: truncate-table-transact-sql-restrictions, sys-dm-db-xtp-checkpoint-files-transact-sql
BR,
Mia
"Backup it, delete it and then restore it. It can be reused" <<< what does it mean ?
Just want to know when I see the chekpoint files in "WAITING FOR LOG TRUNCATION" status. what options I have ?
So and I also see that some times I insert some rows to a in-memory table , the checkpoint file with "DATA UNDER CONSTRUCTION " not appear immediately .
My understanding is checkpoint files appear in the following sequence
precreate -> DATA UNDER CONSTRUCTION -> Merge_target -> active - > WAITING FOR LOG TRUNCATION
Hi @sakuraime ,
Backup it, delete it and then restore it. It can be reused" <<< what does it mean ?
This is for your question as this : WAITING FOR LOG TRUNCATION - Means these files can be truncate (or delete/ or reuse ) after doing log backup ?
Just want to know when I see the chekpoint files in "WAITING FOR LOG TRUNCATION" status. what options I have ?
1,Manually delete the physical files ?
2,wait for some time it will be reuse ?
Option 2. Wait for some time it will be reused.
BR,
Mia
If the answer is helpful, please click "Accept Answer" and upvote it.
Hi @sakuraime ,
Is the reply helpful?
BR,
Mia
If the answer is helpful, please click "Accept Answer" and upvote it.
"Wait for some time it will be reused." . then i will need to know in how much time, what situation , it will be reused ( other than restart the SQL Server) .
I will need to know whether you have solved the issue in your post : In-memory table range scan/ seek