locked
Backup Log Plan Failure RRS feed

  • Question

  • Situation:

    • Full backups are performed nightly at 10PM
    • Transaction backups are performed every 4 hrs starting at 4AM up to 8PM

    Problem:
      Every Thursday i get this error:

    Executing the query "BACKUP LOG [?] TO DISK = N'[backup path & name].trn' WITH NOFORMAT, NOINIT, NAME = N'[backup name]', SKIP, REWIND, NOUNLOAD, STATS = 10" failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    This error message occurs on two databases ONLY for the last month, or so.

    Initially i thought the problem was due to a lack of log file pointers, as we were performing manual backups and shrink the log file. But for the last two weeks, we have not done so as they increased their storage space for the data and log files.

    Question:
    What would be causing it to work every day except on thursday, and even then only the times between 8AM-8PM. It successfully runs on the first iteration at 4AM but fails every iteration after that till its daily schedule is complete and then successfully runs on Friday, and thereafter, as scheduled.


    "I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.
    Monday, July 11, 2011 2:22 PM

Answers

  • Get-EventLog -LogName Application | Where {$_.Source -eq 'MSSQLSERVER'} | Select Message, EventID, TimeGenerated -First 10

    NOTE: The above will fish this out using PowerShell; should you have PowerShell installed on the database server.

    Yea, I think so. SQL Server logs backups (including those where you only truncate the transaction log) and recovery mode changes to the event log.

    P.S. Take Tibork's note to heart. If you're running 2k8, you can disregard manual truncation as a scenario.

    Good luck bro! You'll get it.

    ~CA


    Adam
    • Proposed as answer by Siva1983 Monday, July 11, 2011 4:59 PM
    • Marked as answer by Alex Feng (SQL) Wednesday, July 20, 2011 3:15 AM
    Monday, July 11, 2011 3:58 PM
  • Ah, since you are on 2005, then there are two possibilities. Actually, three since the NO_LOG option does the same as TRUNCATE_ONLY. But using any of those options will produce a message in the eventlog and SQL Server errorlog file, as will changing recovery model (I believe, easy to test). So you sould be able to just go look for that in either of those logs (whcih is more convenient for you).
    Tibor Karaszi, SQL Server MVP | web | blog
    Tuesday, July 12, 2011 12:20 AM

