29

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

Paul White
  • 94,921
  • 30
  • 437
  • 687
user3469285
  • 671
  • 2
  • 10
  • 19

4 Answers4

10

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

Gregory Liénard
  • 136
  • 1
  • 7
9

First make sure that replication is not causing this, as stated in the connect item the "log_wait_reuse_desc=XTP_CHECKPOINT does not necessarily mean that the XTP checkpoint worker is holding up log truncation," so start by running sp_repltrans and make certain that all data has been distributed.

Then there is this little snippet here:

"it happens on a database, which has memory optimized file group, no matter there has memory optimized tables or not.

The current workaround is set the AutoGrown to be a fixed size. Or, changing the recovery mode to be Simple, and shrinking the log."

So if cleaning up replication does not work try the following:

checkpoint;
dbcc shrinkfile (Logfile, truncateonly)
alter database [database] modify file (filename = 'TRANSACTIONLOG', FILEGROWTH = 5MB)

It is not stated if this is for the log file or the database files but lets start by trying the log files and if not then try setting the database files to fixed growth:

mustaccio
  • 28,207
  • 24
  • 60
  • 76
Spörri
  • 4,734
  • 15
  • 28
5

I was able to workaround the issue by adding another log file, which then allowed me to run a full backup, adjust the primary log file size and capped growth along with removing the extra log file added to resolve the XTP_CHECKPOINT issue.

user78285
  • 51
  • 1
  • 2
1

I've experienced this with a customer. The log and in-memory FILESTREAM data files were on the same drive. They created a new log file (few suggested this) but the system can't CHECKPOINT as it fails to create the in-memory checkpoint files (*.HKCKP).

Try freeing up space on the drive with the in-memory FILESTREAM data.

Phil Grayson
  • 123
  • 6