July 20, 2012 at 12:35 pm
There was a slight typo in the original question, and I mistakenly added another in fixing it. It should have been:
12:10 AM - Transaction 1 on QotDDatabase begins
12:11 AM - Transaction 2 on QotDDatabase begins
12:12 AM - Transaction 2 on QotDDatabase commits
12:13 AM - Transaction 1 on QotDDatabase commits
12:14 AM - Full backup data reading of QotDDatabase begins
12:15 AM - Transaction 3 on QotDDatabase begins
12:16 AM - Transaction 3 on QotDDatabase commits
12:18 AM - Full backup data reading of QotDDatabase ends
12:19 AM - Transaction 4 on QotDDatabase begins
12:20 AM - Full backup of QotDDatabase ends
12:21 AM - Transaction 4 on QotDDatabase commits
In this case, 1, 2, and 3 are included, 4 is not as it starts after the data reading portion of the backup ends. I'll award back points to people since it was confusing.
July 20, 2012 at 12:39 pm
Miles Neale (7/20/2012)
I answered that only the answer of 2 was correct. All others had to be wrong since 1 never completes. I do not care about the rules of the entire backup process and all the conversation here about such, (which is great by the way and very educational). The answers were all predicated by the transactions listed in those answers. Only 2 was the most correct, it was the only one that was completely correct. To say that 1 ever was included in the backup is truly false, it had not committed at the completion of the backup so all answers that included 1 are false.Someone needs to adjust the points on this one,
Yes, that was how I stumbled upon the "correct" answer. None of them seemed to be correct and then noticed 2 commits for transaction 2 and assumed ...
July 20, 2012 at 1:24 pm
The question was which transaction changes will be contain in the full backup?
In my understanding all transaction changes until the point where backup finish are recorded in the backup file. Since transaction 4 committed after backup was finish therefore only the changes up to the point of backup finished from transaction 4 will be recorded.
Another story is when backup is used to restore database from backup file then ALL changes for transaction 4 contained in backup file will be rollback since transaction 4 did not have commit statement as other three transaction have.
Regards
Krystian
July 20, 2012 at 1:39 pm
krystian (7/20/2012)
The question was which transaction changes will be contain in the full backup?In my understanding all transaction changes until the point where backup finish are recorded in the backup file. Since transaction 4 committed after backup was finish therefore only the changes up to the point of backup finished from transaction 4 will be recorded.
Incorrect. On a busy system, the full backup would never end and constantly have new log entries. The log up to the end of the data reading portion of the backup is included. After that point, just the log from the beginning of the backup to the end of the data reading portion is written to the backup file.
If you look at my post a few back, that is the most recent, corrected, timeline of events.
July 20, 2012 at 1:49 pm
Steve Jones - SSC Editor (7/20/2012)
There was a slight typo in the original question, and I mistakenly added another in fixing it. It should have been:12:10 AM - Transaction 1 on QotDDatabase begins
12:11 AM - Transaction 2 on QotDDatabase begins
12:12 AM - Transaction 2 on QotDDatabase commits
12:13 AM - Transaction 1 on QotDDatabase commits
12:14 AM - Full backup data reading of QotDDatabase begins
12:15 AM - Transaction 3 on QotDDatabase begins
12:16 AM - Transaction 3 on QotDDatabase commits
12:18 AM - Full backup data reading of QotDDatabase ends
12:15 AM - Transaction 4 on QotDDatabase begins
12:20 AM - Full backup of QotDDatabase ends
12:21 AM - Transaction 4 on QotDDatabase commits
In this case, 1, 2, and 3 are included, 4 is not as it starts after the data reading portion of the backup ends. I'll award back points to people since it was confusing.
Sorry to be pedantic, Steve, to make sure -- you mean transaction 4 starts at 12:19, right?
July 20, 2012 at 1:53 pm
Also, you have to consider that SQL will stop a backup even if there are open transactions. I know because we have had a rogue developer who was given too much access by the CIO at my last job who more than once left a transaction open by mistake for days, but no one caught it at first because he wasn't hitting recent data (he was fixing old data). But we didn't get backup failures or backups going longer than normal, so SQL Server obviously at some point ignores open transactions and finishes the backup.
July 20, 2012 at 2:40 pm
jeff.mason (7/20/2012)
Steve Jones - SSC Editor (7/20/2012)
There was a slight typo in the original question, and I mistakenly added another in fixing it. It should have been:12:10 AM - Transaction 1 on QotDDatabase begins
12:11 AM - Transaction 2 on QotDDatabase begins
12:12 AM - Transaction 2 on QotDDatabase commits
12:13 AM - Transaction 1 on QotDDatabase commits
12:14 AM - Full backup data reading of QotDDatabase begins
12:15 AM - Transaction 3 on QotDDatabase begins
12:16 AM - Transaction 3 on QotDDatabase commits
12:17 AM - Full backup data reading of QotDDatabase ends
12:19 AM - Transaction 4 on QotDDatabase begins
12:20 AM - Full backup of QotDDatabase ends
12:21 AM - Transaction 4 on QotDDatabase commits
In this case, 1, 2, and 3 are included, 4 is not as it starts after the data reading portion of the backup ends. I'll award back points to people since it was confusing.
Sorry to be pedantic, Steve, to make sure -- you mean transaction 4 starts at 12:19, right?
Yes, this whole thing makes my head hurt. Too hard to edit this and not make a mistake somewhere.
July 20, 2012 at 6:01 pm
LOL
I say we throw the whole thing out like the question never existed, no one got it wrong and no one go it right. We'll just call it a free learning experience. 🙂
July 23, 2012 at 1:11 am
Ah, the benefit of turning up to this party a bit late.
Turned out to be a nice question for me on a sunny Monday morning! 😀
July 23, 2012 at 9:04 am
July 29, 2012 at 4:32 am
Sorry to ask a stupid question, but what will the answer be if the make the assumption that the backup that starts at 12.20 takes 15 minutes to run, and the read from the datafile to the backup takes e.g. 12 minutes. that means that data read is finished after the commit of transaction 4.
Will transaction 4, then be included or excluded in the full backup?????????
Søren
August 4, 2012 at 2:11 pm
nerosdk71 (7/29/2012)
Sorry to ask a stupid question, but what will the answer be if the make the assumption that the backup that starts at 12.20 takes 15 minutes to run, and the read from the datafile to the backup takes e.g. 12 minutes. that means that data read is finished after the commit of transaction 4.Will transaction 4, then be included or excluded in the full backup?????????
Not a stupid question at all. (Stupid questions don't exist, in my opinion, unless you count questions not asked out of fear for being considered stupid).
The short answer: Transaction 4 will be included, since it is committed before the full database backup finishes.
The long answer (and I'm not 100% sure here, as I am not a full-blown expert on backup internals) is that the data portion of the backup may contain a mix of pages with "pre-transaction 4" and "post-transaction 4" data, depending on whether the backup process read the pages before or after transaction 4 touches them. The log portion of the backup then contains the information to roll forward any changes from transaction 4. And if there was a transaction 5 that started before or during the backup but finished later, the log part of the backup will also contain the information to roll back changes from that transaction, so that data pages that were already changed before the backup process read them can be restored to their original (before transaction 5) state.
Some other quick comments:
sestell1 (7/20/2012)
Wow, interesting. I'd been under the false impression that the full backup used an internal snapshot to capture the state of the database at the time the backup started.
Maybe you are confusing backup with DBCC CHECKDB? Since (I believe) SQL Server 2005, DBCC CHECKDB does indeed use an internal database snapshot.
Steve Jones - SSC Editor (7/20/2012)Incorrect. On a busy system, the full backup would never end and constantly have new log entries.
For the record, that would only happen on a system that is so busy that the log grows too fast for the database process to catch up. On a normal, or even very, busy system, the database process will eventually catch up with the new log entries and finish.
(Just adding this because I think some readers might -incorrectly- interpret this remark as the backup process waiting for all transactions to commit or rollback)
August 5, 2012 at 8:49 am
Hi again,
Thanks for your reply. I agree with you that in the scenario I describe, then the transaction will be in the backup.
That also leads to that the question needs to have the assumption added that a backup takes less than 60 seconds. If it takes more than 60 seconds, then the answer is wrong.
Soren
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply