The transaction log for database 'mydb' is full due to 'LOG_BACKUP'

Question

The transaction log for database 'mydb' is full due to 'LOG_BACKUP'

sqlfriend

SSC Guru

Points: 52769
More actions
July 14, 2020 at 1:28 am

#3769613

I got an alert The transaction log for database 'mydb' is full due to 'LOG_BACKUP', it fills the log.
And I checked the database is 32 GB, and set to grow 64 mg for data file and log file. Unlimited growth.
I also do transaction log backup every 3 hours. and I see them successful.
This messages comes up since Sunday to Monday which is today, I did not see it before.
What does this mean?
thanks
August 13, 2020 at 7:11 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

It means you need to back up your transaction log. Maybe three hours isn't frequent enough. Something between five minutes and one hour is the most common frequency I see for log backups.

What do you get if you run this command?

SELECT TOP 10
  backup_start_date
, backup_finish_date
FROM msdb.dbo.backupset
WHERE database_name = 'mydb'
AND type = 'L'
ORDER BY backup_set_id DESC

John

sqlfriend SSC Guru Points: 52769 More actions · Answer 2

Capture

this is what I got, every 3 hours, but I changed yesterday to every 2 hours. I did not got this alert before, is that possible there maybe big data transactions on this server when I got these alerts.

Thanks

This reply was modified 3 years, 9 months ago by sqlfriend.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Yes, either one big transactions or lots of little ones. If it's lots of little ones then you can just back up your log more frequently, as I already suggested. If it's one big one then you need to expand your transaction log. Is this an OLTP database, or do you use it for reporting, or something else?

John

homebrew01 SSC Guru Points: 55539 More actions · Answer 4

You can look at the trans log backup file location, and see the sizes of the .TRN files.

I usually backup every 15 minutes.

Your 6:00 and 9:00 backups took 43 seconds, so there may have been large jobs running then.

sqlfriend SSC Guru Points: 52769 More actions · Answer 5

Thanks, this is ECM Opentext software database server, I did not manage it until today I found the alerts. Asked their team they said some contractors are loading some data using their ETL application into OpenText manually.

I made autogrowth of files to 200 mg instead of 64 mg, changed the transaction log backup to 2 hours from 3 hours.

It looks I may need to do more frequent backups if this happens again or more often.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 6

sqlfriend wrote:

Thanks, this is ECM Opentext software database server, I did not manage it until today I found the alerts. Asked their team they said some contractors are loading some data using their ETL application into OpenText manually.
I made autogrowth of files to 200 mg instead of 64 mg, changed the transaction log backup to 2 hours from 3 hours.
It looks I may need to do more frequent backups if this happens again or more often.

I do transaction log backups on my Dev boxes more often than that. I have a job that checks every 15 minutes. If the "log reuse" is waiting on a backup, then I back it up... otherwise, I skip it. It keeps from making log files when nothing has happened in them. It automatically helps absorb unexpected loads by wandering contractors.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)