Transaction Log Backup Failures

Question

Transaction Log Backup Failures

Ross McMicken

SSCarpal Tunnel

Points: 4394
More actions
May 22, 2008 at 2:53 pm

#71958

A question came up today on log backup failures. We run log backups hourly. On occasion, the backups fail (no one has sent me a copy of the failure reason). If a log backup fails, does the next log backup capture the missed hour, or is that one lost? Does anyone have a reference in BOL or MS? I haven't found a reference yet.

Viewing 5 posts - 1 through 4 (of 4 total)

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

zombi SSCrazy Points: 2605 More actions · Answer 1

have you set up a differential backup, if not, then this is the time just set it up so even if ur transaction log back up fails u just have a differential back up.

And why don't u create a job alter for ur self so no one has to tell anything to u , if something fails then u will be notified by mail.

mdm72 SSC Rookie Points: 27 More actions · Answer 2

If I may add a sub-question...

We backup our tlogs every 20 minutes, when the job fails (reason unimportant) we are notified via email/txt. Has anyone executed a scripted job to notify after a second failed job rather than the initial failure? Being that we backup every 20 minutes, we generally watch to see if the next backup succeeds and if so all is well, so to speak.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

zombi (5/22/2008)
have you set up a differential backup, if not, then this is the time just set it up so even if ur transaction log back up fails u just have a differential back up.
And why don't u create a job alter for ur self so no one has to tell anything to u , if something fails then u will be notified by mail.

I realize this response if 5 years old but there's no need for a differential backup just because of a single hourly log backup failure. The successful log backup after such a failure will contain all of the information for both hours.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

mdm72 (5/24/2013)
If I may add a sub-question...
We backup our tlogs every 20 minutes, when the job fails (reason unimportant) we are notified via email/txt. Has anyone executed a scripted job to notify after a second failed job rather than the initial failure? Being that we backup every 20 minutes, we generally watch to see if the next backup succeeds and if so all is well, so to speak.

Is it actually worth trying to do such a thing? You do get notified twice within 20 minutes and, personally, I want to be notified of all such failures. I check the reason on the first failure because it's not always something that will all then next cycle to be successful.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)