November 20, 2013 at 11:12 am
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
November 20, 2013 at 11:43 am
Is another process also setting it to simple ? It should be left in full all the time.
November 20, 2013 at 11:48 am
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.
November 20, 2013 at 11:51 am
With changing the recovery model to Simple you've broken the backup chain.
Igor Micev,My blog: www.igormicev.com
November 20, 2013 at 11:54 am
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.
November 20, 2013 at 12:31 pm
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
November 20, 2013 at 12:41 pm
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 ?
November 20, 2013 at 1:44 pm
runs every 2 hours from 5 am to 5 pm (7 times)
weekdays
no not at the same time nor pattern
Cheers,
John Esraelo
November 20, 2013 at 1:51 pm
Full of course as I have mentioned before. .
Cheers,
John Esraelo
November 20, 2013 at 2:13 pm
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
November 20, 2013 at 2:24 pm
see the attached
let me know if you have any question(s)..
thx guys
Cheers,
John Esraelo
November 20, 2013 at 3:52 pm
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
November 20, 2013 at 9:34 pm
Hi,
seems to me somewhere your recovery model changes to simple thus breaking log chain, investigate if there any bulk operations changing recovery model.
November 20, 2013 at 9:46 pm
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.
November 20, 2013 at 9:48 pm
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 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply