InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 14:48:01 GMT20http://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxNice trick question!Mon, 06 Jun 2011 11:54:10 GMTRafael Krisllerhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]Duncan Pryde (3/9/2011)[/b]Hi - I think you may have overthought things a bit - it wasn't meant to be a complicated or trick question, but just to find out if people thought that TRUNCATE TABLE somehow affected the validity of the transaction log. It doesn't.Thanks for the feedback though, maybe I should have made the question clearer somehow.Duncan[/quote]Acchkkk!!! I over thought it too! :w00t: Just as I hit the answer I thought, " I'm over thinking this, aren't I?" Next time. :cool:Fri, 18 Mar 2011 09:51:51 GMTAlan Voganhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]mohammed moinudheen (3/9/2011)[/b][hr]Thanks for the question Duncan. I had a feeling that this question was more at testing our understanding of the truncate statement than the recovery models.[/quote]Wed, 16 Mar 2011 05:20:27 GMTanu1krishnahttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxnice question.Mon, 14 Mar 2011 09:05:56 GMTmalleswarareddy_mhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxI answered wrong but it was a good question. I brainfarted like a few others and went after the 'data', not the log... so you could restore anytime up till 9:20 in my mind. Would have helped if I had simply read the question twice, 'neh?Ah well, thanks. :-)Thu, 10 Mar 2011 15:55:31 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]wware (3/9/2011)[/b][hr]Good question. Please don't hesitate to submit more. I speak for the 62% who read the question as you intended and answered correctly. Our knowledge was reinforced. The folks who answered incorrectly but understood the question as worded really learned something today![/quote]I couldn't ask for a nicer comment than that. Thanks!Thu, 10 Mar 2011 01:00:32 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxGood question. Please don't hesitate to submit more. I speak for the 62% who read the question as you intended and answered correctly. Our knowledge was reinforced. The folks who answered incorrectly but understood the question as worded really learned something today!Wed, 09 Mar 2011 14:58:31 GMTRose Budhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxThis should be an easy two points, but I have encountered far too many people that think there is no recovery possible upon truncating a table. It is very hard to reverse that thinking - even after showing them examples.Thanks for the question.Wed, 09 Mar 2011 11:26:57 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxNice question, thanks!Though I probably would have asked about it in the BULK LOGGED recovery mode, as that would have made it more likely that a TRUNCATE TABLE might be a bulk operation that would prevent a point-in-time restore. (Within that one log backup.)Wed, 09 Mar 2011 10:34:12 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]alen teplitsky (3/9/2011)[/b][hr][quote][b]sknox (3/9/2011)[/b][hr][quote][b]alen teplitsky (3/9/2011)[/b][hr]if the TRUN CATE TABLE command is logged, why can't you run it on a table that is published for replication?[/quote]Essentially, TRUN CATE TABLE simply invalidates all of the pages related to the table in one operation with no row-level checking. Since replication acts on row-level processing, a table-level command like this is not allowed. Same reason you can't do it on tables with foreign keys and it won't fire any triggers. Also the same reason it's generally so much faster than DEL ETE[Sorry for the extraneous spaces -- stupid IPS can't tell the difference between a comment and an attack][/quote]that's why i think this is a bogus question. technically everything in SQL is logged or whatever because the system has to execute it and it has to know what to execute against. but practically this is not a logged operation.i clicked the SQL Skills link expecting some cool info on how to recover the lost data, but no such luck[/quote]It is a logged operation, just not row-level-logged. All of the information needed to restore is there (i.e, to restore before the truncate, you roll forward the transaction log to the point before the truncate happened.) The question was concerning restoring to any point in time, which is still possible. If it were NOT a logged operation, it would not be possible, because restoring to after the truncate would not include the truncate.For more information about point-in-time restore, a good starting point is here: [url]http://msdn.microsoft.com/en-us/library/ms190982.aspx[/url]Wed, 09 Mar 2011 10:00:55 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]sknox (3/9/2011)[/b][hr][quote][b]alen teplitsky (3/9/2011)[/b][hr]if the TRUN CATE TABLE command is logged, why can't you run it on a table that is published for replication?[/quote]Essentially, TRUN CATE TABLE simply invalidates all of the pages related to the table in one operation with no row-level checking. Since replication acts on row-level processing, a table-level command like this is not allowed. Same reason you can't do it on tables with foreign keys and it won't fire any triggers. Also the same reason it's generally so much faster than DEL ETE[Sorry for the extraneous spaces -- stupid IPS can't tell the difference between a comment and an attack][/quote]that's why i think this is a bogus question. technically everything in SQL is logged or whatever because the system has to execute it and it has to know what to execute against. but practically this is not a logged operation.i clicked the SQL Skills link expecting some cool info on how to recover the lost data, but no such luckWed, 09 Mar 2011 09:10:37 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxOver-thought this one and missed it. I should have gone with my gut.... Doh!Wed, 09 Mar 2011 09:10:13 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]Steve Jones - SSC Editor (3/9/2011)[/b][hr]It's clear to me, and if you think there is some implication that you are restoring to some specific point in time, you are reading something into the question that is not there. The question asks simply if you can restore to a point in time, and you can, based on the transaction log backups. The truncate statement is listed to see if you think this affects your log backup/recovery, which it doesn't.I'm sure you've read something about this on the Internet, but it's not true.[/quote]Thanks Steve. That's sums up my intentions when writing the question.Wed, 09 Mar 2011 09:02:43 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]alen teplitsky (3/9/2011)[/b][hr]if the TRUN CATE TABLE command is logged, why can't you run it on a table that is published for replication?[/quote]Essentially, TRUN CATE TABLE simply invalidates all of the pages related to the table in one operation with no row-level checking. Since replication acts on row-level processing, a table-level command like this is not allowed. Same reason you can't do it on tables with foreign keys and it won't fire any triggers. Also the same reason it's generally so much faster than DEL ETE[Sorry for the extraneous spaces -- stupid IPS can't tell the difference between a comment and an attack]Wed, 09 Mar 2011 08:47:25 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]SKYBVI (3/9/2011)[/b][hr]Good questioni would like to ask, we can restore at 9:15 right?But not after that?Regards,Sushant[/quote]I was expecting someone else to point this out, but I didn't see it so...You can restore your data right up to any point in time, up to 9:19 before the truncation or to 9:21 after the truncation and so on BECAUSE you have a full backup and trans log backups up to the last trans log before your point in time restore, in this case 9:15, and the important part that many DBA's are unaware of, the CURRENT log, which you backup (tail of the log) BEFORE you start your restore process, depending on the type of restore you are doing, which is a whole other topic when talking about tail log backups and "partial" restores :)Wed, 09 Mar 2011 08:34:12 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]SoftwareArtiste (3/9/2011)[/b][hr][quote][b]mvelic (3/9/2011)[/b][hr]Well, I got this wrong but I think I would have worded it differently. If you don't take a tail of the log backup after the TRUNCATE TABLE (or wait for the next log backup at 9:30), then you really can only restore up to 9:15. I think with just a little more description the question could have been more clear.[/quote]I completely agree. I answered the way that I did not because of the TRUNCATE TABLE statement, but because of the backup schedule. Assuming you were trying to restore after the TRUNCATE TABLE statement, but before the next backup, you could only restore up until 9:15.[/quote]I totally agree.Given the wording of the question, the TRUNCATE TABLE was the trigger to perform the restore (your reaction time as a DBA in an emergency is under scrutiny, and data loss (by deleting the contents of an entire table) would constitute an emergency)--so the restore should have happened anywhere between 9:20 and 9:30 (excluding start and end time). And without backing up the log tail restore will only include everything till 9:15.Anyway, this teaches me to make more assumptions; otherwise good question and even better discussion.(In retrospect, the choices of answers given are leaning towards the explanation given...not towards my interpretation...)Thanks,MichaelWed, 09 Mar 2011 08:31:21 GMTmichael.kaufmannhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxif the TRUNCATE TABLE command is logged, why can't you run it on a table that is published for replication?Wed, 09 Mar 2011 08:27:07 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]mvelic (3/9/2011)[/b][hr]Well, I got this wrong but I think I would have worded it differently. If you don't take a tail of the log backup after the TRUNCATE TABLE (or wait for the next log backup at 9:30), then you really can only restore up to 9:15. I think with just a little more description the question could have been more clear.[/quote]Actually, that's how I got the right answer. I thought of the tail of the log backup (which I thought the question was probing for knowledge of) and picked Yes. I did not even get worried about the truncate statement thinking that even if the truncation was NOT logged for some reason, my data and all of it's changes were still captured in the full and trans log backups and current log. I saw the truncate as a red herring also.Anyway, had to think about it for a minute to be sure I picked right. Thanks for a great question!Wed, 09 Mar 2011 08:24:53 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]SanDroid (3/9/2011)[/b][hr][quote][b]Duncan Pryde (3/9/2011)[/b][hr][quote][b]Gobikannan (3/8/2011)[/b][hr]I learned something form this question., Nice question.[/quote]Thanks, glad you liked it.Duncan[/quote]Duncan,I learned something also. I learned I am not the only person that was confused by your question. I do want to thank you for giving me a new interview question to ask. Anyone that answers this question like you would not be considered for a position where I work. :w00t: A good DBA always considers data loss when answering any question.People that are not RDBMS professionals depend heavily on those that are to answer questions like this with data loss always in mind. With that in mind, three of the answers could be argued as correct given the wording of the question.Maybe if we had not just re-written our DR plan and I was fresh out of SQL classes I would have answered differently.Thanks for the guessing game. I guess today I was not lucky. :laugh:[/quote]I'm not sure who is more confused - you by my question, or me by your post!Thanks for the feedback though - I think.DuncanWed, 09 Mar 2011 08:16:57 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxIt's clear to me, and if you think there is some implication that you are restoring to some specific point in time, you are reading something into the question that is not there. The question asks simply if you can restore to a point in time, and you can, based on the transaction log backups. The truncate statement is listed to see if you think this affects your log backup/recovery, which it doesn't.I'm sure you've read something about this on the Internet, but it's not true.Wed, 09 Mar 2011 08:16:05 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]Duncan Pryde (3/9/2011)[/b][hr][quote][b]Gobikannan (3/8/2011)[/b][hr]I learned something form this question., Nice question.[/quote]Thanks, glad you liked it.Duncan[/quote]Duncan,I learned something also. I learned I am not the only person that was confused by your question. I do want to thank you for giving me a new interview question to ask. Anyone that answers this question like you would not be considered for a position where I work. :w00t: A good DBA always considers data loss when answering any question.People that are not RDBMS professionals depend heavily on those that are to answer questions like this with data loss always in mind. With that in mind, three of the answers could be argued as correct given the wording of the question.Maybe if we had not just re-written our DR plan and I was fresh out of SQL classes I would have answered differently.Thanks for the guessing game. I guess today I was not lucky. :laugh:Wed, 09 Mar 2011 08:04:48 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxGood questionand easy, given the fact I recently had todo a point-in-time recovery due to a user selecting all records and "accidentally" pressing delete instead of copy (the fact that the app asks "are you sure you want to delete xxx many records?" despite).Wed, 09 Mar 2011 07:57:02 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxGood question. I too over thought it.Wed, 09 Mar 2011 07:48:22 GMTgregg_dnhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]Koen Verbeeck (3/8/2011)[/b][hr][quote][b]Ol'SureHand (3/8/2011)[/b][hr]Not sure I get the point of the trick question.It should be clear that you can restore to ANY point in time (at each 15 minute interval in this example). However, restoring to the 9:30 state will not recover your TRUNCATEd table ... so was that thrown in as a red herring ?If so, it got me ...[/quote]The question asked: Is it now possible to restore to a given point in time?It is possible, so the answer is certainly yes. The question didn't ask if it was possible to restore that specific table to any given point in time.Anyway, nice question.[/quote]Thanks Koen. I was wondering if I had went to sleep of if this question was worde teribly to make more than one answer possible.Glad to know I'm awake.Wed, 09 Mar 2011 07:46:13 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]SKYBVI (3/9/2011)[/b][hr][quote][b]Duncan Pryde (3/9/2011)[/b][hr][quote][b]SKYBVI (3/9/2011)[/b][hr]Good questioni would like to ask, we can restore at 9:15 right?But not after that?Regards,Sushant[/quote]No, TRUNCATE TABLE doesn't affect point-in-time restore, so you can restore to any time you like as long as you have the log backups.[/quote]u can restore at 9:30 too, but then wat abt the rows being deleted, if we want to have the data back, then we have to restore it at 9:15 ??Regards,Sushant[/quote]The question was asking if you could restore to any given point in time. The answer is that you can, because TRUNCATE table doesn't affect point-in-time restore.Obviously, if you restored to 9:19 you'd have the data, and if you restored to 9:21 you wouldn't. Does that make sense?Wed, 09 Mar 2011 07:42:00 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]Duncan Pryde (3/9/2011)[/b][hr][quote][b]SKYBVI (3/9/2011)[/b][hr]Good questioni would like to ask, we can restore at 9:15 right?But not after that?Regards,Sushant[/quote]No, TRUNCATE TABLE doesn't affect point-in-time restore, so you can restore to any time you like as long as you have the log backups.[/quote]u can restore at 9:30 too, but then wat abt the rows being deleted, if we want to have the data back, then we have to restore it at 9:15 ??Regards,SushantWed, 09 Mar 2011 07:18:55 GMTSKYBVIhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]mvelic (3/9/2011)[/b][hr]It's true. Besides, it's causing a good amount of discussion here about how point-in-time restores work, so even if it wasn't clear up front, people are still learning. Thanks for the question![/quote]No problem. As long as a questions generates a good discussion, it's served some of its purpose as you say. I'm getting quite into this question-writing lark, so all feedback - positive or "constructively critical" - does help. ;-)Wed, 09 Mar 2011 06:53:03 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxGreat Question! Paul's Myth a day pdf is available for Download. It is well worth the read!http://www.sqlskills.com/BLOGS/PAUL/post/Myths-and-misconceptions-60-page-PDF-of-blog-posts.aspxWed, 09 Mar 2011 06:44:20 GMTSQLBallshttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxIt's true. Besides, it's causing a good amount of discussion here about how point-in-time restores work, so even if it wasn't clear up front, people are still learning. Thanks for the question!Wed, 09 Mar 2011 06:42:30 GMTmvelichttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]mvelic (3/9/2011)[/b][hr]Well, I got this wrong but I think I would have worded it differently. If you don't take a tail of the log backup after the TRUNCATE TABLE (or wait for the next log backup at 9:30), then you really can only restore up to 9:15. I think with just a little more description the question could have been more clear.[/quote]When you're writing a question you try and think of how it will be read, but unfortunately it's very difficult to anticipate all interpretations. I should have probably added something about the time now being 9:45 or something, but you live and learn. :cool:DuncanWed, 09 Mar 2011 06:40:06 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]SKYBVI (3/9/2011)[/b][hr]Good questioni would like to ask, we can restore at 9:15 right?But not after that?Regards,Sushant[/quote]No, TRUNCATE TABLE doesn't affect point-in-time restore, so you can restore to any time you like as long as you have the log backups.Wed, 09 Mar 2011 06:37:07 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]Jim Sleeman-388184 (3/9/2011)[/b][hr]Trick Question and answer[/quote]It certainly wasn't meant to be. Thanks for the feedback though.Wed, 09 Mar 2011 06:36:02 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]mvelic (3/9/2011)[/b][hr]Well, I got this wrong but I think I would have worded it differently. If you don't take a tail of the log backup after the TRUNCATE TABLE (or wait for the next log backup at 9:30), then you really can only restore up to 9:15. I think with just a little more description the question could have been more clear.[/quote]I completely agree. I answered the way that I did not because of the TRUNCATE TABLE statement, but because of the backup schedule. Assuming you were trying to restore after the TRUNCATE TABLE statement, but before the next backup, you could only restore up until 9:15.Wed, 09 Mar 2011 06:29:30 GMTKnowledgeDraftsmanhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxIf your server blew up and all you had was the 9:15 log backup, that's as far as you could go. If you took another log backup after the TRUNCATE or waiting for the scheduled one to kick off at 9:30, then you could restore to a point in time just before the TRUNCATE. Like I said, I think the question just needed to be a little more clear.Wed, 09 Mar 2011 06:29:13 GMTmvelichttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxGood questioni would like to ask, we can restore at 9:15 right?But not after that?Regards,SushantWed, 09 Mar 2011 06:07:03 GMTSKYBVIhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxTrick Question and answerWed, 09 Mar 2011 06:03:50 GMTJim Sleeman-388184http://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspxWell, I got this wrong but I think I would have worded it differently. If you don't take a tail of the log backup after the TRUNCATE TABLE (or wait for the next log backup at 9:30), then you really can only restore up to 9:15. I think with just a little more description the question could have been more clear.Wed, 09 Mar 2011 05:58:34 GMTmvelichttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]mohammed moinudheen (3/9/2011)[/b][hr][quote][b]paul.knibbs (3/9/2011)[/b][hr]This is odd. I'm sure I've read stuff that implies TRUNCATE TABLE *does* invalidate log backups until the next full backup is run, which means none of the suggested answers was right--yet everyone is saying this is incorrect and that point-in-time restore is still perfectly possible after doing one of these. I'm confused![/quote]Were you referring to this statement [b]'backup log with truncate_only'[/b]. Running this would invalidate the log backups and we would need to take a full backup again to overcome this. As per my knowledge, a truncate statement wouldn't invalidate log backups.[/quote]Apparently there did use to be non-logged operations in SQL Server 7, but TRUNCATE TABLE wasn't one of them. These days there are no non-logged operations as far as I'm aware.Link: [url=http://support.microsoft.com/kb/272093]http://support.microsoft.com/kb/272093[/url]Also, see Steve's editorial from yesterday: [url=http://www.sqlservercentral.com/articles/Editorial/72676/]http://www.sqlservercentral.com/articles/Editorial/72676/[/url]Wed, 09 Mar 2011 05:31:54 GMTDuncan Prydehttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]paul.knibbs (3/9/2011)[/b][hr]This is odd. I'm sure I've read stuff that implies TRUNCATE TABLE *does* invalidate log backups until the next full backup is run, which means none of the suggested answers was right--yet everyone is saying this is incorrect and that point-in-time restore is still perfectly possible after doing one of these. I'm confused![/quote]Were you referring to this statement [b]'backup log with truncate_only'[/b]. Running this would invalidate the log backups and we would need to take a full backup again to overcome this. As per my knowledge, a truncate statement wouldn't invalidate log backups.Wed, 09 Mar 2011 03:54:48 GMTmohammed moinudheenhttp://www.sqlservercentral.com/Forums/Topic1075276-2901-1.aspx[quote][b]Duncan Pryde (3/9/2011)[/b][hr][quote][b]mohammed moinudheen (3/9/2011)[/b][hr]Thanks for the question Duncan. I had a feeling that this question was more at testing our understanding of the truncate statement than the recovery models.[/quote]It was sort of a bit of both. A misconception appears to be that TRUNCATE TABLE is non-logged or minimally logged. Minimally logged operations occur in BULK_LOGGED (and SIMPLE) recovery and prevent point-in-time restores for affected log backups. TRUNCATE TABLE, even though it behaves similarly to minimally logged operations, is not one and doesn't prevent point-in-time restore - even in BULK_LOGGED recovery.A good article by Kalen Delaney that explains the difference is [url=http://sqlblog.com/blogs/kalen_delaney/archive/2010/10/12/tsql-tuesday-11-rolling-back-truncate-table.aspx]here[/url]Duncan[/quote]Thanks Duncan for sharing the link.Wed, 09 Mar 2011 03:47:09 GMTmohammed moinudheen