Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Transaction Log Backup Job Failure - does it break chain? Expand / Collapse
Author
Message
Posted Monday, February 28, 2011 10:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 9:04 AM
Points: 21, Visits: 76
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!
Post #1070682
Posted Monday, February 28, 2011 1:34 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 15,729, Visits: 28,132
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1070802
Posted Monday, February 28, 2011 1:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 9:04 AM
Points: 21, Visits: 76
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...
Post #1070805
Posted Monday, February 28, 2011 2:38 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 15,729, Visits: 28,132
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1070848
Posted Monday, February 28, 2011 3:52 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:05 PM
Points: 4,390, Visits: 9,534
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1070886
Posted Monday, February 28, 2011 4:00 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 15,729, Visits: 28,132
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1070892
Posted Monday, February 28, 2011 8:15 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:05 PM
Points: 4,390, Visits: 9,534
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1070950
Posted Friday, November 9, 2012 2:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 27, 2013 1:26 AM
Points: 2, 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
Post #1383233
Posted Tuesday, December 4, 2012 3:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:05 AM
Points: 47, Visits: 236

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
Post #1392317
Posted Wednesday, January 16, 2013 6:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 7, 2014 12:29 AM
Points: 93, Visits: 407
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
Post #1407807
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse