locked
The transaction log for database [mydb] is full due to 'LOG_BACKUP' RRS feed

  • Question

  • I have an ETL process that is scheduled to run in the wee hours of the morning. The last few days it has been failing with the error "The transaction log for database [mydb] is full due to 'LOG_BACKUP'"

    So we have had issues with having to increase the TLog space, but if I run this same ETL process a little later in the morning, then it completes with just barely enough room, leaving just a few MB left on the TLog free after completing.  It completes with no errors.

    Typically when I see issues with the TLog running out of space I start looking at the size of the data flows being used. 

    So now I am considering something else.  Could it be that the TLog isn't getting backed up ?

    I ran this, and my Db is getting its Tlog backed up every 3 hours, and the time when this job runs there should have been plenty of time for another run of the 

    select database_name, backup_finish_date
    from msdb..backupset
    where type = 'L'
    ORDER BY backup_finish_date desc

    So what else can I check?

    Friday, February 14, 2020 3:41 PM

All replies

  • Can you let me know recovery model of the database which is giving the log backup error, I have a hunch it is in simple ? 

    What I think is at the time your ETL runs some other job like index maintenance is also running and causing massive log file growth, since this is simple recovery log truncation is hampered due to index activity and both combined causes the error. While when you do it later the log file gets time to get truncated. My guess


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, February 14, 2020 4:58 PM
  • Recovery model is FULL
    Friday, February 14, 2020 5:41 PM
  • Recovery model is FULL
    Then in that case their is log backup happening somewhere in between the original time and the new time when error is not coming. May be you should see if you are taking enough log backups

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, February 14, 2020 6:02 PM
  • Hi shiftbit,

    ---step1:Check the log usage and database status now:

    DBCC SQLPERF(LOGSPACE)
    
    GO
    
    SELECT name, recovery_model_desc, log_reuse_wait,log_reuse_wait_desc 
    
    FROM sys.databases
    
    GO

    values and description: FactorsThatDelayTruncation

    ---step2:find solution based on the cause

    Causes and solutions for the log growth:

    1. The database recovery model is not a simple model, and no log backup is scheduled;

    Solution: Schedule the log backup job (your case has done so we can rule out this)

    2. There is a transaction on the database that has not been committed for a long time;

    Solution: Find out the transaction that has been completed but not committed (DBCC OPENTRAN), kill this transaction.

    3. A large transaction is running on the database, such as a user is building / rebuilding an index,or using DELETE / INSERT statements to delete or insert a large amount of data, 

    or the user has opened a server cursor but did not take the data away in time ;

    Solution: Optimize the statement and see if it can be divided into transactions.

    More information about this error: troubleshoot-a-full-transaction-log-sql-server-error-9002

    You can also reference this case: how-to-fix-this-the-transaction-log-for-database-searchservicedb-is-full-due-to-logbackup


    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, February 17, 2020 6:11 AM
  • 2. There is a transaction on the database that has not been committed for a long time;

    Solution: Find out the transaction that has been completed but not committed (DBCC OPENTRAN), kill this transaction.

    Really Kill the transaction, are you serious ? Should you not suggest to see what the transaction is actually doing, what if it is in explicit transaction and is about to complete, killing will open hornets nest and rollback would make things very tough.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, February 17, 2020 6:15 AM
  • Hi shiftbit,

    Do you solve the issue?

    Best Regards.

    yuxi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, February 21, 2020 7:41 AM
  • What are you using for your ETL process?

    There are ways to minimize the usage of the transaction log during the load process that may help in your situation, but it depends on how you are actually loading the data.

    For example, if you are using SSIS and the OLE DB Destination - then you can control the batch/commit sizes to reduce the impact on the transaction log.  However, if you are using a linked server then you have no control...

    I would also recommend reviewing why the database is set to full recovery model.  Is that really necessary - or can it be switched to simple?  In most cases, the destination for an ETL does not need to be in full recovery model because you can rebuild it by restoring the latest backup and reloading the data since that backup.

    If you do require full recovery model - then you definitely need to increase the frequency of the transaction log backups.  Backing that up every 3 hours does no good...it can take just a few minutes of a data load to fill a transaction log - it all depends on how much data is loaded during the ETL process.  And - since you are only backing it up every 3 hours none of the space in the transaction log can be marked as reusable until the next transaction log backup is run.

    You could also look at changing the recovery model during your ETL process to bulk-logged.  This might reduce the amount of space in the transaction log - and could (depending on how your loads are built) - be minimally logged.


    Jeff Williams

    Saturday, February 22, 2020 4:56 PM