深入SQLServer日誌收縮

問題背景

SQLServer的日誌是DB級別的這點和MySQL不同，多個DB就需要維護多個日誌檔案並且每個DB的日誌檔案可以有多個，所以從空間資源的角度來說這給雲上的使用者帶來一些困擾，畢竟在雲上磁碟空間是要收費的，除了效能他們也非常關心這些日誌何時截斷重用、何時收縮

問題探討

事務日誌

在理解事務日誌基本概念的基礎上我們來做個有關收縮的測試：

    --1. 構建測試用的日誌
    --    tpcc是我的測試DB，ITEM表是之前生成的一個測試表，這裡不需要關心表內容
    USE [tpcc]
    GO
    DECLARE @VALUE INT
    SET @VALUE = 0
    WHILE @VALUE <= 100
    BEGIN
        SELECT * INTO ITEM_2 FROM ITEM
        DROP TABLE ITEM_2
           SET @VALUE = @VALUE + 1
    END
    GO    
    
    --2. 觀察當前等待日誌截斷重用的原因和每個DB的日誌使用量
    select log_reuse_wait_desc from sys.databases where name=`tpcc`
    go
    dbcc sqlperf(logspace)
    go

    --3. 備份資料和日誌
    BACKUP DATABASE TPCC TO DISK=`D:BACKUPTPCC.BAK` WITH COMPRESSION,INIT
    BACKUP LOG TPCC TO DISK=`D:BACKUPTPCC.TRN` WITH COMPRESSION,INIT
    
    --4. 再次觀察當前等待日誌截斷重用的原因和每個DB的日誌使用量
    select log_reuse_wait_desc from sys.databases where name=`tpcc`
    go
    dbcc sqlperf(logspace)
    go

    --5. 收縮日誌
    USE [tpcc]
    GO
    dbcc shrinkfile(log)

    --6. 檢視收縮後的日誌使用量        
    dbcc sqlperf(logspace)
    go

這一步也可以通過上一步收縮的結果計算出來（90112*8K = 704 MB）；

現在在理解基本概念的基礎上產生了第一個疑問，為什麼1G的日誌使用了4%卻只能收縮到704MB？

為了解答這個問題我們需要引入另一個概念 Virtual Log Files(VLF)，SQLServer為了方便日誌管理，邏輯上將事務日誌劃分為多個虛擬日誌檔案，我們要討論的收縮就是以虛擬日誌檔案為單位操作的。

具體可以參考日誌的結構

我們再來重複一次剛才的測試，這次加入對VLFs的記錄觀察；

在剛才的1、3、5步驟之後記錄如下SQL返回結果，幫助後續分析；

USE [tpcc]
GO    
dbcc loginfo

步驟1結束後

步驟3結束後

步驟5結束後

這裡跟收縮日誌最相關的是Stats欄位，0表示inactive，2表示active，active狀態的VLF通過checkpoint+日誌備份可以轉化為inactive，但有一個原則是header（可理解為最新的活動日誌）部分只能後推或迴繞（wraps around）不能move forward（跟offset相關）；

在開始備份前有11個活動VLF，checkpoint（資料備份的第一階段）+ 備份日誌後只有最新的VLF處於活動狀態（日誌截斷並標記老的VLF為inactive-可重用），收縮後釋放掉了未使用的VLF；

截止到這裡截斷和收縮實際已經達到了充分利用空間的目的，雖然直觀看物理檔案沒有下降多少但新的日誌已經開始迴繞；但即便如此一些客戶還在追求物理檔案的進一步減少，實際想達到這個目的也很簡單，依據之前講的header部分已經迴繞，再次備份收縮就可以了；

--再次備份
backup database tpcc to disk=`d:ackup	pcc.bak` with compression,init
backup log tpcc to disk=`d:ackup	pcc.trn` with compression,init

--再次收縮
USE [tpcc]
GO
dbcc shrinkfile(log)    

--觀察VLF
USE [tpcc]
GO    
dbcc loginfo    

為什麼沒有收縮最後一個inactive的VLF跟這個DB的日誌初始化大小、shrinkfile的引數有關，簡單說不回收最後一個VLF已經可以達到初始化的大小；

我們也可以通過VLF的總大小和物理檔案對比做一個驗證：

--通過FileSize計算要加8KB的頁頭                67043328+67043328+67043328+67043328+67043328+67043328+67043328+67559424+8192=536870912B=512MB

--通過最後一個偏移量算
469311488+67559424=536870912B=512MB        

結論和建議

• 日誌截斷依賴於checkpoint和日誌備份（FULL模式）
• 日誌截斷的含義是把VLF標記為可重用
• 日誌收縮的多少需要看VLF的header和VLF的數量以及大小而不是通過sqlperf返回的結果判斷（這也是很多DBA和使用者誤解的地方）
• VLF的數量是事務日誌建立時初始化好的一個初始值後續會隨日誌增長和增加，大小跟日誌檔案的大小、增長速度相關沒有恆等的計算方法
• 在非雲場景下（傳統使用者），不建議使用者頻繁做日誌收縮去回收空間，因為這種收縮是有很大開銷的，但肯定也不會一直增長，我們通過頻繁的日誌備份做截斷、迴繞日誌檔案以達到節省空間的目的；在雲場景下（RDS），依然也不建議頻繁收縮，但面對一些希望用其它資源換空間資源且業務場景允許的情況下，建議這部分使用者使用OpenAPI定製化自己的備份策略