Log not truncating due to xpt_checkpoint - by PeteCarter

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


19
0
Sign in
to vote
ID 1021865 Comments
Status Closed Workarounds
Type Bug Repros 7
Opened 11/5/2014 6:48:22 AM
Access Restriction Public

Description

I have a DB on a SQL Server 2014 instance. I have a memory-optimized filegroup, but as yet, no memory optimized tables. I also have a FILESTREAM filegroup, which contains 1 FILETABLE and a Primary filegroup which contains several tables.

I am in FULL recovery model.

My transaction log is not being reused. It just keeps growing. The Log_Reuse_Wait_DESC is xpt_Checkpoint. I believe this means it is waiting for a memory optimized checkpoint.

The database and the transaction log have been backed up. I have also tried issuing a checkpoint, as I know this also triggers a memory optimized checkpoint.

Looking inside DBCC LOGINFO, I can see that all of the VLFs are marked as active, despite there being no open transactions.
Sign in to post a comment.
Posted by Microsoft on 2/9/2016 at 9:09 AM
We have addressed the issue where sys.databases would erroneously show XTP_CHECKPOINT as the reason for holding up log truncation.
The fix is in SQL Server 2014 SP1 CU4 as well as SQL Server 2016.

--

Jos de Bruijn - SQL Server PM
Posted by Satish Bhujbal on 10/13/2015 at 9:45 PM
I got same issue for one of my prod db instance today. Any workaround or solution for this ?
Posted by Hookit team member on 10/5/2015 at 9:09 AM
In case anyone needs info on this...
After a lot of google searches I found NO SOLUTION for this problem. In our case we were not using memory-optimized tables so we had to create a new empty database and copy all of the data from the old to the new database. The biggest issue we had was the transaction log grew so much so fast we ran out of disk space to do the backups. It wasn't a fun experience but our solution worked. Good luck!




Posted by Hookit team member on 9/28/2015 at 9:20 AM
Does anyone have an update on this. We are experiencing this issue in our production environment.

SQL Server 2014 Enterprise
Have tried Simple and Full recovery modes
Have memory-optimized filegroup setup but no memory optimized tables.

Tried CHECKPOINT - failed
DBCC OPENTRAN - no open transactions
log_reuse_wait_desc in sys.databases = 'XTP_CHECKPOINT'

I am looking for a solution to fix this AND/OR steps to remove memory-optimized filegroup

Posted by NateB50 on 5/22/2015 at 8:46 AM
Same issue with database in SIMPLE recovery mode - it was working fine before adding an in-memory filegroup, so am fairly sure it is related.

Also important to note is that there are a ton of recorded XTP checkpoints in the SQL Server error log before the log runs out of space.

After doing some research, found a guy who wrote a script that can reproduce the issue:

http://pastebin.com/jWSiEU9U
Posted by Peter Skoglund on 1/7/2015 at 4:02 AM
Hi,

We got the same problem
Msg 3013, Level 16, State 1, Line 9
BACKUP DATABASE is terminating abnormally.
Msg 9002, Level 17, State 16, Line 9

The transaction log for database 'X' is full due to 'XTP_CHECKPOINT'.

We have tried the below in different combinations, but with no luck:

CHECKPOINT

ALTER DATABASE [X] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE [X] SET RECOVERY SIMPLE WITH ROLLBACK IMMEDIATE;

BACKUP DATABASE [X] TO DISK = N'NUL'



select 'x' AS name, recovery_model_desc, log_reuse_wait,log_reuse_wait_desc from sys.databases WHERE log_reuse_wait=16

SELECT @@version

Posted by Microsoft on 1/5/2015 at 2:31 PM
Thanks for submitting this feedback.

Note that, in SQL2014, log_wait_reuse_desc=XTP_CHECKPOINT does not necessarily mean that the XTP checkpoint worker is holding up log truncation. It could be the case that you have log ready for reuse, or there could be another reason for holding up log truncation, even if log_wait_reuse_desc=XTP_CHECKPOINT. We are planning to address that issue for the next release, such that log_wait_reuse_desc will give a better indication of what, if anything, is actually holding up log truncation.

To better understand in your scenario what is holding up log truncation we would need additional information. Please send me an email at jodebrui@microsoft.com, referencing feedback ID 1021865, if you want to explore this further.

--
Jos de Bruijn - SQL Server PM