你的SQL Server日誌增長參數設定的正確嗎？

大家都知道SQL Server記錄檔用來記錄事物日誌，但是SQL Server資料庫引擎內部將每一物理記錄檔分成多個虛擬記錄檔管理。而且虛擬日誌的多少回影響資料庫的效能。

 

虛擬記錄檔沒有固定大小，且物理記錄檔所包含的虛擬記錄檔數不固定。資料庫引擎在建立或擴充記錄檔時動態選擇虛擬記錄檔的大小。

 

資料庫引擎嘗試維護少量的虛擬檔案. 只有當記錄檔使用較小的 size 和 growth_increment 值定義時，虛擬記錄檔才會影響系統效能。如果這些記錄檔由於許多微小增量而增長到很大，則它們將具有很多虛擬記錄檔。這會降低資料庫啟動以及記錄備份和還原作業的速度。

 

那是不是虛擬檔案越少越好呢？也不是。

 

我們知道交易記錄是一種迴繞的檔案,如果每個虛擬日誌的檔案大小為8GB，那麼只有這8GB的檔案全部變成不活動時才會被清除，這樣SQL Server一次就需要清除8GB的日誌，也會影響效能。

 

所以需要將虛擬日誌的大小維護在一個合理的水平。這裡先看一下虛擬日誌的增長：

 

日誌增長值64MB = 4VLFs

日誌增長大於 64MB小於1GB = 8 VLFs

日誌大於1GB = 16 VLFs

 

Kimberly給出的建議是對於記錄檔比較大的系統虛擬大小檔案為512M，也就是起始記錄檔大小為4GB(16個VLF)，然後每次以4GB的速度增長，這樣可以提高效能。

 

使用DBCC Loginfo可以查看VLF的數量：

 

這裡我建立Allentest資料庫，LDF檔案大小為24MB，可以看到初始為4個VLF檔案，活動的VLF檔案1（status=2）。建立一張表執行下面的語句使日誌增長，可以看到已經變成2個活動的VLF：

 

create table test(namevarchar(20))

insert into testvalues('allentest')

go 100000

 

備份LOG檔案後查看VLF的狀態：

backup logallentesttodisk='d:\mssql\allentest.trn'

 

可以看到第一個VLF檔案已經變為可用狀態了，但是第二個VLF檔案因為沒有被寫滿所以繼續使用。

 

更多資訊可以參考：交易記錄物理體繫結構