XPost: microsoft.public.sqlserver.server   
   From: esquel@sommarskog.se   
      
   Gene Wirchenko (genew@ocis.net) writes:   
   >   1) I need some definitions:   
   >           physical log  (I think this a fixed-length file.)   
   >           logical log   
   >           virtual log   
      
   To be honest, I don't know what the author may have in mind, least of all   
   the first two. A transaction log consists of a number of Virtual Log Files   
   (VLF), but I would not really consider it beginner's matieral. Heck, I only   
   have a dim understanding of it myself.   
      
   >   2) My understanding of the term "transaction log" is that such a log   
   > would be preserved until a backup is done, but the text has   
   > conflicting statements.  There is something about a checkpoint.  When   
   > is truncating a transaction log is done by SSE?   
      
   First of all, keep in mind that you can use different recovery modes: full,   
   bulk-logged and simple. For the purpose of this discussion, we can consider   
   bulk-logged as a variation of full.   
      
   If you have simple recovery, SQL Server basically truncates the transaction   
   up to the point where there is data that might be needed for some purpose.   
   The two main purposes are rollback and recovery. There are also special   
   puroses like replication, but let's ignore that for now.   
      
   To understand this, you should view the transaction as a long chain of   
   events, which themselves may be unrelated (that is, come from different   
   processes and concern different tables), but they all come in one single   
   sequence, and the sequence can be truncated at some point, but it can never   
   happen that things are taken out in the middle.   
      
   For rollback this means that if we read the transaction log from the   
   (current) beginning, and we find a record that comes from a transaction that   
   has not yet been committed, we must stop, because this record may be needed   
   to rollback the transaction.   
      
   Recovery is a process which happens when a database comes online again, for   
   instance after restarting SQL Server. During recovery uncommitted   
   transactions must be rolled back, and committed transactions must be rolled   
   forward. To understand the latter, we should know that SQL Server first   
   writes to the transaction log. The data pages are updated in memory, and   
   with a regular interval SQL Server writes dirty pages to disk. The latter is   
   known as a *checkpoint*. If SQL Server goes down between COMMIT and   
   checkpoint, the transaction needs to be rolled forward on recovery.   
      
   In simple recovery, truncation of the log happens at checkpoint, up to the   
   point for the checkpoint, unless there are open uncommitted transactions.   
      
   In full recovery, the transaction is never truncated automatically, only   
   when you explicitly back up the transaction log. This permits you to recover   
   from a failure, and restore the database in the state in was in just before   
   the failure. (Provided that you were able to back up the tail of the backup   
   after the failure, else you can only restore to the point of the most recent   
   transaction log backup.)   
      
   What is important to understand for the newbie DBA is that you need to   
   decide on which recovery requirements you have. If there is a disaster,   
   are you content with restoring the most recent backup? Or do you need   
   up-to-the-point recovery?   
      
   There are many people who run with full recovery, but who have not   
   understood that they need to back up the transaction log, with the result   
   that their transacion log grows and grows. Some people think that the log   
   is truncated you back up the database, but this is *not* the case.   
      
   If you do not see any need for up-to-the-point recovery, you can forget   
   most about the transaction log, as long as you make sure you have   
   simple recovery for your database. If you need up-to-the-point recovery,   
   you will also need to set up transaction log backups - as well as   
   to learn to actually use them in case of a disaster. Tip: in a development   
   environment, simple recovery is almost always sufficient.   
      
   >   3) When are committed transactions used to update the tables?  I   
   > would have thought that this would happen fairly often,   
      
   Yes, as I said, this happens at every checkpoint (which typically is   
   once a minute, although this can vary.)   
      
      
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)