Why am I getting so many checkpoint files when I have In-Memory OLTP enabled?
Recently, I looked an In-Memory OLTP issue with Principal Software Engineer Bob Dorr who is still my office neighbor. After restoring a database that had just one memory optimized table, we dropped the table. Even without any memory optimized tables,number of checkpoint files keep going up every time we issue a checkpoint. For a while, I thought we have a bug where our checkpoint files don’t get cleaned up properly.
But after looking closely in sys.dm_db_xtp_checkpoint_files, I notcied that the state_desc was “WAITING FOR LOG TRUNCATION” most of the checkpoint files. The I realized that, we hadn’t backed up the transaction logs. Checkpoint files go through various stages before they can be deleted and removed. If it is “WAITING FOR LOG TRUNCATION”, they can’t be removed. You will need to ensure logs are backup.
So this speaks importance of log backup. Not doing log backup can cause log growth plus checkpoint file growth.
Here is a simple repro
- Create a database
- Create a memory optimized table
- insert some data
- backup the database (full backup) but do not do log backup
- then issue checkpoint on the database repeatedly in a loop like while 1 = 1 checkpoint
- observe the folder that has checkpoint files (use dir /s ) to see number of files keep growing
- stop the above while loop and backup your log, then you will observe most of the checkpoint files will be gone
What are checkpoint files?
They are data and delta files as documented in Durability for Memory-Optimized Tables. When you use disk based tables, the data is written to data files. Even though data is stored in memory for memory optimized tables, SQL Server still needs to persists data for disaster recovery. Data for memory optimized tables is stored in what we call checkpoint files. Data file contains rows from insert and update operations. Delta file contains deleted rows. Over time, these files can be 'merged' increase efficiency. Unneeded files after the merge can be removed eventually (but this can only happen after a log backup).
Demo script
--create database and set up tables
CREATE DATABASE imoltp
GO
--------------------------------------
-- create database with a memory-optimized filegroup and a container.
ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA
go
ALTER DATABASE imoltp ADD FILE (name='imoltp_mod1', filename='c:\sqldata\imoltp_mod1') TO FILEGROUP imoltp_mod
go
use imoltp
go
CREATE TABLE dbo.ShoppingCart (
ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
CreatedDate DATETIME2 NOT NULL,
TotalPrice MONEY
) WITH (MEMORY_OPTIMIZED=ON)
GO
go
insert into dbo.ShoppingCart (Userid, CreatedDate, TotalPrice) values ( 1, getdate(), 1)
go
-- backup database
backup database imoltp to disk = 'c:\temp\imoltp.bak' with init
go
--run checkponit
while 1 = 1 checkpoint
--step 4 issue the command from the following periodically to see file growth
dir /s c:\sqldata\imoltp_mod1
-- stop the checkpoint in the while loop above and issue a backup log. then observe the files will eventually go away
--if you have a small disk that has both checkpoint files and log in one drive, you can eventually run out of disk sapce and get and error like below
Msg 3930, Level 16, State 1, Line 31
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Jack Li |Senior Escalation Engineer | Microsoft SQL Server