SQL: BACKUPS::

Question

Post reply

SQL: BACKUPS::

John Esraelo-498130

SSCertifiable

Points: 5914
More actions
November 20, 2013 at 11:12 am

#286446

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

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

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

homebrew01 SSC Guru Points: 55544 More actions · Answer 1

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

Ed Wagner SSC Guru Points: 287024 More actions · Answer 2

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 3

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

Igor Micev,My blog: www.igormicev.com

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 5

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

homebrew01 SSC Guru Points: 55544 More actions · Answer 6

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 ?

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 7

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

weekdays

no not at the same time nor pattern

Cheers,
John Esraelo

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 8

Full of course as I have mentioned before. .

Cheers,
John Esraelo

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 9

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

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 10

see the attached

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

thx guys

Cheers,
John Esraelo

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 11

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

PravB4u Default port Points: 1401 More actions · Answer 12

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

burfos Ten Centuries Points: 1141 More actions · Answer 13

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.

burfos Ten Centuries Points: 1141 More actions · Answer 14

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.