How to fix the error: The transaction log for database is full due to 'log_backup'.

Bikram Sana 0 Reputation points
2023-05-09T05:17:22.2266667+00:00

Hi Team,

I am getting the below error:

The transaction log for database is full due to 'log_backup'.

Whereas SQL Always on Clustering has configured and for testing purpose we are monitoring for few days that is working or not properly even no transaction is running. Therefore, why log is getting full automatically??

Please assist me.

Azure SQL Database
Azure Virtual Machines
Azure Virtual Machines
An Azure service that is used to provision Windows and Linux virtual machines.
7,396 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,104 questions
Active Directory
Active Directory
A set of directory-based technologies included in Windows Server.
6,087 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Olaf Helper 42,561 Reputation points
    2023-05-09T06:06:23.82+00:00

    Hi Team,

    No team, this is a community forum with volunteers.

    Therefore, why log is getting full automatically??

    Can be caused by missing log backups, which releases the VLF, or a broken log shipning (replication). What do this query return:

    select name, log_reuse_wait_desc
    from sys.databases
    
    
    0 comments No comments

  2. Erland Sommarskog 103.8K Reputation points MVP
    2023-05-09T08:20:35.67+00:00

    we are monitoring for few days that is working or not properly even no transaction is running.

    How did you ascertain that? For instance, there is no index maintenance or similar things?

    You need to schedule log backups, that's seems obvious.

    0 comments No comments

  3. Rahul Randive 9,011 Reputation points Microsoft Employee
    2023-05-09T19:30:18.6666667+00:00

    Hi @Bikram Sana

    Thanks for your question.

    When the transaction log becomes full due to 'LOG_BACKUP'

    Gnerally SQL Server Database Engine issues a 9002 error.

    Please find below general recommendations when you are working with transaction log files issue. I hope those would be helpful to mitigate such issue.
    Manage the size of the transaction log file

    Let us know if this help or if you have any additional question.

    Thank you!

    0 comments No comments

  4. LiHongMSFT-4306 24,766 Reputation points
    2023-05-10T02:41:37.36+00:00

    Hi @Bikram Sana

    Referring to this similar thread: How to fix this 'The transaction log for database 'Search_Service_DB' is full due to 'LOG_BACKUP'.?

    SQL Server Database Engine issues this 9002 error when the transaction log becomes full. To avoid it in the future, you can use the methods below:

    • Backing up the log.
    • Freeing disk space so that the log can automatically grow.
    • Moving the log file to a disk drive with sufficient space.
    • Increasing the size of a log file.
    • Adding a log file on a different disk.
    • Completing or killing a long-running transaction.

    More information: Troubleshoot a Full Transaction Log (SQL Server Error 9002)

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments