SQLSERVER 交易紀錄碎片的解決方法與交易紀錄的收縮(shrink)

上篇討論了關於何謂交易紀碎片以及它對於的效能影響。

當你發現你的DB有交易紀錄碎片的問題時，首先要做的就是「降低碎片」，依照上篇所講的觀念，所謂的「降低碎片」，說白了就是「減少VLF的數量」。

VLF一旦被建立後，無法改變它的大小。所以「減少VLF的數量」意味著我們要刪除一些VLF。

而刪除VLF，主要是透過dbcc shrinkfile來達成，當然，如果你的DB容許暫停服務的話，你也可以直接把log砍掉重練，這裡我們不多做討論，本篇是使用dbcc shrinkfile。

要想更深入的理解dbcc shrinkfile，要先對VLF有些基本概念，如果你只想知道如何做，可以直接跳至「VLF碎片的解決」

VLF基本觀念

我們可以使用dbcc loginfo來查看VLF的資訊，以下圖為例，該資料庫的交易紀錄檔LDF，共被劃分為10個VLF來管理。

dbcc loginfo顯示的訊息中係依StartOffset排序，StartOffset代表了該VLF在LDF檔中的偏移量，也就是VLF的實體存放位址，也代表著VLF的順序。

FseqNo則代表了每個VLF的編號（0表示從未使用過）。此編號並不是固定的，它可以視為是一個會遞增的編號。

VLF是循環使用的，資料庫的交易紀錄從第一個FseqNo開始寫，當這個VLF寫滿後，即跳到下一個VLF寫入，並給予下一個VLF一個新的FseqNo編號（+1）。直到所有的VLF用完後，若之前的VLF已經可以重用，就會再循環使用。如果沒有VLF可用了，就會觸發自動成長。如果自動成長關閉或者因磁碟空間不足無法自動成長時，即會跳出交易紀錄檔已滿的錯誤訊息。

  

我們測試建立一個table如下

create table testVLF(c1 int identity,c2 char(1024))

接著新增約2000筆資料後，再執行dbcc loginfo

由上圖可以看到，目前交易紀錄的總量，佔用了3個VLF。

SQLSERVER對每筆交易紀錄，都會賦予一個唯一的編號（Log Sequence Number 簡稱LSN）。LSN 是經過排序的，因此如果 LSN2 大於 LSN1，表示 LSN2 所參考記錄中描述的變更，發生在記錄 LSN1 所描述的變更之後。

一、      在每個DB的交易紀錄中，LSN是唯一的。

二、      LSN是有順序性的，它是隨著交易的先後順序遞增，值越小的表示順序越前。

三、      LSN可以進行比較或排序，以方便建構還原順序。

我們用fn_dblog()去查看transaction Log時所到看的LSN，如下圖

圖中可以看到真實的LSN是一個「3段數值組成的字串」，3個數值中間以「:」隔開。LSN三段式的意義如下：

n  VLF序號（file sequential number）

n  VLF中的偏移量（offset）（以512bytes為單位度量）

n  Slot 編號

以上圖的最後一個LSN為例

000009b6:000016d9:0004 

在此篇中，我們有興趣的是第一部份「000009b6」，這是16進位的表示法，換算成10進位，即為「2486」。

各位可以再對照之前dbcc loginfo的結果圖，目前寫到的VLF，其FSeqNo的值即為2486。這表示了這筆交易紀錄是存在放FSeqNo=2486這個VLF之中。

（哈哈，這裡有點講過頭了，LSN的概念在一般的DBA工作上，可能永遠也用不到，但你如果對交易紀錄的結構有興趣，這則是必備的常識）

每個VLF視其中存放的交易紀錄的情況，在dbcc loginfo的結果中，基本上會有0跟2兩種狀態（status）

status 0：Reusable or Unused à代表可使用的空間

status 2：Active or Recoverable à代表不可使用的空間

它的意義在官網的說如下

Active (簡單的說就是：交易使用中，不能shrink)

The active portion of the log begins at the minimum LSN representing an active transaction. The active portion of the log ends at the last LSN written. Any VLFs that contain any part of the active log are considered active VLFs.

Recoverable （交易已結束，但因為有其它的：如mirror,replication，還沒完成，所以不能shrink）

The portion of the log preceding the oldest active transaction is needed only to maintain a sequence of log backups for restoring the database to a former state.

Reusable （原本有在用，但資料已經備走了或者不再需要，可以shrink）

If transaction log backups aren’t being maintained or you’ve already backed up the log, VLFs before the oldest active transaction aren’t needed and can be reused. Truncating or backing up the transaction log changes recoverable VLFs into reusable VLFs.

Unused（閒置中，從沒用過的，可以shrink）

