July 20, 2012 at 7:15 am
A full backup contains enough of the transaction log so the database can be made consistent on restore. The way the question is worded transaction 4 commits before the end of the backup so would be included in the backup.
---------------------------------------------------------------------
July 20, 2012 at 7:34 am
Deleted by author (I neglected to read the first post before posting).
July 20, 2012 at 7:59 am
Koen Verbeeck (7/20/2012)
I like the intent of the question, but alas poorly executed.
Agreed +1
the hard part was with the execution being done so poorly, making sure of the intent of the question became much harder.
July 20, 2012 at 8:22 am
I should apologize. I read the silly thing several times unfortunately I missed the fact that I failed to commit transaction 1 at all. To much copy and paste I guess. Obviously I need to read it a few more times next time.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
July 20, 2012 at 8:32 am
I just focussed on the last three lines and counted the 'begins' and 'commits': 4 begin, 3 commit and only the transaction 4 had been commit after the backup reading process...
12:18 AM - Full backup data reading of QotDDatabase ends
12:19 AM - Transaction 4 on QotDDatabase commits
12:20 AM - Full backup of QotDDatabase ends
Nice and simple logic...
Thanks!
July 20, 2012 at 8:35 am
george sibbald (7/20/2012)
A full backup contains enough of the transaction log so the database can be made consistent on restore. The way the question is worded transaction 4 commits before the end of the backup so would be included in the backup.
See, assuming that one of the two Transaction 2 commits is for Transaction 1, I totally agree with this. A full backup takes a snapshot of the data as it was at the beginning of the backup but then at the end appends as much of a log backup needed to make the backup current as of the end of the backup (without marking that part of the log as OK to overwrite; only a tran log backup does that). The way the question was worded, I interpreted the 12:18 event as the end of the data page backup and the beginning of the log append process. The transaction 4 completes at 12:19 and the backup finishes at 12:20. By this logic, the completion of the transaction 4 should be part of the log backup appended to the end of the backup and therefore all four transactions would be in the file. The answer as provided is wrong, or else the poster did a poor job of explaining what backup steps were happening when.
July 20, 2012 at 8:41 am
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. I'm intrigued that that is not the case, and changes to pages made after the backup has read the page are captured from the log at the end of the backup process. This seems a bit awkward to me, as it makes the point in time of the backup a bit fuzzy depending on how long it takes to process.
Nothing fuzzy about it at all -- the backup is current as of the time the backup completes. The log is appended to make this happen.
July 20, 2012 at 8:59 am
jeff.mason (7/20/2012)
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. I'm intrigued that that is not the case, and changes to pages made after the backup has read the page are captured from the log at the end of the backup process. This seems a bit awkward to me, as it makes the point in time of the backup a bit fuzzy depending on how long it takes to process.Nothing fuzzy about it at all -- the backup is current as of the time the backup completes. The log is appended to make this happen.
That's what I meant by fuzzy... it's dependent on how long the backup takes. A backup that typically runs for a few hours (or longer if there are active transactions) will not represent the database at the point that the backup is started, but some time in the future when it completes.
July 20, 2012 at 9:13 am
I've lost track of the pluses. So +57 to all of the above.
I saw that Transaction 1 had not committed, and was in a quandary. None of the answers seemed correct. So, I went with 2, as it seemed the least incorrect. At least 36% of my peers went with the same answer.
It's Friday, so I'm too tired to beg for points.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
July 20, 2012 at 9:36 am
WayneS (7/19/2012)
The full backup will also contain the uncommitted transactions from transaction 4 at the time that the backup finishes. If a restore is necessary, the UNDO portion of the restore will roll back those transactions. Therefore, the full backup will contain transactions from all four of the transactions.A small error in the question: transaction 1 never is committed, and transaction 2 is committed twice (which can't happen). Obviously, one of those transaction 2 commits is for transaction 1.
Edit: see http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx for more about the UNDO portion of recovery.
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 20, 2012 at 9:45 am
Why would 1 be included? It is never committed.
- Never mind / I should have read all the posts before posting. Kenneth committed a "cut/paste - oops!".
July 20, 2012 at 9:52 am
jeff.mason (7/20/2012)
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. I'm intrigued that that is not the case, and changes to pages made after the backup has read the page are captured from the log at the end of the backup process. This seems a bit awkward to me, as it makes the point in time of the backup a bit fuzzy depending on how long it takes to process.Nothing fuzzy about it at all -- the backup is current as of the time the backup completes. The log is appended to make this happen.
I agree with you both here Jeff & George. Cheers
July 20, 2012 at 10:23 am
sestell1 (7/20/2012)
jeff.mason (7/20/2012)
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. I'm intrigued that that is not the case, and changes to pages made after the backup has read the page are captured from the log at the end of the backup process. This seems a bit awkward to me, as it makes the point in time of the backup a bit fuzzy depending on how long it takes to process.Nothing fuzzy about it at all -- the backup is current as of the time the backup completes. The log is appended to make this happen.
That's what I meant by fuzzy... it's dependent on how long the backup takes. A backup that typically runs for a few hours (or longer if there are active transactions) will not represent the database at the point that the backup is started, but some time in the future when it completes.
It may be fuzzy to predict in advance -- you won't know the point in time for tomorrow's backup, let's say. But once the backup is complete, you know exactly what the backup is current as of. And as long as you have the prior day's backup and the full log chain, you can still do point in time recovery for a time during the full backup. If the backup is still in progress as of 3 AM but you need to recover to 3 AM, get the prior backup and restore logs to 3 AM. A pain, to be sure, but still an option.
July 20, 2012 at 10:27 am
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,
Not all gray hairs are Dinosaurs!
July 20, 2012 at 10:35 am
+ yet another one to what has gone before.
I reckon the only right answer is 1,2,3 and 4, unless the question meant "all the updates of which transactions" in which case the answer was "2,3 and 4" omitting 1 because it didn't commit before the backup ended so the backup couldn't know whether it had all the dates or not careless - I didn't notice the duiplicate commit of 2). Anyway the given "correct" answer (1,2,3) is certainly wrong under any interpretation of the question, even allowing one of the commit transaction 2s to be read as commit transaction 1.
But I wouldn't have got a point even if the question had been write, because I had finger trouble and checked the wrong option!
Tom
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply