I have a question about XTP_CHECKPOINT.
I'm using SQL Server 2014. I have a database that is in SIMPLE recovery model mode. It is also being replicated.
There are no open transactions. I've run DBCC OPENTRAN and it returns:
"No active open transactions."
But I keep getting this message whenever I try to create or drop a table or delete data:
(I've replaced my actual database name with the word database_name)
"The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'"
Does anyone know why this might be happening, and, more importantly, how can I make it stop?
And yes, the database really is in SIMPLE recovery model mode. i.e. The transaction log should truncate automatically.
Incidentally, another database that I have in full recovery mode did the same thing, started returning the same error:
The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'
I tried to change the log growth settings to unlimited growth, but it wouldn't let me, returning the same error.
I can reproduce the problem without any XTP stuff at all, except for just the filegroup. Here's how: http://pastebin.com/jWSiEU9U
Best Answer
I had a similar problem: I did not have replication but once I used Memory Optimized table as a test, database in Simple recovery mode, but my transaction logs weren't truncated. Manual truncation, even right after a full backup, gave the error:
A manual checkpoint failed:
A manual checkpoint only succeeded right after restarting the SQL Service, which would lead to a 4 hour In Recovery state because of my Multi Tb database size. I also tried to set the autogrowth to a specific size, but it all ended up doing the same: fill up the transaction logs until no space was left.
Finally, after days and nights trying and researching, I found the solution for my problem by installing the Cumulative Update 3 for SQL Server 2014 SP1