SQL: BACKUPS::

  • Hello team,

    Transactional backup is the topic.

    I have over dozens of databases that get backed up automatically (jobs)

    once a week full

    once a day differential

    and

    transactional, some hourly and some every other hour during the business hours.

    Except 1

    if I backup the full, then diff then the transactional.. everything is great.. and in fact the transactional may be fine for a day..

    and then

    it dies with the following message:

    Message

    Executed as user: BERG\srvsqlxservices. 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.

    Actions taken:

    Changed the hours on the schedules

    changed the DB options on recovery model to simple and then back to full

    did the full

    did the diff

    and

    did the tran ..

    everything great.. and it works nicely ..

    until the next day..

    I cannot figure this out..

    any help will be appreciated.

    thx

    Cheers,
    John Esraelo

  • Is another process also setting it to simple ? It should be left in full all the time.

  • homebrew01 (11/20/2013)


    Is another process also setting it to simple ? It should be left in full all the time.

    The error you're getting when attempting a transaction log backup is because you cannot take a log backup if there's no full backup that's been done. The transaction log backup backs up the transaction logs generated since the last full backup. If you don't have a full backup, you can't take a log backup.

    Homebrew has it right - leave it set to full recovery model. Setting it to simple breaks the transaction log chain so a point-in-time recovery isn't possible.

  • With changing the recovery model to Simple you've broken the backup chain.

    Igor Micev,
    My blog: www.igormicev.com

  • For more information on the transaction log, there's a stairway series at http://www.sqlservercentral.com/stairway/73776/ that may help. In particular, see the Level 3 article.

  • If I go ahead, right now, and run the full backup and then the transactional one then it is fine.. until the next day..

    same, if I run the full back up right now, then differential and lastly the transaction .. everything will be fine for few hours.. and then.. the drama kicks in ..

    of course having the recovery model set to full that is..

    😉

    it is a mystery.... and as I mentioned earlier.. I have more than dozens of DBs' backup setup the same way for the last few years..

    cannot figure this one out..

    Cheers,
    John Esraelo

  • How frequently do your transaction log backups run ?

    Is the failure random ? Same time every day ?

    When it fails, is the DB full or simple ?

  • runs every 2 hours from 5 am to 5 pm (7 times)

    weekdays

    no not at the same time nor pattern

    Cheers,
    John Esraelo

  • Full of course as I have mentioned before. .

    Cheers,
    John Esraelo

  • Is it possible some code somewhere to make some mess, like CHECKPOINT, or change the recovery model and get it back. I don't think option auto shrink is turned on...

    Strange situation.

    Igor Micev,
    My blog: www.igormicev.com

  • see the attached

    let me know if you have any question(s)..

    thx guys

    Cheers,
    John Esraelo

  • Another TRN is going to fire up in 10 minutes..

    and also

    going to keep an eye on it for another 24 hours and see what happens

    Cheers,
    John Esraelo

  • Hi,

    seems to me somewhere your recovery model changes to simple thus breaking log chain, investigate if there any bulk operations changing recovery model.



    Praveen D'sa
    MCITP - Database Administrator 2008
    http://sqlerrors.wordpress.com

  • Between the time it is working and the time it is breaking, there has to be some process that is causing the database to change recovery modes (or even possibly blow the database away and recreate it although this would be more unlikely)

    Recovery model changes should be captured in your errorlog. So first thing to do is to look at your error log and look for anything suspicious. you may find entries sucha as 'Setting database option recovery to SIMPLE for database xxxxx' or something similar.

    Check that out and let us know.

  • or another possible option is that the database is perhaps being restored by an automated process and then changed from simple to full recovery. Again check error logs and also the restorehistory table in the msdb database.

Viewing 15 posts - 1 through 15 (of 15 total)

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