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,129 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.
12,711 questions
Active Directory
Active Directory
A set of directory-based technologies included in Windows Server.
5,854 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,816 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
  2. Erland Sommarskog 101K 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
  3. Rahul Randive 8,271 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
  4. CosmogHong-MSFT 22,941 Reputation points Microsoft Vendor
    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