SQL SERVER 究竟什么时候写日志？

昨天看到网上有一个关于SQL SERVER 课件，便随手下载了下来看看主要讲了些什么内容，于是看到了下面两个PPT页面

   

 

    由于第一张PPT上的内容不太准确（日志文件中没有“日志页”的概念，只有VLF的概念，可能是我们对“数据页”的概念太深刻了，因此弄了以“日志页”的概念出来，而PPT中说先更新高速缓冲区中的数据页，然后将事务日志写入到“日志页”，很容易让人理解成先更改高速缓冲区，然后将日志写入到磁盘上的“日志页”）,再加上我看PPT时比较"囫囵"(只看到前一张PPT,没有往后翻两下看后面一张PPT).因此我觉得PPT的作者在日志的写入顺序上有问题.索性查了一下资料,然后比较深入的思考了日志的写入顺序问题,同时也纠正了一些自己以往的不正确理解.

    该文主要包含以下内容:
     1.SQL SERRVER 日志管理器的大致工作内容与原理.
     2.实例探究SQL SERVER 事务日志的产生与写入磁盘磁盘. 
     3.一些其它的相关思考.

    第一部分:SQL SERVER 日志管理器的大致工作原理.

    日志管理器承担着事务日志的编排与写入工作。它维护着一个或多个被称之为“日志缓存”的连续的专用内存区域。由于SQL SERVER 事务日志必须按照一定的格式写入到日志文件中，因此日志缓存中的功能之一就是用来编排日志的格式。而当一个日志缓存区域被占满的时候，还有一个或多个日志缓存区域可以被用来保存新产生的日志记录。 
    其次，日志管理器维护着两个日志缓存队列，一个flushQueue,另一个是freeQueue。其中flushQueue包含的是等待被刷新到日志文件（物理磁盘）的日志缓存；freeQueue包含的是已经被刷新并且可以被再次使用的日志缓存。
    而日志的刷新工作主要一个被称之为“日志编写器”的线程来负责，它将依次遍历 flushQueue，一次仅将一个当前的日志缓存中的内容写入到磁盘上。
    而日志编写器的刷新工作由什么来触发呢？当一个事务被提交时或者日志缓存被占满时，当前的日志缓存就被放入flushQueue，日志编写器就必须开始工作。日志编写器的工作完成后， 日志管理器将会收到一个写入成功的信号，进而激活所有正在等待日志缓存刷新的所有进程，以继续完成工作。

    第二部分：实例探究SQL SERVER 事务日志的产生与写入磁盘。

    当一个更新语句被发出并获得相关锁以后，SQL SERVER 将先更改高速缓冲区中的相关数据页，在更改高速缓冲区中的页时，将会产生一条日志记录并放到日志缓存中，当这个更新语句被提交（COMMIT）时，这条存在于当前日志缓存中的日志记录将首先被成功刷新到磁盘上的日志文件中以后，再返回“更新成功”的确认信息到客户端。以上是事务比较“小”的时候日志写入的相关情况。而当事务比较“大”时，尽管事务没有被COMMIT,而日志也会被写入到磁盘上。
   下面我将以实例来证明以下几种情况：
    A. 当事务比较“小”时，只有事务被COMMIT时，日志才会被写入到磁盘上的日志文件中。
    B. 当事务比较“大”时，尽管事务没有被COMMIT，日志也会被写入磁盘上的日志文件中。

    实例一：要证明情况A比较麻烦，因为需要在事务被开始但没有被COMMIT时，查看磁盘上的日志文件中是否有相关的日志记录。而SQL SERVER 虽然提供了一个未公开的查看日志记录的命令DBCC LOG(数据库名)，但是这个命令却会将存在于日志缓冲区内没有实际写入磁盘的日志记录一并列出来。因此我不得不借助一个大家熟知的第三方工具Log Explorer。

    试验步骤：
    1.建立一个测试数据库northwind,恢复模式为完整。并且对其进行一次完整备份(这样SQL SERVER 才能将日志保存，否则将会被定期截断)，运行CHECKPOINT命令，然后再进行一次事务日志备份以截断所有不活动的日志。运行一下命令DBCC LOG(northwind)看看情况。 
       
   在上图的结果中，你将只能看到Operation分别为LOP_BEGIN_CKPT和LOP_END_CKPT的两条日志。这两条日志是刚刚进行“事务日志备份”而产生的两条日志，而其它的日志已经被截断。 

   2.测试数据库中建立一个表TEST(ORDER_ID INT,ZDESC   VARCHAR(100) ),然后插入一条测试数据。 

  CREATE   TABLE  TEST 
    ( 
       ORDER_ID     INT , 
       ZDESC          VARCHAR ( 100 ) 
     ) 

  GO  

    INSERT   INTO  TEST(ORDER_ID,ZDESC)  
                   VALUES (  1 ,’a’) 
   GO   

    成功执行后，我们再运行DBCC LOG(northwind)看看日志的情况：
           

 

   3.然后我们运行以下命令：

          BEGIN   TRAN  
               UPDATE  TEST  SET  ZDESC = ’B’ 
               WHERE  ORDER_ID = 1

      该命令开始了一个事务，将ZDESC列的值更改为‘B’，但是该事务没有被COMMIT. 
      运行DBCC LOG(northwind)查看日志的情况。
  
   大家注意看第54条日志，从第54条日志开始，就是我们运行上面的UPDATE事务所产生的所有日志，由于该事务并没有被COMMIT,我们必须想办法查看自54号日志开始的所有日志是否已经保存到了磁盘的日志文件中。这时，我们先将SQL SERVER服务改为手动启动，然后强行重新启动电脑，启动电脑后，在SQL SERVER未启动以前，拷贝northwind的日志文件到其它目录(虽然我们可以在电脑启动后，SQL SERVER 服务启动以后再次运行DBCC LOG(northwind)来查看日志的情况，但是我担心SQLSERRVER在启动的时候会进行恢复工作而对没有提交的日志进行什么处理)，我们还是利用Log Explorer来查看拷贝出来的日志文件中的日志记录。
   我们还是先DBCC LOG(northwind)看看情况：
  
  大家可以看到，先前的序号64，65号日志已经看不到了，而这两条日志，就是UPDATE语句产生的真正的日志，而54-63是进行修改工作系统内部的一些事务。然后我们再用Log Explorer来看看之前拷贝出来的日志文件中的日志。


    上图是Log Explorer显示的备份出来northwind的日志文件的详细情况，我们可以在上面的表格栏选中某一条日志，然后注意红色框框出来的LSN.LSN:36:87:1这条日志即是我们用DBCC LOG(northiwind)命令所显示的排序为64的即CurrentLSN为：00000036:00000087:0001这条日志，很明显，Log Explorer显示的这条日志和先前DBCC LOG(northiwind)显示出的日志并不相同，因此我们可以断定，一个“小”的事务在未被COMMIT以前，日志已经产生，并且存储在日志缓冲区，但没有写入到磁盘的日志文件中。而一旦该事物被COMMIT，日志将一定会被写入磁盘，这种情况各位园友可以自己去实际验证。


