When I look at our production SQL Server 2016's server logs I am seeing multiple entries that say:

[INFO] Database ID: [5]. Deleting unrecoverable checkpoint table row (id: 294).

The id at the end is incremented by 1 for each entry.

I Googled for this error and there is literally nothing about it. What does this entry mean? It sounds rather ominous.

  • What are the time intervals between the entries in ERRORLOG? Is it something regular? Do Transaction Log backups occur before or after the message? Do you have any trace flags enabled on the SQL Server instance? – John K. N. Feb 3 '17 at 14:40
  • It seems to be happening every 12 hours or so, but definitely not exactly 12 hours. It seems random. This database has the 'Simple' recovery model. No trace flags, that I am aware of. – Brian P. Hamachek Feb 3 '17 at 14:42
  • Also I ran a DBCC CHECKDB and it returned no issues. – Brian P. Hamachek Feb 3 '17 at 14:43
  • Are you running "memory optimized tables" aka "In-Memory OLTP"? – John K. N. Feb 3 '17 at 14:47
  • Are you using memory optimized tables ? I suspect its related to checkpoint files. Check sys.dm_db_xtp_checkpoint_files to see whats going on. – Kin Shah Feb 3 '17 at 14:47

This is my assumption based on the log entry -

If you are using Memory optimized tables, there are Checkpoint File Pair (CFPs).

CPFs transition through several states before they can be deallocated. Database checkpoints and log backups need to happen to transition the files through the phases, and ultimately clean up files that are no longer needed. For a description of these phases, see sys.dm_db_xtp_checkpoint_files

The log entry refers to deallocation by garbage collection process.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.