xtp Transaction stuck

Why the xtp transaction do not kill or rollback when a long running process is stopped?

A long running transaction inserting millions of records in a In-Memory OLTP TableĀ  is stopped but the spid remains in running state, it do not kill or rollback.

Even you try to kill the session it do not actually kill the session. Run the Kill spid WITH STATUSONLY. It might give the below message

SPID 56: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

We checked the sys.databases log_reuse_wait_desc column and it says waiting for xtp_checkpoint

image

IĀ never thought the xtp transaction might keep on waiting for a checkpoint.

Before we manually issued the checkpoint we enabled TRACE flag 3502 & 3605 to see what happens in the background

use master
go
DBCC TRACEON (3502, 3605, -1);
go

Issue the checkpoint

go
CHECKPOINT
go

image

Leave a Reply

Your email address will not be published. Required fields are marked *