First published on MSDN on Dec 23, 2015
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.
NOTE:
If they see this message in the SQL error log
[WARNING] Database ID: [7]. Checkpoint hit an error code 0x8300000a. Restarted Physical database, attempt 0 of 5.
the only option to recover the situation is to add space on the disk where the existing container is present and where you run out of space.
Jack Li |Senior Escalation Engineer | Microsoft SQL Server
twitter | pssdiag | Sql Nexus
