| title | Checkpoint Operation for Memory-Optimized Tables |
|---|---|
| description | Learn about checkpoints for memory-optimized tables in SQL Server. The memory-optimized table checkpoint operation is distinct from that of disk-based tables. |
| author | MikeRayMSFT |
| ms.author | mikeray |
| ms.date | 03/01/2017 |
| ms.service | sql |
| ms.subservice | in-memory-oltp |
| ms.topic | conceptual |
| monikerRange | =azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance] A checkpoint needs to occur periodically for memory-optimized data in data and delta files to advance the active part of transaction log. The checkpoint allows memory-optimized tables to restore or recover to the last successful checkpoint and then the active portion of transaction log is applied to update the memory-optimized tables to complete the recovery. The checkpoint operation for disk-based tables and memory-optimized tables are distinct operations. The following describes different scenarios and the checkpoint behavior for disk-based and memory-optimized tables:
When you issue a manual checkpoint, it closes the checkpoint for both disk-based and memory-optimized tables. The active data file is closed even though it may be partially filled.
Automatic checkpoint is implemented differently for disk-based and memory-optimized tables because of the different ways the data is persisted.
For disk-based tables, an automatic checkpoint is taken based on the recovery interval configuration option (for more information, see Change the Target Recovery Time of a Database (SQL Server)).
For memory-optimized tables, an automatic checkpoint is taken when transaction log file becomes bigger than 1.5 GB since the last checkpoint. This 1.5 GB size includes transaction log records for both disk-based and memory-optimized tables.