One or more VLFs at the physical end of the log files might not have been used yet if not enough logged activity has taken place.

VLF 碎片的解決

要解決VLF的碎片，簡單的說，就是刪除過多的VLF，並且設定適當的自動成長。
底下列出要執行dbcc shrinkfile來解決VLF碎片的步驟：

1. 如果DB復原模式是full或bulk logged，請先執行backup log，目的是將交易紀錄檔的資料清空。

2. 如果DB復原模式是simple，執行checkpoint，目的同樣是為了將交易紀錄檔的資料清空，但也可以不用做，因為系統自己會做。

3. 使用dbcc loginfo觀察最大的active VLF（status<>0的max FSeqNo），最好位於log file的前面開始處。

4. 執行dbcc shrinkfile(‘邏輯檔名’,收縮目標大小)，收縮log file到一個適當的大小，至於何謂適當的大小，這完全看你的系統狀況而定了，當然最佳的選擇是收縮到MinimumSize後，再重新建立新的VLF。

5. 修改並設定適當的log file大小（alter database），請注意，設定時請參考上篇文章自行計算，一個VLF不要大於500MB。

6. 若有設定自動成長，應視實際環境，給予固定的大小，不要給太小，以免日後又造成VLF Fragemention。

VLF的數量及大小多少才適合，有幾個重要的參考指標：

一、每個VLF size不超過500MB。

二、VLF的數量上限不要超過1000個。

三、VLF數量的甜蜜點（sweet spot）為100個。

dbcc shrinkfile的概念

使用dbcc shrinkfile 的指令去縮小LDF檔時，其實非常的單純，就是以StartOffSet為順序，從尾部開始，將 status 為 0 的VLF收回，但在所有VLF中至少需有一個可用（status =0 ）的VLF。

SQL SERVER會試著去完成你所要求的收縮目標大小，當它發現VLF尾部的空間即使全放掉，也達不到你要求的收縮目標大小時，會自動在log中塞入一些NO-OP log，也就是一些無意義的log，這個目的是為了讓active VLF往下跳一個，以便backup log或checkpoint執行時能清除卡關的那個VLF。

如果你的DB的復原模式是simple mode，它會自動再做一次checkpoint，去試著清除LOG，這些動作的目地都只是為了讓shrink後的大小能符合你收縮目標大小的設定。

因此有時候明明資料沒多少但卻不能shrink，主要是目前的active VLF正好在LDF檔案的尾端。

以上圖這種情況，如果DB是full mode，執行交易紀錄的shrinkfile，將無法釋放任何的空間，因為最後一個VLF的狀態為2。

這時候你可以等待或手動產生一些交易行為，然後執行backup log(full mode)或checkpoing(simple mode)並觀察「最高的active」VLF，最佳的位置是在LOG File的開頭處。然後再執行shrink。

至於 AGs 的log shrink，由於shrink是寫入的動作，所以只能在主要節點進行shrink。

dbcc shrinkfile(‘邏輯檔名’,收縮目標大小)，如果目標大小給0的話，表示shrink 到 MinimumSize (初始化大小)。

mdf/ndf檔的 MinimumSize，可以從 dbcc page(xxx,1,0,3) 中的 MiniSize 看出來，但log file則無法使用dbcc page去看。

要查詢一個資料檔或交易紀錄檔的MinimumSize，其實可以簡單的借用 dbcc shrinkfile(‘邏輯檔名’, 99999) ，意思是給一個大值，確保它不會真的去壓縮，而只是想看它回傳的訊息。

圖中MinimumSize(8K Page)即是

所以，shrinkfile如果大小給0，它只會 shrink 到 MinimumSize，請注意。

資料行名稱	Description
DbId	Database Engine 試圖壓縮之檔案的資料庫識別碼。
FileId	Database Engine 試圖壓縮之檔案的檔案識別碼。
CurrentSize	檔案目前所佔的 8 KB 頁數。
MinimumSize	檔案所能佔用的 8 KB 頁數最小值。 這對應於檔案的大小下限或最初建立的大小。
UsedPages	檔案目前所用的 8 KB 頁數。
EstimatedPages	Database Engine 估計檔案可以壓縮成 8 KB 頁面的數目。

常常在shrinkfile時發現明明交易紀錄檔的「閒置空間」很大，但就是shrink不下來，後來經過一番研究才逐漸了解它背後的運作方式，此時才知道有個東西叫VLF，接著才又知道原來VLF也有碎片.，然後又再潛入交易紀錄檔的解析….等。
當逐漸了解交易紀錄檔的本質及運作原理後，在處理一些跟交易紀錄有關的問題時，心態就穩定許多，不容易再被一些問題的表象迷惑。