SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transaction Log Backup Job Failure - does it break chain?


Transaction Log Backup Job Failure - does it break chain?

Author
Message
Paul S. Huebner
Paul S. Huebner
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 82
Never had a "set-in-stone" answer on this one and I've, fortunately, never had to find out the hard way, but if a transaction log backup job fails, for any reason, does this failure constitute a "break in the chain" of t-log backups?

Please advise...thanks!
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96003 Visits: 33013
It kind of depends on the type of log backup failure, but yes, some of them will break the recovery chain. You'll pretty much know the next time you go to run the log backup. If it fails, you're in a broken chain situation.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Paul S. Huebner
Paul S. Huebner
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 82
OK, then for example, the disk array that my t-log backups are pointed to will be unavailable for approx. 30-mins for updates to be applied. If they are scheduled for backups every 10-mins, will the subsequent t-log backup that will occur after the array is available backup all transactions since the last good t-log backup and, therefore, not break the chain? Or will all be lost and I should do a full backup at that time? I hope I am explaining my concern clearly...
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96003 Visits: 33013
If you just put a delay in the transaction log backups during that period you can avoid the issue. That's what I'd do. Otherwise, no, I don't think a driving being compltely unavailable will cause the backup chain to break. You will get a failure, but you won't lose the chain because of that error.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18563 Visits: 10042
There is a very small possibility that you could be in the middle of a transaction log backup - and the drive becomes unavailable just at the right time to cause that backup file to be corrupted.

This would cause the backup to appear to be successful - but, your log chain would still be broken.

Grant's advise on suspending the transaction log backups during this time is the best way to avoid that kind of issue - or any issues with the log chain.

If the drive is not available when the log backup starts, it will fail but it will not cause a break in the log chain. The real concern here is that you are not in the process of performing a log backup when the drive become unavailable - which could cause a break in the log chain and/or a corrupted backup file (which also breaks your log chain).

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96003 Visits: 33013
Jeffrey Williams-493691 (2/28/2011)
There is a very small possibility that you could be in the middle of a transaction log backup - and the drive becomes unavailable just at the right time to cause that backup file to be corrupted.

This would cause the backup to appear to be successful - but, your log chain would still be broken.

Grant's advise on suspending the transaction log backups during this time is the best way to avoid that kind of issue - or any issues with the log chain.

If the drive is not available when the log backup starts, it will fail but it will not cause a break in the log chain. The real concern here is that you are not in the process of performing a log backup when the drive become unavailable - which could cause a break in the log chain and/or a corrupted backup file (which also breaks your log chain).


good catch. I hadn't thought of that. It might break the chain in that event.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18563 Visits: 10042
Yes - and that is the problem...it might break the log chain, but no way to be sure. So, to be safe - I would not recommend running transaction log backups during the outage or I would modify the location to another drive temporarily.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

dkamalkumardba
dkamalkumardba
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 35
Hi i have some posible options in my hand that is


*With out taking full backup we cant take log backup so the full backup was fail automaticaly log backup also fail

*Changing the recovery model from full to simple

*when database goes into suspect mode\offline

*when the disk space is full
Hemant.R
Hemant.R
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 240
hi
yes it will break the chain.
t-log backup job failure break t-log chain

below are different reason,

1st if any one taken full backup of same database where t-log job is running,in this case u want take full backup dont want to break chain of t-log then use copy_only backup

2nd if anyone change recovery model of database

3.if someone executed backup log with truncate_only

in this case you can use trace flag 3031 ,3231 it wont break t-log backup chain
chewychewy
chewychewy
Say Hey Kid
Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)

Group: General Forum Members
Points: 702 Visits: 520
Hi,

1st if any one taken full backup of same database where t-log job is running,in this case u want take full backup dont want to break chain of t-log then use copy_only backup
--> i thought full backup and transaction logs backup are independent? full backup shouldn't break the chain of t log?

thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search