I want to take some time and discuss my experiences with a certain SQL Server error, error 9002. Error 9002 Transaction log is full due to XTP checkpoint.
An XTP checkpoint is similar to a standard database checkpoint (more info on checkpoints here) that is used in the database recovery process built into SQL Server. The one difference is XTP letting us know that the database is memory optimized.
I start researching this error by validating it, the log is full. I cannot interact with the database using any Alter statements due to the log being full. So fixing the problem means at minimal the database is going to be down. I tried the simple things like doing log backups to truncate the log, this did not help.
After restoring the database (luckily I had a good backup strategy in place) I was finally able to start trying to figure out the cause of the error.
When trying to find root cause I started looking at the log size, the mount point had plenty of space (I typically support auto growth) so that lead to me finding that the log file was limited to a max size. I changed this setting and have not had the issue since.
UPDATE: Another trick I was able to do was add a second log file to resolve the error. Once the error went away I was able to perform a backup to truncate the logs. Once the backup completed I was able to alter the max size of the log and remove the second ldf file I created.
After opening my eyes this problem I encountered was a very basic problem and I wanted to share my experiences in hopes that someone can use it and fix a problem quickly or even better fix a problem before it becomes a problem.
After this happened I went through and altered by log file sizes for my memory optimized labs for this application. Let me know if you have seen the error before.
Lesson Learned, Cheers! – Justin
Written by: Justin Figg a Sr. SQL Server DBA with over 10 years of IT experience with Windows OS, Windows Server and Microsoft SQL Server. Subscribe to receive notifications of newly posted blog entries.
Support VitaminDBA.comThe post SQL Server Transaction Log is full due to XTP Checkpoint Error 9002, Severity 17, State 16 appeared first on VitaminDBA.
