October 31, 2008 at 6:45 am
What happens when "the db's broke" and you can't run a log backup? Say for instance if you lose a disk controller, or disks or a fire engulfs your server etc...
what then?
If you take a nightly Full backup and take log backups periodically throughout the day that you store on another machine you have a much better chance of recovering more data than you would with your current schedule.
Worse yet, what happens when you do your full backup, and it's invalid, corrupted or something else? now you're out 2 days worth of data.
But then again I'm just a bit on the paranoid side when it comes to these things...
October 31, 2008 at 7:22 am
Luke L (10/31/2008)
Nice Article Gail, I did have one question though. Perhaps it's just me but it's been something I've seen here at SSC a few times in recent months...what was your last sentence supposed to say? All I see is
I hope this has clarified some of the details of what the
Blame the editor. 😉 The last sentance is supposed to read
I hope this has clarified some of the details of what the transaction log is, how it’s used and how it should be managed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2008 at 7:24 am
Very nice article Gail. Clear, concise, and to the point. Sounds like a follow-up on how the checkpoint and lazy writer work would be good.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 31, 2008 at 7:27 am
From all of the replies, Gail, you seem to have scratched an itch that a lot of people have (including me!)
One thing I'm not absolutely clear on though... you mentioned the "daisy chain of logs" in recovery. If I have a full backup every night and have transaction log backups every 6 hours, in case of a recovery need, do I need every log backup along with the last full backup or only the latest log backup along with the last full backup?
Thanks again for a good article!
Bob
October 31, 2008 at 7:33 am
Karrasko's Co. (10/31/2008)
What do you think about this method?
I think it won't do what you think it will. If you truncate the transaction log then you can neither take a tran log backup nor do any type of point-in-time restore afterwards until another full or diff backup is run.
If your DB failed and you tried to run a log backup, you would get this error (SQL 2005)
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
On SQL 2000, it's worse. The log backup will succeed, but it will not be usable. The truncate throws away log records. In order to restore a log backup, the log chain must be intact. Because the log records are missing, the log chain is not intact.
If you have any form of disaster with the backups run like that, you will be loosing up to a day's data. If that's acceptable, set the DB to simple recovery, because that's essentially what you're in now. If not, set up regular log backups.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2008 at 7:33 am
Bob Bridges (10/31/2008)
I need every log backup along with the last full backup or only the latest log backup along with the last full backup?Bob
All log backups are necessary.
October 31, 2008 at 7:35 am
Bob Bridges (10/31/2008)
One thing I'm not absolutely clear on though... you mentioned the "daisy chain of logs" in recovery. If I have a full backup every night and have transaction log backups every 6 hours, in case of a recovery need, do I need every log backup along with the last full backup or only the latest log backup along with the last full backup?
All of them. A log backup only contains the log entries since the previous log backup (or full backup if there is no previous log backup). To restore, you need the full backup and then all of the log backups, in sequence, since that full backup.
It's differential backups where only the latest is needed, because diffs contain the data changes since the last full backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2008 at 7:36 am
Jack Corbett (10/31/2008)
Very nice article Gail. Clear, concise, and to the point. Sounds like a follow-up on how the checkpoint and lazy writer work would be good.
Thanks. Glad you liked it.
I'll put the suggestion on the list. Currently I'm contemplating an article 'Help, my database is corrupt, what do I do?'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2008 at 7:39 am
GilaMonster (10/31/2008)
Jack Corbett (10/31/2008)
Very nice article Gail. Clear, concise, and to the point. Sounds like a follow-up on how the checkpoint and lazy writer work would be good.Thanks. Glad you liked it.
I'll put the suggestion on the list. Currently I'm contemplating an article 'Help, my database is corrupt, what do I do?'
Pray your backups are good and restore? That's a good one too, although one I hopefully don't have to deal with again.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 31, 2008 at 7:47 am
Jack Corbett (10/31/2008)
GilaMonster (10/31/2008)
I'll put the suggestion on the list. Currently I'm contemplating an article 'Help, my database is corrupt, what do I do?'Pray your backups are good and restore? That's a good one too, although one I hopefully don't have to deal with again.
That's the ideal. Probably 2/3 of the article would have to be for those without backups though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2008 at 8:09 am
If i was to look at a transaction log, say for one particular transactions, what would I see?
Taking a data update as an example, I was once told that that every record changed within the transaction is represented in the log with: a copy of the record pre-update; a copy of the record post-update; and the command that updated the record. Such that an update to 10 records would contain 30 entries in the log. Is this the case?
If so, upon rollback, what does sql server do? Is it something like ... one record at a time, work out the difference between the before and after records and then determine and execute the relevant update needed to reverse the transaction?
Been meaning to use one of those neat little tools to look inside a tlog and find out for myself but haven't yet got arond to it!
Many thanks.
James
October 31, 2008 at 8:30 am
jmanly (10/31/2008)
Taking a data update as an example, I was once told that that every record changed within the transaction is represented in the log with: a copy of the record pre-update; a copy of the record post-update; and the command that updated the record. Such that an update to 10 records would contain 30 entries in the log. Is this the case?
Not exactly. I did a quick test.
update TblTest set SupplierId = SupplierId + 1
tbltest has 4 rows in it. 1 column only, heap with a nonclustered index.
The tran log had 13 rows in it for that update. I'm not going to reproduce the entire thing (there are too many data columns), but this is the gist.
Operation Context AllocUnitName
LOP_MODIFY_ROW LCX_HEAP dbo.TblTest
LOP_DELETE_ROWS LCX_MARK_AS_GHOST dbo.TblTest.idx_test
LOP_SET_BITS LCX_PFS dbo.TblTest.idx_test
LOP_INSERT_ROWS LCX_INDEX_LEAF dbo.TblTest.idx_test
LOP_MODIFY_ROW LCX_HEAP dbo.TblTest
LOP_DELETE_ROWS LCX_MARK_AS_GHOST dbo.TblTest.idx_test
LOP_INSERT_ROWS LCX_INDEX_LEAF dbo.TblTest.idx_test
LOP_MODIFY_ROW LCX_HEAP dbo.TblTest
LOP_DELETE_ROWS LCX_MARK_AS_GHOST dbo.TblTest.idx_test
LOP_INSERT_ROWS LCX_INDEX_LEAF dbo.TblTest.idx_test
LOP_MODIFY_ROW LCX_HEAP dbo.TblTest
LOP_DELETE_ROWS LCX_MARK_AS_GHOST dbo.TblTest.idx_test
LOP_INSERT_ROWS LCX_INDEX_LEAF dbo.TblTest.idx_test
If so, upon rollback, what does sql server do? Is it something like ... one record at a time, work out the difference between the before and after records and then determine and execute the relevant update needed to reverse the transaction?
Pretty much. It will execute compensating changes to get the rows and pages back to how they were. It's not so much it executes an update, there's no need. It knows the old values and can directly change the pages as necessary (as it did to make the changes in the first place)
Been meaning to use one of those neat little tools to look inside a tlog and find out for myself but haven't yet got arond to it!
No need to buy anything.
SELECT * from fn_dblog(null, null)
Note. This command is undocumented and is not guaranteed to appear in future versions. Do not use this on production servers. The log structure will likely change in future versions, do not write code that depends on this command or the structure of its results.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2008 at 8:47 am
GilaMonster (10/31/2008)
That's the ideal. Probably 2/3 of the article would have to be for those without backups though.
[sarcasm]
What? You mean someone may not have backups? That never happens does it?
[/sarcasm]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 31, 2008 at 8:52 am
Great article, Gail. I have always found this subject difficult: initially, to understand myself, and then later, to explain to customers. Now I can just point them to this article, thanks!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 31, 2008 at 9:55 am
Great article. I find myself struggling to explain these concepts to people on a weekly basis. You've put everything very clearly.
Viewing 15 posts - 16 through 30 (of 128 total)
You must be logged in to reply to this topic. Login to reply