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。