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

  • sqlfriend

    SSC Guru

    Points: 52444

    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

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    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: 52444

    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 4 weeks ago by  sqlfriend.
  • John Mitchell-245523

    SSC Guru

    Points: 148769

    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: 55186

    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: 52444

    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: 996843

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

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

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