In-Memory checkpoint files status

sakuraime 2,316 Reputation points
2020-10-23T03:11:10.37+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,260 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Ronen Ariely 15,081 Reputation points
    2020-10-23T04:54:06.68+00:00

    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/


  2. m 4,266 Reputation points
    2020-10-23T07:50:18.18+00:00

    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


  3. m 4,266 Reputation points
    2020-10-24T01:21:59.94+00:00

    Hi @sakuraime ,

    Is the reply helpful?

    BR,
    Mia


    If the answer is helpful, please click "Accept Answer" and upvote it.