2

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.

Improve this question
8
  • 1
    Is the log file limited to a certain size ? Did you try issuing a manual checkpoint ?
    – Kin Shah
    Commented Jun 12, 2019 at 14:03
  • The disk is not full and i receive the same error when i try to run a checkpoint. The log file is set to 2gb maximum and that has been reached which is where my issue lies.
    – Grady Negronida
    Commented Jun 12, 2019 at 14:08
  • 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 ?
    – Kin Shah
    Commented Jun 12, 2019 at 14:11
  • I get the same error when trying to change the size to unlimited. I configured that based on some solarwinds documentation "For small databases, you can use 2048 MB for Transaction Log Size and 1024 MB for Autogrowth" - support.solarwinds.com/SuccessCenter/s/article/…
    – Grady Negronida
    Commented Jun 12, 2019 at 14:19
  • 1
    “You can use” is not the same as “you can set an absolute hard cap to” - they’re talking about initial size, not the max size.
    – Aaron Bertrand
    Commented Jun 12, 2019 at 15:56

1 Answer 1

Reset to default
1

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.

Improve this answer
7
  • 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?
    – Grady Negronida
    Commented Jun 13, 2019 at 19:14
  • @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 :).
    – Randi Vertongen
    Commented Jun 13, 2019 at 19:32
  • 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?
    – Grady Negronida
    Commented Jun 17, 2019 at 12:09
  • @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.
    – Randi Vertongen
    Commented Jun 17, 2019 at 13:15
  • Actually at the moment the secondary node in the AG is purposefully down. Could this be causing the issue?
    – Grady Negronida
    Commented Jun 17, 2019 at 15:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.