By Grady Negronida

2019-06-12 13:51:20 8 Comments

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.


@Randi Vertongen 2019-06-13 08:20:55

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]
ALTER DATABASE [DB_NAME] ADD LOG FILE ( NAME = N'DB_log2', FILENAME = N'F:\Log1\DB_log02.ldf' , SIZE = 65536KB , FILEGROWTH = 65536KB );
BACKUP LOG [DB_NAME] TO DISK = '\\LogBackupLocation\DB_LogIssue.trn';

--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.

@Grady Negronida 2019-06-13 19:14:15

One more quick question. My plan is to do weekly full backups and if we need to recovery from a backup use one of those backups. Not too worried about losing data. Is there a way I can schedule a cleanup task in Microsoft to remove just the log backups but keep the full backups? Log backups are really only being taken to truncate the log file. Whats the best course of action?

@Randi Vertongen 2019-06-13 19:32:27

@GradyNegronida If you really don't need them which I would still strongly advise against, you could use Ola Hallengren's backup solution and specify @CleanupTime = ... hours. In your case 1 or 0 could work. This automatically deletes the files older than the specified cleanuptime without too much hassle :).

@Grady Negronida 2019-06-17 12:09:52

My database log file is still growing very large. Over the weekend the log file has grown to be 48 GB where the database is only 1.8 GB. The log backups are running every 30 seconds but it doesn't appear to be truncating the log. What am i missing here?

@Randi Vertongen 2019-06-17 13:15:18

@GradyNegronida are the databases on the secondary node healthy and synchronized? You could check the synchronization performance (seconds) and see if it is far behind on applying the LSN's. What are your backup preferences? Are you also scheduling backups on the secondary? Ola hallengren's procedure needs to be running on all nodes as it might only be backing up the db on the secondary node due to backup preference settings.

@Grady Negronida 2019-06-17 15:38:42

Actually at the moment the secondary node in the AG is purposefully down. Could this be causing the issue?

@Randi Vertongen 2019-06-17 16:17:36

@GradyNegronida Yes it could, due to sql server waiting for the secondary replica to come online & apply the log records. As a result the log records are not backed up & 'truncated'

@Grady Negronida 2019-06-18 19:08:45

I was able to confirm that once i removed the other node from the Availability group i was able to reduce the size of the log file. Thanks!

Related Questions

Sponsored Content

2 Answered Questions

[SOLVED] Transaction log full due to log_backup

1 Answered Questions

[SOLVED] Unable to trace the transaction log space

3 Answered Questions

2 Answered Questions

2 Answered Questions

4 Answered Questions

[SOLVED] Simple model database transaction log full 'CHECKPOINT'

Sponsored Content