Post reply

Transaction log backup troubles

  • November 3, 2006 at 6:58 am

    #117357

    I have a SQL server 2005 database that is having some problems doing transaction log backups. I run a night full transaction log backup followed by a full databasr backup to disk at 11 PM.

    code:BACKUP LOG ACTTL TO acttl_log_backup WITH INIT

    BACKUP DATABASE ACTTL TO acttl_db_backup WITH INIT

    Everyday starting at 8 AM I start doing hourly log backups that append the current log backup file. For some reason every 3 days I receive the following error when the first log backup tries to execute:

    BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

    Once I complete another full db backup the log backups run fine for 3 more days. Why is SQL stating that there is no full database backup when there is? Am I missing a setting somewhere? I checked the media retention setting and it is at the default of 0. I am at a complete loss. Any thoughts would be greatly appriciated.

    Thanks,

    Jared

  • November 6, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • November 7, 2006 at 5:08 am

    #670258

    I forgot to mention that the backups are being stored on a 'disk" presented from a SAN so there is no problem with high IO's on the backup disk. I am kind of assuming I will have to take this to Microsoft. Any thoughts?

  • July 4, 2007 at 7:52 am

    #717167

    Yeah please any posts are welcome. I'm having a similar problem, but slightly different though

    I run full backups daily in the eve and tranaction log backups daily every 4hrs. All my DB's are set to full recovery mode. The transaction log backups are failing on 6 DB's 5 of which whose collation is not set to the standard SQL_Latin1_Gen_CP1_CI_AS and the 1 DB whose collation is set to the standard but the transaction logs keep on failing anyway .....

    Please help

  • July 4, 2007 at 8:04 am

    #717173

    Do you mean to say that the TLOG backup jobs are failing from maintenance plan. can yo post us the error from the plan that you get.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • December 12, 2007 at 3:14 pm

    #759619

    I had this problem but because my data backup was a couple of hours old and the recovery model on the databases was changed from Full to Simple.

    Here is what I did:

    1. deleted the transaction log bak file

    2. Ran a full backup of my data

    3. Ran a backup of the transaction log.

    After that my scheduled job started working

    Hope this works for you.

  • December 13, 2007 at 4:47 am

    #759764

    Jared,

    Is there anything in your system that changes the recovery model of your database to Simple or Bulk Logged? If this runs between yuor DB backup and your log backup, it will cause the message you see. This will happen even if you switch back to Full Recovery before your tran log backup.

    If you do switch recovery modes, you will need to run a full or differential DB backup to provide a starting point for recovery.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • May 21, 2008 at 5:24 pm

    #818925

    I am having the same problem, two of out 7 database are fialing with the same error.

    I have backups from the night before and it runs fine for 3 days. Then it fails , complaining that there is no current backup when there is???

    Did you get it resolved?

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

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