InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 04:28:41 GMT20http://www.sqlservercentral.com/Forums/Topic1118800-1173-1.aspxThank you Tony! That was extremely useful ! A printout of this will be with me till I work on SQL Databases....:)With teachers like you,I could be a rocket scientist!Fri, 16 Mar 2012 10:46:53 GMTbsantosh6http://www.sqlservercentral.com/Forums/Topic1118800-1173-1.aspxHi,I have a basic question for which I can't manage to find a certain and final answer.When a transaction is "committed", what exactly does that mean in the log?Is there an "OK" or "COMMITTED" written after the log records?Is is just the end time?Thanks,NicolasFri, 18 Nov 2011 03:40:36 GMTnicolas.pageshttp://www.sqlservercentral.com/Forums/Topic1118800-1173-1.aspxThanks Tony... Good Article...Wed, 17 Aug 2011 04:52:47 GMTJagadish Kumar Punnapuhttp://www.sqlservercentral.com/Forums/Topic1118800-1173-1.aspxFesterson,Thanks, that was definitely enlightening. I read through the bol stuff, and realized I had mis-used a term in my question. What I was wanting to know was really: When using read-Committed (instead of read-UNcommitted), if the case is that un-committed data can be written from the cache to the .mdf, then where is the previous committed value stored? I understand that with the new Snapshot isolation that tempdb is where the different row versions are, but without snapshot isolation on--and one is reading data using the default read-committed--where are previous versions stored? Would this just exist in the data cache then as a 'row version' there--the same idea as the Snapshot isolation but not using Tempdb and just doing that same mechanism in the cache? Again, this is purely academic so I can get a clear, virtual picture of what's happening with the data in these little slices of time. If my assumption is correct then it makes sense then that if a crash happened just at that moment that the .mdf's disk value of that row would be rolled back during recovery because that transaction was known not to be committed as recovery reads the .ldf.--JimMon, 20 Jun 2011 11:06:23 GMTJames Stephenshttp://www.sqlservercentral.com/Forums/Topic1118800-1173-1.aspx[quote][b]James Stephens (6/18/2011)[/b][hr]Hi all,I have a related question that I think was somehow answered in the above posts but I just wanted clarification for academic purposes:Suppose I change a field of data from Table-1 from "Some Data Before" to "Some Data After".Now suppose that this is not committed but due to memory pressure or some other reason there's a checkpoint [/quote]Memory pressure doesn't enter into when a checkpoint is done. It's happens automatically only when the Checkpoint Handler estimates that there is too much uncheckpointed information in the log, and recovering it would take longer than [i]recovery_interval[/i] minutes.[quote]and it's written from the log cache to the disk (.Ldf), [/quote]No, this happens far more often than at checkpoint time. The article asserts that the .LDF is written to at commit time, but my question about the million rows shows that it happens before then. [quote]and also from data cache to the disk (.mdf). [/quote]This is correct. Dirty pages in the data cache, committed or not, are written to the .MDF[quote]Now I want to do a Select with "read uncommitted" (I'm not fully sure but I think this is done using "With (NoLock)"??).[/quote]No, read uncommitted means "I will ignore the exclusive locks of other users as I do my reads, and read the uncommitted changes they have made to the row". Also known as a Dirty Read, there is the risk of reading something that will soon be rolled back by the other user.You can enable it with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDNot that I'm saying this is a good idea.[quote]From where does my Select statement fetch the "Some Data Before"? Does it, seeing the With(Nolock), divert the request to the .ldf data and step back to the last committed value of that particular row?[/quote]Quick rule: Unless you are in the middle of recovery, or some other unusual process, the log is not read. In particular, a select statement will never read the log.Your select statement, like any other, will first look for the rows you want in cache. If it finds it there, all's good - if not, the page of rows is read from disk.Notice that [b]you [/b]doing a select is very different from [b]someone else [/b]doing a select on the changes you've made. Your connection can see all the changes you have made, committed or not. Other users can [ordinarily] see only the changes you have committed. Unless, of course, they have turned on READ UNCOMMITTED, which they shouldn't do.For more complexity, look in Books Online for Snapshot Isolation.Sun, 19 Jun 2011 19:40:07 GMTFesteronhttp://www.sqlservercentral.com/Forums/Topic1118800-1173-1.aspxAmazing read. Thanks for the contribution.Eagerly looking forward to the next chapters (levels).Best regards,Sat, 18 Jun 2011 15:28:56 GMTcodebyohttp://www.sqlservercentral.com/Forums/Topic1118800-1173-1.aspxHi all,I have a related question that I think was somehow answered in the above posts but I just wanted clarification for academic purposes:Suppose I change a field of data from Table-1 from "Some Data Before" to "Some Data After".Now suppose that this is not committed but due to memory pressure or some other reason there's a checkpoint and it's written from the log cache to the disk (.Ldf), and also from data cache to the disk (.mdf). Now I want to do a Select with "read uncommitted" (I'm not fully sure but I think this is done using "With (NoLock)"??).From where does my Select statement fetch the "Some Data Before"? Does it, seeing the With(Nolock), divert the request to the .ldf data and step back to the last committed value of that particular row?Thanks,JimSat, 18 Jun 2011 08:45:42 GMTJames Stephenshttp://www.sqlservercentral.com/Forums/Topic1118800-1173-1.aspxI believe the before data is included in the log record. Everything needed for roll forward / roll back is in the log, so it must either be a linked record or the same recordFri, 17 Jun 2011 14:22:46 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1118800-1173-1.aspxAgreed. I think that's all included in the article.But specifically, when the transaction that has made row changes is checkpointed and not committed, and for whatever reason needs to roll back, where does the old version of the row come from? Not the data file - it was updated at checkpoint. Tony said it was a reversing entry - but how is that generated?If I change a salary from 53 to 57, and the change is checkpointed, is the data file updated? Yes.Is the change recorded in the log? Of course.But where is the 53 preserved? We need to keep that value somewhere in case the transaction [which is still running] decides to roll back. I thought it was in the before image of the row recorded in the log, but if I'm wrong about this I need to know.Fri, 17 Jun 2011 12:14:20 GMTFesteronhttp://www.sqlservercentral.com/Forums/Topic1118800-1173-1.aspxcheckpoints flush log records to disk, both committed and uncommitted. When the database recovers (in restore or on restart), the redo/undo actions either mark the data files with the changes or remove the changes from the data files, depending on whether the transaction was committed.Fri, 17 Jun 2011 10:25:40 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1118800-1173-1.aspxThanks for starting this series, Tony. It will be good to have a complete transaction log referenceCould you clarify this:[quote]Data is modified in the data cache, and the log records to describe the effects of the transaction are created in the log cache. When a transaction is committed, the log records are written to the transaction log, on disk.[/quote]I tried running your million row insert within a transaction, and even before the commit the log was at 99.74MB and 93.46% full. This makes sense because the transaction creates such memory pressure that it couldn't be contained by RAM. The log records must be written as the transaction occurs, not at commit time as you claim.How do log records "describe the effects of the transaction"?Also, I'd like to see more about how a checkpointed but uncommitted transaction gets rolled back. I always thought that the log contained both before and after images of the row, but you talk about "reversing entries" being generated. I can see how a delete can reverse an insert, but how can an update reverse an update without knowing the old values?Good luck with the stairwayFri, 17 Jun 2011 08:30:15 GMTFesteronhttp://www.sqlservercentral.com/Forums/Topic1118800-1173-1.aspxThanks Tony for sharing this and looking forward to this discussion....I am actually working on a project where have to enable Tlog backups on one of my system.The system is quite heavily used so have to first gather information on what average per day t log size can get to so as to ensure we have enough disk space to accommodate it comfortably and even little bit worried on performance impact as well but then point in time recovery is only available when we enable this option so don't have any choice really....Fri, 17 Jun 2011 00:37:23 GMTVineet Khannahttp://www.sqlservercentral.com/Forums/Topic1118800-1173-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Stairway+Series/73775/">Stairway to Transaction Log Management in SQL Server, Level 1: Transaction Log Overview </A>[/B]Thu, 02 Jun 2011 08:54:37 GMTTony Davis