<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Backups
»
Transaction Log Backup Job Failure - does it...
11 posts, Page 1 of 2
1
2
»»
Transaction Log Backup Job Failure - does it break chain?
Rate Topic
Display Mode
Topic Options
Author
Message
Paul S. Huebner
Paul S. Huebner
Posted Monday, February 28, 2011 10:59 AM
Grasshopper
Group: General Forum Members
Last Login: Monday, January 09, 2012 9:29 AM
Points: 21,
Visits: 67
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
Grant Fritchey
Grant Fritchey
Posted Monday, February 28, 2011 1:34 PM
SSChampion
Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,375,
Visits: 25,159
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
Paul S. Huebner
Paul S. Huebner
Posted Monday, February 28, 2011 1:39 PM
Grasshopper
Group: General Forum Members
Last Login: Monday, January 09, 2012 9:29 AM
Points: 21,
Visits: 67
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
Grant Fritchey
Grant Fritchey
Posted Monday, February 28, 2011 2:38 PM
SSChampion
Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,375,
Visits: 25,159
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
Jeffrey Williams 3188
Jeffrey Williams 3188
Posted Monday, February 28, 2011 3:52 PM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Friday, May 17, 2013 11:21 AM
Points: 4,317,
Visits: 9,216
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
Grant Fritchey
Grant Fritchey
Posted Monday, February 28, 2011 4:00 PM
SSChampion
Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,375,
Visits: 25,159
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
Jeffrey Williams 3188
Jeffrey Williams 3188
Posted Monday, February 28, 2011 8:15 PM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Friday, May 17, 2013 11:21 AM
Points: 4,317,
Visits: 9,216
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
dkamalkumardba
dkamalkumardba
Posted Friday, November 09, 2012 2:06 PM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, January 30, 2013 12:03 PM
Points: 1,
Visits: 34
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
Hemant.R
Hemant.R
Posted Tuesday, December 04, 2012 3:02 AM
SSC Rookie
Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 45,
Visits: 175
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
chewychewy
chewychewy
Posted Wednesday, January 16, 2013 6:34 AM
Valued Member
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:15 AM
Points: 72,
Visits: 286
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 »
11 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