实例二：证明情况B比较简单，我们只需要让SQL SERVER运行一个较“大”的事务，然后观察磁盘上日志文件有没有被自动增长，如果增长了，那么日志肯定被写入到磁盘上了。

实验步骤：
1.观察northiwind当前日志文件的大小。因为我的northwind是刚刚新建的数据库，日志文件的物理大小为1M.
2.运行以下脚本，然后在观察日志文件的物理大小。 

    BEGIN   TRAN  
        DECLARE   @I    INT  
        SET   @I = 1  
        WHILE ( @I < 99999 ) 
             BEGIN  
                  UPDATE  TEST  SET  ZDESC =LEFT ( NEWID (), 10 ) 
                  SET   @I = @I + 1  
             END  

  该脚本被封装成一个事务，并且没有被提交（COMMIT）,运行完成后，我观察到的日志文件的物理大小为38.3M,如下图：

很明显，尽管该事务没有被提交(COMMIT),但是，只要日志缓冲区被填满，日志缓存中的日志就会被写入到物理磁盘上。及时我们回滚了该事务，我们依然可以用DBCC LOG(northwind)看到这些被回滚的日志。


第三部分：其它一些相关问题的思考
    1.当SQL SERVER修改高速缓冲区中的数据页时，日志便会产生，并放入到日志缓存。那么日志究竟是由缓冲区管理器产生后交给日志管理器，还是由日志管理器探测到缓冲区的修改，然后自己产生日志。
    2.日志缓冲中的日志，究竟要“编排”成什么格式？是否就是我们通过DBCC LOG(northwind)所看到的格式。
    3.日志编写器在讲日志写入磁盘时，如何知道该写到日志文件的哪一个VLF,也就是说它是如何知晓某个VLF是逻辑上的最后一个VLF。

 

 

