patternsqlModerate
The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT'
Viewed 0 times
duethefullxtp_checkpointlogdatabase_namedatabasefortransaction
Problem
I have a question about
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
"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
"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
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
Solution
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:
Cannot shrink log file X because the logical log file located at the end of the file is in use.
A manual checkpoint failed:
Msg 41315, Level 16, State 4, Line N
Checkpoint operation failed in database X.
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
Cannot shrink log file X because the logical log file located at the end of the file is in use.
A manual checkpoint failed:
Msg 41315, Level 16, State 4, Line N
Checkpoint operation failed in database X.
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
Context
StackExchange Database Administrators Q#93588, answer score: 10
Revisions (0)
No revisions yet.