SQLTeam.com | Weblogs | Forums

How to fix db log full due to XTP_CHECKPOINT?


#1

Msg 9002, Level 17, State 16, Line 5
The transaction log for database 'db_R&D' is full due to 'XTP_CHECKPOINT'.

this database is In-memory enabled. The In-memory table is always delete * and re-insert with new data (almost hourly).

i had checked on the log as well. It has all this weird weird stuff.

[INFO] HkHostLogCheckpointRecord(): Database ID: [5]. Logged XTP checkpoint (v4) for timestamp 110290, serialize timestamp 110292: { Root ==> GUIDs = { Rowset {92A9D126-AE5B-49E5-94A9-F645940AF8EC}, Column {327E3AF8-EB18-4ABB-BC69-F622A4F9FE32} }, Recovery LSN = 00000839:00018764:0008, Previous complete checkpoint LSN = 00000839:00018A48:0028, Close LSN = 00000839:00129499:0005, Complete LSN = 00000839:00129911:002B, Root LSN = 00000839:00129499:0004, MRT LSN = 00000000:00000000:0000, Delta Watermark LSN = 00000000:00000000:0000, Backup File Collection LSN = 00000839:00129911:0028 }

Disallowing page allocations for database 'db_R&D' due to insufficient memory in the resource pool 'default'. See 'http://go.microsoft.com/fwlink/?LinkId=330673' for more information.

How to fix this error?


#2

what error? Looks like an informational message to me


#3

totally no idea, m going to throw the in-memory thingy to never to appear in my head


#4

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


#5

not sure u fixed the problem or not but i gave up everything related to in-memory to my core database but setup it to another database.

What i tried is that you should never every try to delete yours log file as the so call in-memory engine will stored something in the log as well. Last time we can delete the log file and sql server will rebuilt it....but not for in-memory!