InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 22:09:56 GMT20http://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspxGood question! Tests ones' understanding in a better ways.Kudos to the Question submitter!Thu, 10 Jun 2010 03:46:06 GMTTeenuhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Paul White NZ (4/1/2010)[/b][hr]Thanks, everyone. Glad I am not the only one to find this whole area fascinating.[/quote]Yes, I do too [for reasons unfathomable]And I'm glad I kinda kicked off the discussion about when the backup knew when to stop. I'm slogging my way through all of Paul's postings, and drawing diagrams of my own.Again, thanks to all.Thu, 01 Apr 2010 09:47:07 GMTFesteronhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspxThanks, everyone. Glad I am not the only one to find this whole area fascinating.Thu, 01 Apr 2010 03:33:33 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Tom.Thomson (3/31/2010)[/b][hr]And I see the MS design does include flushing already dirty pages to disc despite Hugo's suggestion that this is not a useful thing to do.[/quote]Yes, I noticed that as well. And was a bit surprised by it.I'm glad Paul was able to find and post such a detailed description of the process. Thanks, Paul!Wed, 31 Mar 2010 16:08:37 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Paul White NZ (3/31/2010)[/b][hr]The whole backup operation is conceptually quite simple:[ul]Force a database checkpoint (flush all updated-in-memory pages to disk before anything is read by the backup)[/ul][ul]Record the minimum recovery LSN (LSN1)[/ul][ul]Database data read begins[/ul][ul]Database data read ends[/ul][ul]Record the current LSN (LSN2)[/ul][ul]Read the transaction log from LSN1 to LSN2[/ul][/quote]Thanks Paul, that's a very clear description. It answers all the questions I had about whether dirty data is flushed first, when the start LSN is recorded, and whether the final LSN is allowed to be later than the current LSN at the end of the DB read.And I see the MS design does include flushing already dirty pages to disc despite Hugo's suggestion that this is not a useful thing to do.Wed, 31 Mar 2010 14:58:03 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Paul White NZ (3/31/2010)[/b][hr]Thank you, Peter. Of course, there are some edge cases and subtleties, but unless Mr Randal appears and tells me I know nothing about nothing...I'm happy with it.[/quote]I don't sweat the exceptions. Concepts first, exceptions later. Can't understand the exceptions until the normal process makes sense. I get grief for using broken analogies to explain concepts like joins but the students like simple explanations, even if they are flawed by real world applications.Wed, 31 Mar 2010 08:38:06 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspxThank you, Peter. Of course, there are some edge cases and subtleties, but unless Mr Randal appears and tells me I know nothing about nothing...I'm happy with it.Wed, 31 Mar 2010 08:18:15 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Paul White NZ (3/31/2010)[/b][hr][quote][b]Tom.Thomson (3/29/2010)[/b][hr]There are a couple of additional things backup could do to improve performance: at some point before starting to write pages to backup media, it could write all dirty pages from cache to disc - this could be done either before or after (preferably before) working out the log start position; and it could write extra pages of the log to disc after the log stop position - either to the end of the log, or up to the last log entry that has commited a transaction. But there are a couple of issues with these "performance enhancements": (a) if pages are being dirtied fast enough the first trick will slow backup by more than in speeds recovery, so not much point to it; (b) extra log data after the stop point is all very well but theoretically with the end point defined either of the ways suggested it's possible that the log is growing fast enough that the backup will never terminate (and there's certainly some practical risk that it will take an excessive time to terminate). So MS may have kept it simple or may have done calculations which tell them which way will generally be best to go; only someone who knows their implementation of backup inside out will know what they have actually chosen to do (I certainly don't).[/quote]The whole backup operation is conceptually quite simple:[ul]Force a database checkpoint (flush all updated-in-memory pages to disk before anything is read by the backup)[/ul][ul]Record the minimum recovery LSN (LSN1)[/ul][ul]Database data read begins[/ul][ul]Database data read ends[/ul][ul]Record the current LSN (LSN2)[/ul][ul]Read the transaction log from LSN1 to LSN2[/ul][/quote]Wow, simple explanation. Thanks!Wed, 31 Mar 2010 08:06:19 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Tom.Thomson (3/29/2010)[/b][hr]There are a couple of additional things backup could do to improve performance: at some point before starting to write pages to backup media, it could write all dirty pages from cache to disc - this could be done either before or after (preferably before) working out the log start position; and it could write extra pages of the log to disc after the log stop position - either to the end of the log, or up to the last log entry that has commited a transaction. But there are a couple of issues with these "performance enhancements": (a) if pages are being dirtied fast enough the first trick will slow backup by more than in speeds recovery, so not much point to it; (b) extra log data after the stop point is all very well but theoretically with the end point defined either of the ways suggested it's possible that the log is growing fast enough that the backup will never terminate (and there's certainly some practical risk that it will take an excessive time to terminate). So MS may have kept it simple or may have done calculations which tell them which way will generally be best to go; only someone who knows their implementation of backup inside out will know what they have actually chosen to do (I certainly don't).[/quote]The whole backup operation is conceptually quite simple:[ul]Force a database checkpoint (flush all updated-in-memory pages to disk before anything is read by the backup)[/ul][ul]Record the minimum recovery LSN (LSN1)[/ul][ul]Database data read begins[/ul][ul]Database data read ends[/ul][ul]Record the current LSN (LSN2)[/ul][ul]Read the transaction log from LSN1 to LSN2[/ul]Wed, 31 Mar 2010 01:42:46 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Tom.Thomson (3/19/2010)[/b][hr]If I undertand it rightly, the correct wording is more like "from the start of the oldest transaction that was uncommitted at the start of the backup, or from the first log entry after the start of the backup if there is no such transaction".[/quote]The start LSN of the backup is the [i]minimum[/i] of:[li]The LSN of the last checkpoint (note the backup operation starts by issuing a checkpoint)[/li][li]The LSN of the start of the [i]oldest active[/i] transaction[/li][li]The LSN of the last replicated transaction[/li]See [url]http://sqlkpi.com/BLOGS/PAUL/post/Debunking-a-couple-of-myths-around-full-database-backups.aspx[/url]Wed, 31 Mar 2010 00:42:22 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Hugo Kornelis (3/29/2010)[/b][hr]As far as I know, it stops as soon as it reaches the end of the log file. That means that, in theory, you could envisage a scenario where the log file grows faster than the backup can read it, and the backup won't terminate. Though I am pretty sure that in practice, you'll never be able to make that happen.[/quote]That is not possible, no. The backup operation only requires log records up to the point that the [i]backup data read[/i] completes.See [url=http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx]Understanding SQL Server Backups[/url] by Paul Randal for a neat diagram to explain that statement visually.Wed, 31 Mar 2010 00:27:42 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Hugo Kornelis (3/30/2010)[/b][hr][quote][b]Tom.Thomson (3/29/2010)[/b][hr](...)There are a couple of additional things backup could do to improvve performance: at some point before starting to write pages to backup media, it could write all dirty pages from cache to disc[/quote]And how would that enhance performance? Either the backup copies pages from cache (in which case flushing dirty pages first doesn't change anything, except for the extra time taken to flush them), or it copies pages from disk (in which case flushing the dirty pages first influences which version of the data is in the backup(*), but not the backup time -again, except for the time taken to flush the dirty pages).[/quote]Obviously it depends on what exactly the backup mechanism chosen by the designers of SQL Server is. On platforms which provide media to media transfer without passing through the computer's RAM copying from disc to cache and then from cache to backup media is inefficient - - but I have no idea whether any version of windows can use that feature even when hardware provides it so it may be that pushing stuff out to disc so that the copy process doesn't have to alternate between disc and RAM as source won't help by maximising the benefit of autonomous copy, and even if Windows can support this there's no guarantee that SQLS would support it. It is however true that even without autonomous copy it is faster, on some platforms, to arrange things so that very large chunks are written rather than a larger number of small ones, and in that case it's still possible that making it possible to transfer everything from disc and not refer to cache may give a performance gain; but again SQLS may have chosen not to do this as it may not be worth the effort (it may even be counterproductive on some platforms).Tue, 30 Mar 2010 16:46:14 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspxCan't believe I got this one wrong :blush: :blush: :blush:Tue, 30 Mar 2010 08:07:46 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Tom.Thomson (3/29/2010)[/b][hr](...)then it copies all the database pages from disc to the backup media[/quote]As far as I know, the backup process copies pages from cache to the backup file, not from disk. Pages not in cache will be read in first, just as for any other database operation.[quote]There are a couple of additional things backup could do to improvve performance: at some point before starting to write pages to backup media, it could write all dirty pages from cache to disc[/quote]And how would that enhance performance? Either the backup copies pages from cache (in which case flushing dirty pages first doesn't change anything, except for the extra time taken to flush them), or it copies pages from disk (in which case flushing the dirty pages first influences which version of the data is in the backup(*), but not the backup time -again, except for the time taken to flush the dirty pages).(*) ... and since the restore process will use the log portion of the backup to roll forward or roll back all transactions that were in process during the backup, it does not really matter which version of a dirty page is backed up.Tue, 30 Mar 2010 00:54:38 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Festeron (3/29/2010)[/b][hr]Can I ask you about the other end of the backup?At what point does the backup decide it's time to stop backing up, even though there may be transactions still in flight? Does there come a time when backup decides to stop chasing its tail?Or does it not back up anything in the log past the point at which the backup began?[/quote]Logically teh minimum that has to happen is that at the beginning of the backup it notes the point in the log where the oldest currently active transaction began, or the current position if there is no currently active transaction - call that the start position; then it copies all the database pages from disc to the backup media, and notes the current position in the log as the end position; then it writed all the log entries from the start position to the end position to the backup. There are a couple of additional things backup could do to improvve performance: at some point before starting to write pages to backup media, it could write all dirty pages from cache to disc - this could be done either before or after (preferably before) working out the log start position; and it could write extra pages of the log to disc after the log stop position - either to the end of the log, or up to the last log entry that has commited a transaction. But there are a couple of issues with these "performance enhancements": (a) if pages are being dirtied fast enough the first trick will slow backup by more than in speeds recovery, so not much point to it; (b) extra log data after the stop point is all very well but theoretically with the end point defined either of the ways suggested it's possible that the log is growing fast enough that the backup will never terminate (and there's certainly some practical risk that it will take an excessive time to terminate). So MS may have kept it simple or may have done calculations which tell them which way will generally be best to go; only someone who knows their implementation of backup inside out will know what they have actually chosen to do (I certainly don't).Mon, 29 Mar 2010 19:03:05 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Festeron (3/29/2010)[/b][hr][quote][b]Tom.Thomson (3/20/2010)[/b][hr][quote][b]vk-kirov (3/20/2010)[/b][hr][quote][b]Tom.Thomson (3/19/2010)[/b][hr]But a single select statement may write more pages than can be held in RAM and this means that dirty pages get to disc[/quote]"Insert statement", I believe?[/quote]Yes, of course. Silly me! :blush:[/quote]Can I ask you about the other end of the backup?At what point does the backup decide it's time to stop backing up, even though there may be transactions still in flight? Does there come a time when backup decides to stop chasing its tail?Or does it not back up anything in the log past the point at which the backup began?[/quote]As far as I know, it stops as soon as it reaches the end of the log file. That means that, in theory, you could envisage a scenario where the log file grows faster than the backup can read it, and the backup won't terminate. Though I am pretty sure that in practice, you'll never be able to make that happen.Mon, 29 Mar 2010 11:40:09 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Tom.Thomson (3/20/2010)[/b][hr][quote][b]vk-kirov (3/20/2010)[/b][hr][quote][b]Tom.Thomson (3/19/2010)[/b][hr]But a single select statement may write more pages than can be held in RAM and this means that dirty pages get to disc[/quote]"Insert statement", I believe?[/quote]Yes, of course. Silly me! :blush:[/quote]Can I ask you about the other end of the backup?At what point does the backup decide it's time to stop backing up, even though there may be transactions still in flight? Does there come a time when backup decides to stop chasing its tail?Or does it not back up anything in the log past the point at which the backup began?Mon, 29 Mar 2010 11:29:12 GMTFesteronhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspxI got this wrong, i went for A. Excellent question and one that I should have answered correctly since I have been reading up on this topic recently. The important thing is that I learnt something. My thanks to the author.Mon, 29 Mar 2010 01:20:17 GMTChris Houghtonhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]vk-kirov (3/20/2010)[/b][hr][quote][b]Tom.Thomson (3/19/2010)[/b][hr]But a single select statement may write more pages than can be held in RAM and this means that dirty pages get to disc[/quote]"Insert statement", I believe?[/quote]Yes, of course. Silly me! :blush:Sat, 20 Mar 2010 09:33:40 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Tom.Thomson (3/19/2010)[/b][hr][quote][b]vk-kirov (3/16/2010)[/b][hr][quote][b]Hugo Kornelis (3/16/2010)[/b][hr]the backup first copies all the data pages, then adds all the log pages starting from the start of the oldest uncommitted transaction right up until the time the backup ends.[/quote]I would like to add: "from the start of the oldest uncommitted transaction [b](which was in an uncommitted state when the backup started)[/b]", because without this remark one may think "the oldest uncommitted transaction which was in an uncommitted state when the backup finished to copy data pages" :-)[/quote]If I undertand it rightly, the correct wording is more like "from the start of the oldest transaction that was uncommitted at the start of the backup, or from the first log entry after the start of the backup if there is no such transaction".[/quote]Yes, this is much better. I forgot about the case "there's no uncommitted transaction at all". Thank you for pointing it out :-)I just wanted to say that the phrase "the oldest uncommitted transaction" might be ambiguous.[quote][b]Tom.Thomson (3/19/2010)[/b][hr]But a single select statement may write more pages than can be held in RAM and this means that dirty pages get to disc[/quote]"Insert statement", I believe?There can be other reasons for writing dirty pages to disk ([url]http://msdn.microsoft.com/en-us/library/aa337560.aspx[/url]):[quote][b]Writing Pages[/b][hr]A dirty page is written to disk in one of three ways. •Lazy writingThe lazy writer is a system process that keeps free buffers available by removing infrequently used pages from the buffer cache. Dirty pages are first written to disk. •Eager writingThe eager write process writes dirty data pages associated with nonlogged operations such as bulk insert and select into. [i](OK, there's a logged operation in the question)[/i]•Checkpoint [/quote]Sat, 20 Mar 2010 04:09:59 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspxHello everyone,I am delighted to see a good response to the question I posted long back.;-)Yeah, first of all let me admit that the explanation for the answer was not at all upto the technical standards. Because, I am new to SQL Server and this was a question which I faced in one of my interviews. From the posts in response to the question, I could make out that there was an ambiguity whether a t-log backup was restored, after restoring the full backup with no recovery. Actually what I meant was, whether the full backup after restoring with recovery (which is by default) would insert all the 10000 rows or just a few or not at all. I just wanted to know the consequence after restoring a full backup. T-log backup was out of question.Thanks once again5uj@ J@me5:-)Fri, 19 Mar 2010 15:39:09 GMTsuja.jamesgeorgehttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]john.arnott (3/17/2010)[/b][hr]Sorry for the rant, but those who live in glass houses.....[/quote]Don't be sorry, I imagine most who see it will be gald to see that someone provided the deserved response quickly.Fri, 19 Mar 2010 13:57:27 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]gamcall (3/17/2010)[/b][hr]"So if the insert transaction completes after the full backup is complete, how many rows where inserted till the backup gets completed will be included in the backup."Shocking use of english. It almost occludes the meaning of this part of the explanation. I *think* what is meant here is:Therefore, if the insert transaction completes after the full backup, howEVER many rows WERE inserted before the backup IS completed will be included in the backup. Editors would do well to check the grammar of submissions. I've always found that improper use of language in a technical scenario always translates to a technical liability.[/quote]:angry:I think I have to comment that gamcall's insistance of using "is" instead of "gets" is silly because "is completed" is ambiguous (can describe either a state or an event) whereas "gets" resolves the ambiguity (it can only describe an event). Ambiguity in a technical scenario is a worse offence than using "how many" instead of "however many" (many versions of English allow "how many" in that context) or a trivial spelling error ("h" in were). So apparently he wants to place a duty on editors to fix trivia and at the same time change correct unambiguous English into ambiguous (and therefor, in a technical context requiring precision, incorrect:exclamationmark:) English.Fri, 19 Mar 2010 13:54:52 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]vk-kirov (3/16/2010)[/b][hr][quote][b]Hugo Kornelis (3/16/2010)[/b][hr]the backup first copies all the data pages, then adds all the log pages starting from the start of the oldest uncommitted transaction right up until the time the backup ends.[/quote]I would like to add: "from the start of the oldest uncommitted transaction [b](which was in an uncommitted state when the backup started)[/b]", because without this remark one may think "the oldest uncommitted transaction which was in an uncommitted state when the backup finished to copy data pages" :-)[/quote]If I undertand it rightly, the correct wording is more like "from the start of the oldest transaction that was uncommitted at the start of the backup, or from the first log entry after the start of the backup if there is no such transaction". The point is that a section of the log will be included in the backup even if all transactions are committed by the point in time at which the backup of everything other than the log is complete (unless there has been no update or rollback or commitment since the start of the backup and no transaction was uncommitted at the start of the backup). Maybe you intended your addition to be an explanation of Hugo's words, but without some sign like "that is" or "i.e." after the opening parenthesis it can be read as an additional condition on the time indicated in which case it seems to suggest that if there is not a transaction which is uncommitted at the time the backup (of pages, not including the log) is complete there is no defined starting time for the section of log to be backed up so it makes the description less clear than Hugo's explanation. My addition of an alternative start time is needed to cope with the case of a transaction that began after the backup started and committed before the end of the backup of the pages.I actually believe that the answer is somehat misleading (although it is correct) because most people will (despite the inclusion of the word "all") assume that if the select statement does not complete then none of the rows will be in the backup. But a single select statement may write more pages than can be held in RAM and this means that dirty pages get to disc and to the transaction log before the select statement completes, and since they are in the trasaction log they are in the backup, and if page copying is sufficiently low they will also be in backed up (dirty) pages (although they will not of course be included in any restore WITH RECOVERY from the backup). Even a low number like 10000 can be enough rows to make this happen (especially if there's not much RAM and each row contains a nice big varchar(MAX) value).Fri, 19 Mar 2010 13:37:06 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Lynn Pettis (3/17/2010)[/b][hr][quote][b]john.arnott (3/17/2010)[/b][hr][quote][b]gamcall (3/17/2010)[/b][hr]"So if the insert transaction completes after the full backup is complete, how many rows where inserted till the backup gets completed will be included in the backup."Shocking use of english. It almost occludes the meaning of this part of the explanation. I *think* what is meant here is:Therefore, if the insert transaction completes after the full backup, howEVER many rows WERE inserted before the backup IS completed will be included in the backup. Editors would do well to check the grammar of submissions. I've always found that improper use of language in a technical scenario always translates to a technical liability.[/quote]1. Unless he/she is criticizing an unwise spin on the cue ball in a billiards game, I believe the author of this post may have meant to refer to a "shocking use of English." The name of our language is always capitalized. 2. Tarting up a sentence by using "Therefore" as a direct substitution for "So" IMHO does not contribute to more effective communication. 3. The egregious use of CAPital letters for part of a word is distracting and unnecessary. A point may be better emphasized by the use of a persuasive argument.4. As I recall, "occlude" means to block, as in "an occluded artery". Perhaps the author meant to say that poor grammar can [b]obscure [/b]meaning.5. As has been repeated in many QOD discussions, there is little editing applied to the questions, answers or explanations. 6. The use of the word "translates" in the final sentence seems ironically apropos. Many, many of the users of SSC do not use English as a first language. Those of us who do may consider that when reading or responding to posts here. I'm guessing from the admittedly nonstandard syntax of the explanation and from the author's name that he or she would belong to that category. Our comments on the QOD, or any other post on SSC for that matter, would best be limited to the subject matter at hand and not the writing skills of our worldwide community of professionals.Sorry for the rant, but those who live in glass houses.....[/quote][ join rant ]I have to agree with John on this; English (or American English) is not the first, second, or even third language for many of the people that visit SSC on a regular basis. To critize ones use of the English language on an international site is simply inappropriate. We all try our best to understand what others say, and sometimes we misinterpret what is said, but that is the nature of communication.[/ end rant][/quote]Lynne, as always well said.Wed, 17 Mar 2010 21:22:16 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Lynn Pettis (3/17/2010)[/b][hr][quote][b]john.arnott (3/17/2010)[/b][hr][quote][b]gamcall (3/17/2010)[/b][hr]"So if the insert transaction completes after the full backup is complete, how many rows where inserted till the backup gets completed will be included in the backup."Shocking use of english. It almost occludes the meaning of this part of the explanation. I *think* what is meant here is:Therefore, if the insert transaction completes after the full backup, howEVER many rows WERE inserted before the backup IS completed will be included in the backup. Editors would do well to check the grammar of submissions. I've always found that improper use of language in a technical scenario always translates to a technical liability.[/quote]1. Unless he/she is criticizing an unwise spin on the cue ball in a billiards game, I believe the author of this post may have meant to refer to a "shocking use of English." The name of our language is always capitalized. 2. Tarting up a sentence by using "Therefore" as a direct substitution for "So" IMHO does not contribute to more effective communication. 3. The egregious use of CAPital letters for part of a word is distracting and unnecessary. A point may be better emphasized by the use of a persuasive argument.4. As I recall, "occlude" means to block, as in "an occluded artery". Perhaps the author meant to say that poor grammar can [b]obscure [/b]meaning.5. As has been repeated in many QOD discussions, there is little editing applied to the questions, answers or explanations. 6. The use of the word "translates" in the final sentence seems ironically apropos. Many, many of the users of SSC do not use English as a first language. Those of us who do may consider that when reading or responding to posts here. I'm guessing from the admittedly nonstandard syntax of the explanation and from the author's name that he or she would belong to that category. Our comments on the QOD, or any other post on SSC for that matter, would best be limited to the subject matter at hand and not the writing skills of our worldwide community of professionals.Sorry for the rant, but those who live in glass houses.....[/quote][ join rant ]I have to agree with John on this; English (or American English) is not the first, second, or even third language for many of the people that visit SSC on a regular basis. To critize ones use of the English language on an international site is simply inappropriate. We all try our best to understand what others say, and sometimes we misinterpret what is said, but that is the nature of communication.[/ end rant][/quote]Beating the same rant drum. We are not here to analyze or criticize the linguistic ability, neither in written nor verbal form, of the members of this community.If it is really a problem of interpretation of what has been said or meant, then ask directly rather than nitpicking.Wed, 17 Mar 2010 20:06:46 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]john.arnott (3/17/2010)[/b][hr][quote][b]gamcall (3/17/2010)[/b][hr]"So if the insert transaction completes after the full backup is complete, how many rows where inserted till the backup gets completed will be included in the backup."Shocking use of english. It almost occludes the meaning of this part of the explanation. I *think* what is meant here is:Therefore, if the insert transaction completes after the full backup, howEVER many rows WERE inserted before the backup IS completed will be included in the backup. Editors would do well to check the grammar of submissions. I've always found that improper use of language in a technical scenario always translates to a technical liability.[/quote]1. Unless he/she is criticizing an unwise spin on the cue ball in a billiards game, I believe the author of this post may have meant to refer to a "shocking use of English." The name of our language is always capitalized. 2. Tarting up a sentence by using "Therefore" as a direct substitution for "So" IMHO does not contribute to more effective communication. 3. The egregious use of CAPital letters for part of a word is distracting and unnecessary. A point may be better emphasized by the use of a persuasive argument.4. As I recall, "occlude" means to block, as in "an occluded artery". Perhaps the author meant to say that poor grammar can [b]obscure [/b]meaning.5. As has been repeated in many QOD discussions, there is little editing applied to the questions, answers or explanations. 6. The use of the word "translates" in the final sentence seems ironically apropos. Many, many of the users of SSC do not use English as a first language. Those of us who do may consider that when reading or responding to posts here. I'm guessing from the admittedly nonstandard syntax of the explanation and from the author's name that he or she would belong to that category. Our comments on the QOD, or any other post on SSC for that matter, would best be limited to the subject matter at hand and not the writing skills of our worldwide community of professionals.Sorry for the rant, but those who live in glass houses.....[/quote][ join rant ]I have to agree with John on this; English (or American English) is not the first, second, or even third language for many of the people that visit SSC on a regular basis. To critize ones use of the English language on an international site is simply inappropriate. We all try our best to understand what others say, and sometimes we misinterpret what is said, but that is the nature of communication.[/ end rant]Wed, 17 Mar 2010 18:57:31 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]gamcall (3/17/2010)[/b][hr]"So if the insert transaction completes after the full backup is complete, how many rows where inserted till the backup gets completed will be included in the backup."Shocking use of english. It almost occludes the meaning of this part of the explanation. I *think* what is meant here is:Therefore, if the insert transaction completes after the full backup, howEVER many rows WERE inserted before the backup IS completed will be included in the backup. Editors would do well to check the grammar of submissions. I've always found that improper use of language in a technical scenario always translates to a technical liability.[/quote]1. Unless he/she is criticizing an unwise spin on the cue ball in a billiards game, I believe the author of this post may have meant to refer to a "shocking use of English." The name of our language is always capitalized. 2. Tarting up a sentence by using "Therefore" as a direct substitution for "So" IMHO does not contribute to more effective communication. 3. The egregious use of CAPital letters for part of a word is distracting and unnecessary. A point may be better emphasized by the use of a persuasive argument.4. As I recall, "occlude" means to block, as in "an occluded artery". Perhaps the author meant to say that poor grammar can [b]obscure [/b]meaning.5. As has been repeated in many QOD discussions, there is little editing applied to the questions, answers or explanations. 6. The use of the word "translates" in the final sentence seems ironically apropos. Many, many of the users of SSC do not use English as a first language. Those of us who do may consider that when reading or responding to posts here. I'm guessing from the admittedly nonstandard syntax of the explanation and from the author's name that he or she would belong to that category. Our comments on the QOD, or any other post on SSC for that matter, would best be limited to the subject matter at hand and not the writing skills of our worldwide community of professionals.Sorry for the rant, but those who live in glass houses.....Wed, 17 Mar 2010 18:23:48 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx"So if the insert transaction completes after the full backup is complete, how many rows where inserted till the backup gets completed will be included in the backup."Shocking use of english. It almost occludes the meaning of this part of the explanation. I *think* what is meant here is:Therefore, if the insert transaction completes after the full backup, howEVER many rows WERE inserted before the backup IS completed will be included in the backup. Editors would do well to check the grammar of submissions. I've always found that improper use of language in a technical scenario always translates to a technical liability.Wed, 17 Mar 2010 15:33:35 GMTgamcallhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote]On a restore, first all data pages are restored; then the log entries included in the backup are used toa) roll forward any transactions that were committed while the backup was running, andb) roll back any changes from transactions that were not yet commited when the backup finished.The latter is required because the backed up data pages may already have been modified by those transactions.[/quote]This is completely correct, with the understanding that the restore is being done WITH RECOVERY [which is the default].If WITH NORECOVERY is specified, then a) is done but b) is not. The uncommitted transactions might complete in a future restore operation, so they must not be rolled back.What I'm not sure about is how the backup avoids chasing its tail. If it's backing up the log after the pointer, can't a long-running transaction be filling the log while the backup is trying to complete? Wed, 17 Mar 2010 09:09:43 GMTFesteronhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]vk-kirov (3/16/2010)[/b][hr][quote][b]ChrisMoix-87856 (3/16/2010)[/b][hr]IIRC, SQL 7.0 the backup contained what was "in" the database at the start of the backup.[/quote]You are mistaken. Here is a quote from BOL 7.0:[quote][b]Creating and Restoring a Database Backup[/b][hr]Restoring a Database BackupThis process ensures that the restored database is a copy of the database as it existed [b]when the backup operation completed[/b], except that all incomplete transactions have been rolled back. This is required to restore the integrity of the database.[/quote]I found BOL 7.0 here: http://www.microsoft.com/technet/prodtechnol/sql/70/downloads/books.mspx (careful, there's 12MB .exe file).[/quote]Maybe I am remembering back to my 6.5 days (or before)... Good thing I hedged myself with the IIRC, etc. :-)Wed, 17 Mar 2010 06:18:40 GMTChrisMoix-87856http://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspxI go with Hugo's explanation, except that the backup of the log does take a finite time. Particularly if you have a large active log then it may take a while, so the transaction inclusion cutoff is some (indeterminate) time before the backup completes. The time that the data page backup completes can probably be approximated by the time the message gets generatedFor pre-yearend backups I have used the concept in practice; timing my multi-hour backup to terminate maybe 15 minutes past the cutoff time for user processing, and taking a log backup after cutoff for good measureWed, 17 Mar 2010 03:40:06 GMTtony.turnerhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Oleg Netchaev (3/16/2010)[/b][hr][quote][b]sjimmo (3/16/2010)[/b][hr]Hugo, after getting the questin wrong;-) and re-reading a few times in disbelief I found on <http://msdn.microsoft.com/en-us/library/ms175477.aspx> - Each data backup includes part of the transaction log so that the backup can be recovered to the end of that backup.So, in a full backup, all [b]completed[/b] transactions will be added to the full dump. everything else will be added to the transaction log dumps if being performed.[/quote]All completed transactions are added to the dump regardless whether they were initiated before or after the dump started, so long as they were committed before the dump is completed. Looks like the note on the BOL page you mention is the key. it states that full database backups "represent the whole database at the time the backup finished". Since I like to make the short story long, here is the small test to confirm, with the only exception it does not have the while loop for the while loop is evil as the select is already a loop: I used 2 instances of the SSMS and 3 query windows. In the first window of instance 1 I started a full backup of the AdventureWorks:[/quote]thanks for ur example, understood it completely...:-)Wed, 17 Mar 2010 02:50:41 GMTziangijhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspxprobably some kind of rollback should have been in place.. :-)[quote][b]CirquedeSQLeil (3/16/2010)[/b][hr]Great question. Would have been better if I had not missed my click.:-D[/quote]Wed, 17 Mar 2010 02:42:04 GMTziangijhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]ChrisMoix-87856 (3/16/2010)[/b][hr]IIRC, SQL 7.0 the backup contained what was "in" the database at the start of the backup.[/quote]You are mistaken. Here is a quote from BOL 7.0:[quote][b]Creating and Restoring a Database Backup[/b][hr]Restoring a Database BackupThis process ensures that the restored database is a copy of the database as it existed [b]when the backup operation completed[/b], except that all incomplete transactions have been rolled back. This is required to restore the integrity of the database.[/quote]I found BOL 7.0 here: http://www.microsoft.com/technet/prodtechnol/sql/70/downloads/books.mspx (careful, there's 12MB .exe file).Tue, 16 Mar 2010 15:49:27 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspxIIRC, SQL 7.0 the backup contained what was "in" the database at the start of the backup. SQL 2000 containes what was "in" the database at the end of the backup.Tue, 16 Mar 2010 14:24:20 GMTChrisMoix-87856http://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Hugo Kornelis (3/16/2010)[/b][hr]the backup first copies all the data pages, then adds all the log pages starting from the start of the oldest uncommitted transaction right up until the time the backup ends.[/quote]I would like to add: "from the start of the oldest uncommitted transaction [b](which was in an uncommitted state when the backup started)[/b]", because without this remark one may think "the oldest uncommitted transaction which was in an uncommitted state when the backup finished to copy data pages" :-)[quote][b]ChrisMoix-87856 (3/16/2010)[/b][hr]This behavior changed (I think it was between 7.0 and 2000).[/quote]It's very interesting. Could you please describe the previous behavior?[quote][b]ChrisMoix-87856 (3/16/2010)[/b][hr]Do all versions since the change (SQL 2000, 2005,and 2008) all behave the same as well? [/quote]Yes, they do.Tue, 16 Mar 2010 14:22:08 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspxWell, I am admitedly being a bit picky with this, but the question never states which version of SQL Server it applies to. This behavior changed (I [i]think[/i] it was between 7.0 and 2000). Do all versions since the change (SQL 2000, 2005,and 2008) all behave the same as well?Tue, 16 Mar 2010 13:40:04 GMTChrisMoix-87856http://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspx[quote][b]Mark D Powell (3/16/2010)[/b][hr]Because this is a full backup ...From the manual:The full recovery model uses log backups to prevent data loss in the broadest range of failure scenarios, and backing and restoring the transaction log (log backups) is required.So if the log was restored then all the data would be present.[/quote]"Full backup" and "full recovery model" are two different things. You may do a full backup of a database that uses any of the three recovery models. A full backup always includes a part of the transaction log, no matter which recovery model is used. And that part of the transaction log will be processed during the restore process.Tue, 16 Mar 2010 13:38:56 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic883499-2654-1.aspxAh. I think I see what you're getting at, Mark. The original explanation implies that as many of the 10000 rows as have been inserted by the end of the backup would then be included in a restored version of that database. That, of course, would not be true if they were part of a transaction that was committed after the backup finished, But wouldn't it still be true if the inserts were not designated within an explicit transaction? That is, if each insert were implicitly committed, then you could get some but not all of the 10,000 rows in your restored db. Or am I still missing something here?Tue, 16 Mar 2010 12:52:46 GMTjohn.arnott