What to do when you run out of disk space for In-Memory OLTP checkpoint files
While data for memory optimized tables resides in memory all the time with SQL Server 2014 and 2016's In-Memory OLTP feature, we still need a means to cut down recovery time in case of crash or restart. For disk based table, checkpoint flushes the dirty pages into data file(s). With In-memory OLTP, there are separate set of checkpoint files that SQL Server uses. These checkpoint files reside in a directory you specify when you create the MEMORY_OPTIMIZED_DATA filegroup required to enable In-Memory OLTP feature.
The question is what happens if the disk that host the In-Memory checkpoint files runs out of disk space? So I decided to do some testing and document the symptoms and recovery steps here in case you run into such issue. With our Azure, test was really easy. All I had to do was to spawn a VM and attach a very small disk to simulate out of disk space condition.
If your disk runs out of space, you will see various errors below though your database stays online
Your insert, update or delete may fail with the following error:
Msg 3930, Level 16, State 1, Line 29
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
In the errorlog, you will see
2015-12-23 21:38:23.920 spid11s [ERROR] Failed to extend file 'f:\temp\imoltp_mod1\7ef8758a-228c-4bd3-9605-d7562d23fa76\a78f6449-bd73-4160-8a3f-413f4eba8fb300000ad-00013ea0-0002' ('GetOverlappedResult'). Error code: 0x80070070. (d:\b\s1\sources\sql\ntdbms\hekaton\sqlhost\sqllang\fsstgl
2015-12-23 21:40:49.710 spid11s [ERROR] Database ID: [6]. Failure to allocate cache file. Error code: 0x80070070. (d:\b\s1\sources\sql\ntdbms\hekaton\engine\hadr\ckptagent.cpp : 890 - 'ckptAgentAllocateCfp')
if you manually issue checkpoint command, you will get this error:
Msg 41315, Level 16, State 0, Line 5
Checkpoint operation failed in database 'testdb'.
What to do when you encounter such condition?
step 1 -- Add additional 'container'
if you can append more space to the disk, just do so. If you can't append more space to current disk, you can add another 'container' to the MEMORY_OPTIMIZED_DATA to point to a folder in another drive. You can do so by issuing a command like this: ALTER DATABASE testdb ADD FILE (name='imoltp_mod1', filename='f:\checkpoint\imoltp_mod1') TO FILEGROUP imoltp_mod
step 2-- Manually issue a checkpoint: after you have added space or additional 'container' as above, just run checkpoint against the database. then you are all set.
Jack Li |Senior Escalation Engineer | Microsoft SQL Server
Comments
- Anonymous
January 19, 2016
Interesting and raises a few scenarios I'd like to try out. Thanks Jack!