All replies

  • What else happens on those thursdays? Do you have maintenance plans, other sql server agent jobs, and other scheduled automatic or manual job happening at that time?

    If not sure I'd use SQL Server Profiler to capture what's going on.


    Well, I should really put some here... *smiles
    Monday, July 11, 2011 2:32 PM
  • Nothing else that is time consuming...most everything that occurs in/around the time that this plan is operating lasts maybe 5 to 15 mins, and is database specific operation and does not include either of these two failing databases.

    the plan is an "All user database" inclusion plan.  As far as i can tell it hits these two, fails out and catches the others from manually looking at the backup directory files.

    In either case, the jobs that would potentially interrupt the process are running in/around the same time the other 6 days.  So its not tied to any other Job/plans.


    "I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.
    Monday, July 11, 2011 2:40 PM
  • Hey goldbishop!

    Na, the problem isn't too out there, but the error message is a little confusing. I some way and some how the login chain is being broken. This can happen a few different ways.

    1. Attempt to create a transaction log backup without having a prior full backup.
    2. Attempt to create a transaction log backup after having truncated the log.
    3. Attempt to create a transaction log backup after having changed the database's recovery mode.

    I'm sure there are others, but these are the cases I could think of-off the top of my head. But, listen, don't take my word for it. Run the scenarios:

    --Preparatory work: create database and enable full recovery
    CREATE DATABASE [BoogerFish];
    GO
    ALTER DATABASE [BoogerFish] SET RECOVERY FULL;
    GO
    
    --Scenario #1: Attempt to Create a Transaction Log Backup 
    --without a Preceding Full Backup
    BACKUP LOG [BoogerFish] TO DISK = 'BoogerFish)_log.bak';
    GO
    
    --Scenario #2: Attempt to Create a Transaction Log Backup 
    --after truncating the log.
    BACKUP LOG [BoogerFish] TO DISK = 'BoogerFish' WITH TRUNCATE_ONLY;
    GO
    BACKUP LOG [BoogerFish] TO DISK = 'BoogerFish_3_log.bak';
    GO
    
    --Scenario #3: Attempt to Create a Transaction Log backup
    --after changing the database's recovery mode.
    ALTER DATABASE [BoogerFish] SET RECOVERY SIMPLE;
    GO
    ALTER DATABASE [BoogerFish] SET RECOVERY FULL;
    GO
    BACKUP LOG [BoogerFish] TO DISK = 'BoogerFish_1_log.bak' WITH INIT;
    GO
    
    BACKUP LOG [BoogerFish] TO DISK = 'BoogerFish_3_log.bak' WITH INIT, NO_TRUNCATE;
    GO
    
    BACKUP LOG [BoogerFish] TO DISK = 'BoogerFish_4_log.bak' WITH INIT, NO_TRUNCATE;
    GO
    

    In all these cases, you can fix the problem by creating a new full backup then creating new logs from that backup.

    BACKUP DATABASE [BoogerFish] TO DISK = 'BoogerFish_full.bak' WITH INIT;
    GO
    BACKUP LOG [BoogerFish] TO DISK = 'BoogerFish_1_log.bak' WITH INIT;
    GO
    BACKUP LOG [BoogerFish] TO DISK = 'BoogerFish_2_log.bak' WITH INIT;
    GO
    

    So, considering the question from you initial note, think about changing it to "What is causing the log chain to break on Thursday?" I'd answer by identifying causes of the log break (given you some here) then search your instance (if need-be, interacting environments too) for instances of these log-breaking scenarios that occur prior to the backup failure on Thursday. You've got your work cut out for you.

    I hope this helps bro!

    CA-


    Adam
    Monday, July 11, 2011 3:24 PM
  • Yeah i thought about the log-break, i havent looked at the Windows Event tracker but i should be able to see if any other operation is being manually performed there right?

    I increased the iteration from 4 hours to 1 hour to see if there it only occurs at certain hours during the day or if its just those 4hr time(s).

    Only time will tell this Thursday when i start actively monitoring the server.


    "I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.
    Monday, July 11, 2011 3:30 PM
  • Just FYI, I concur with Adam's viewpoint. The problem should be that something is setting the db to simple recovery and back to full again. In earlier versions, it could also have been "BACKUP LOG WITH TRUNCATE_ONLY" command, but that has been removed in 2008.
    Tibor Karaszi, SQL Server MVP | web | blog
    Monday, July 11, 2011 3:40 PM
  • Get-EventLog -LogName Application | Where {$_.Source -eq 'MSSQLSERVER'} | Select Message, EventID, TimeGenerated -First 10

    NOTE: The above will fish this out using PowerShell; should you have PowerShell installed on the database server.

    Yea, I think so. SQL Server logs backups (including those where you only truncate the transaction log) and recovery mode changes to the event log.

    P.S. Take Tibork's note to heart. If you're running 2k8, you can disregard manual truncation as a scenario.

    Good luck bro! You'll get it.

    ~CA


    Adam
    • Proposed as answer by Siva1983 Monday, July 11, 2011 4:59 PM
    • Marked as answer by Alex Feng (SQL) Wednesday, July 20, 2011 3:15 AM
    Monday, July 11, 2011 3:58 PM
  • Hi Adam,

    Much appreciate your approach and explanation !!!


    Sivaprasad.L Together We can Achieve
    Monday, July 11, 2011 5:00 PM
  • TiborK,

      Well concidering the server is 2k5, that would partially answer your concurrence.  But wouldnt the TRUNCATE_ONLY be effective all 7 days, not just 1 day?


    "I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.
    Monday, July 11, 2011 7:23 PM
  • Did not think about using PS.  Still need to read up on the syntax for the PS language structure.

    Ill throw your PS script into the engine and see what gets spit out.


    "I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.
    Monday, July 11, 2011 7:26 PM
  • Ah, since you are on 2005, then there are two possibilities. Actually, three since the NO_LOG option does the same as TRUNCATE_ONLY. But using any of those options will produce a message in the eventlog and SQL Server errorlog file, as will changing recovery model (I believe, easy to test). So you sould be able to just go look for that in either of those logs (whcih is more convenient for you).
    Tibor Karaszi, SQL Server MVP | web | blog
    Tuesday, July 12, 2011 12:20 AM