下边是摘自http://blog.csdn.net/realsnow/archive/2009/06/22/4288566.aspx

 

 首先，SQL Server的数据缓存的实现，是基于Windows基础的，SQL Server和Windows具有相同的页面调度和页面分配方法，所以，日志的缓存和数据缓存都是在SQL Server的统一缓存中的，都是在"页"中的，所以“日志页”这个概念也是存在的，只是页上面的数据结构不一样。

  其次，课件中的锁更替（更新锁到排他锁的升级）完成后，数据写入缓存和日志写入缓存，不存在先后的关系，SQL Server本身是一个很复杂的产品，很抱歉我不能在课件中很好的描述他们之间的先后关系（事实上，他们之间没有先后关系，日志写入日志缓存页和数据写入数据缓存页，由不同的SQL Server管理组件负责，他们各自对内存页面的访问一般情况下都是并发的），日志是先写入到缓存页面中。

  下面这两段话摘自MSDN:

  若要了解预写日志的工作原理，最重要的是了解如何将修改的数据写入磁盘。SQL Server 维护当必须检索数据时，将数据页读入的缓冲区高速缓存。数据修改不是直接在磁盘上进行，而是修改高速缓冲存储器中的页副本。直到数据库中出现检查点，或者必须将修改写入磁盘才能使用缓冲区来容纳新页时，才将修改写入磁盘。将修改后的数据页从高速缓冲存储器写入磁盘的操作称为刷新页。在高速缓存中修改但尚未写入磁盘的页称为“脏页 ”。

  对缓冲区中的页进行修改时，将在记录修改的日志高速缓存中生成一条日志记录。 在将关联的脏页从高速缓冲存储器刷新到磁盘之前，必须将这条日志记录写入磁盘。如果在写入日志记录前刷新脏页，则该脏页便会在磁盘上创建修改。如果服务器在将日志记录写入磁盘前失败，则不能回滚此修改。SQL Server 具有防止在写入关联的日志记录前刷新脏页的逻辑。日志记录将在提交事务时写入磁盘。

  这两段话很清楚的说明了，日志首先是写入在了高速缓存区中，然后在事务提交的时候，写入到磁盘中，并且，事务没有写入磁盘的时候，数据页中的数据是不能够刷新到缓存中的，这是SQL Server保证数据一致性的特点。这是我打算在讲课的时候再去细细讲解的部分。

  最后，我觉得有必要说明一下，这个课程是针对有一定经验的系统开发人员和数据库开发人员的，目标是想要分享SQL Server中的一些原理性质的东西，所以在课程中，我会尽量的描述SQL Server的很多行为和原理，但是不会太多的涉及到SQL Server中很多太细节的内容--这部分内容，我研究了一段时间，是一个很辛苦的过程，而不是人人都愿意在课堂中承受这个过程的，但是在课堂中，我会给听课的朋友提供的是更加清晰的概念，但是我很高兴能够在其他的地方和感兴趣朋友们一起讨论，一起研究，一起进步。

再次，很感谢朋友们的关注和和认真！谢谢！

同时，希望有更多的朋友能够来到我们课程的现场，来进行交流。