Sql-server – The transaction log for database ‘SolarWindsOrion’ is full due to ‘XTP_CHECKPOINT’

sql serversql-server-2017transaction-log

To start, I am a database novice. I thank you for any help in advance.

I have a solarwinds SQL windows 2017 database in full recovery mode in an availability group. The database is roughly 2GB.

Yesterday, I went through the process of shrinking my log file that had continuously grown to ~120gb.

To prevent this issue in the future I:

  • Configured the log file for an auto growth of 1gb and max size of 2gb (based on some solarwinds recommendations I had found)
  • Scheduled regular Transnational log backups every 30sec (again based on some recommendations I had found)

… well, i accidentally configured the log backups to be every 30 minutes in error and i believe that is what then allowed the log file to fill to it's maximum size?

I am now receiving the error "The transaction log for database 'SolarWindsOrion' is full due to 'XTP_CHECKPOINT'"

I am unable to shrink the log file and I am also unable to update the log file to a bigger size (get the same error message)

I am wondering what the best course of action is and if there is anything else i need to do to prevent this issue in the future once resolved.

Best Answer

I am wondering what the best course of action is and if there is anything else i need to do to prevent this issue in the future once resolved.

As Kin Shah mentioned,

To get you out, change the log size to unlimited, run a checkpoint and take a log backup (probably few). Why cap the tlog size ?

Setting the log size to unlimited and not capping it at 2GB whilst taking regular log backups should fix your problem.

In your case, that was not possible yet.

I get the same error when trying to change the size to unlimited.

The error

The transaction log for database 'SolarWindsOrion' is full due to 'XTP_CHECKPOINT'"

A temporary solution could be adding another log file, taking a log backup & changing the size of the original log file.

An example of creating the log file, taking a log backup, changing the size to unlimited and removing the log file.

USE [master]
GO
ALTER DATABASE [DB_NAME] ADD LOG FILE ( NAME = N'DB_log2', FILENAME = N'F:\Log1\DB_log02.ldf' , SIZE = 65536KB , FILEGROWTH = 65536KB );
GO
ALTER DATABASE [DB_NAME] MODIFY FILE ( NAME = N'DB_log', MAXSIZE = UNLIMITED);
GO
BACKUP LOG [DB_NAME] TO DISK = '\\LogBackupLocation\DB_LogIssue.trn';
USE [DB_NAME]
GO
ALTER DATABASE [DB_NAME]  REMOVE FILE [DB_log2]
GO

--The file 'DB_log2' has been removed.

That seems to have done the trick. As far as best approach moving forward. Now that i have the transnational backups happening every 30secs (as originally planned) should that prevent this issue from happening in the future?

Depends on the Always On Availability Group settings and the size of some transactions if you keep the max size at 2GB.

Setting the max size of your log file to unlimited and on a separate log drive where it could grow should be best. As far as scheduling log backups go, 30 seconds should be pretty good.

Related Question