交易記錄檔是 SQL Server 非常重要的工具；

它提供復原功能，同時也支援許多功能，例如 AlwaysOn Availability Groups、Transactional Replication、Change Data Capture，以及許多其他功能。

VLF

在交易紀錄檔內部，記錄檔會被切分成一系列的 Virtual log files。

當記錄檔中的最後一個 VLF 被寫滿時，SQL Server 會嘗試繞回到記錄檔開頭的第一個 VLF 繼續使用。

如果這個 VLF 尚未被截斷，因此無法重複使用，那麼 SQL Server 會嘗試讓記錄檔成長。

截斷的意思是，資料庫判斷這個VLF 已經不需要再使用，所以把這個 VLF 標記成可重用，至於什麼狀況會判斷成已截斷則必須根據 recovery model 去決定。

如果因為磁碟空間不足，或因為最大大小設定限制，導致 SQL Server 無法擴充記錄檔，則會拋出 9002 錯誤，並且該交易會被回復。

記錄檔內部的 VLF 數量，取決於記錄檔一開始建立時的大小，以及每次自動成長時所增加的大小。

如果記錄檔建立時，或每次成長時的增量小於 64MB，那麼會新增 1 個 VLF 到檔案中。

如果記錄檔建立時，或每次成長時的增量介於 64MB 到 1GB 之間，那麼會新增 8 個 VLF 到檔案中。

如果記錄檔建立時，或每次成長時的增量大於 1GB，那麼會新增 16 個 VLF。

這是 SQL Server 2022 的行為變更。在以前的版本中，如果記錄檔以小於 64MB 的增量成長，會建立 4 個新的 VLF。
這個變更可以減少那些以小幅度成長的記錄檔中的記錄檔碎片數量。

Recovery Model (簡單說明)

這是資料庫層級的屬性，用來控制交易如何記錄，這個位影響交易紀錄檔的維護方式。

總共有三種復原模式。

這會在備份的章節詳細說明

SIMPLE

在 SIMPLE 復原模式中，無法備份交易記錄檔。

交易只會被最小程度地記錄，而且交易記錄會自動被截斷。

在 SIMPLE 復原模式中，交易記錄只會存在到交易需要被復原／回復為止。

與多種 HADR（高可用性與災難復原）技術不相容，例如 AlwaysOn Availability Groups 和 Log Shipping。

這種模式適合只偶爾更新的報表資料庫，或是不需要頻繁備份的非正式環境資料庫。

因為在 SIMPLE 模式下，無法做到時間點復原；復原點目標只能是最後一次 完整備份（FULL backup） 或 差異備份（DIFFERENTIAL backup） 的時間點。

FULL

在 FULL 復原模式中，必須執行交易記錄備份。交易記錄只會在交易記錄備份過程中被截斷。交易會被完整記錄，因此可以進行時間點復原。這也代表必須擁有完整連續的交易記錄備份鏈，才能將資料庫還原到最近的時間點。

BULK_LOGGED

BULK_LOGGED 復原模式通常是暫時使用的模式。當你原本使用 FULL 復原模式，但需要執行大量 BULK INSERT 作業時，可以暫時切換到這個模式。當你切換到這個模式後，BULK INSERT 作業只會被最小程度地記錄。匯入完成後，再切回 FULL 復原模式。在這種復原模式中，可以還原到任何備份的結尾，但不能還原到兩個備份之間的特定時間點。

交易記錄檔數量

我認識很多同行，他們沒有專業的 DBA ，而他們通常都認為，多個交易紀錄檔可以提升資料庫效能。

但我必須說這是完全錯誤的。

這種想法可能源自於前面提到的資料檔，但事實是，交易紀錄是循序寫入的，即使新增多個交易紀錄檔，SQL Server 也會把他們視為單一交易紀錄來處理。

這樣就代表第二個交易紀錄檔，只會第一個交易紀錄檔被寫滿之後才會使用，因此，這種作法無法帶來任何效能上的好處。

以我的經驗來說，我看過很多朋友的資料庫有多個交易紀錄檔，但從來沒有遇過一個真正合理、有效的理由需要這樣做。

縮小交易紀錄檔

先重點說明結論
縮小交易紀錄檔不應該成為維護流程的一部份，把他納入維護sop 沒有任何好處。

正常的交易紀錄檔不是越小越好，應該是
長到足夠應付正常業務尖鋒，然後再內部循環重複使用

只有在某些情況下才必須縮小交易紀錄檔，通常這個原因，是資料庫中發生了非典型活動，例如一次性的 ETL 載入。

如果是這樣，造成交易紀錄檔成長到超過該磁碟區的空間門檻

1. 可以縮小
2. 也可以擴充
3. 在這個狀態下，縮小交易紀錄檔會更適合。

但是還是要仔細分析目前狀況，確認這真的是一次性的異常事件；如果他看起來會一直發生，那就應該是要去擴充容量。

增加容量之前，非常值得先調查是否可以用較小的交易、批次來處理。

縮小的語法如下

縮小交易紀錄檔一定是從交易紀錄檔的尾端開始回收空間，直到遇到第一個active VLF為止，所以執行之前，先做一次交易紀錄備份，並將資料庫切換到單一使用者模式，是比較合理的作法。
前項方法只適用於 FULL 或 BULK_LOGGED recovery model。
切換單一使用者只是為了避免其他連線跑新的交易，讓連線比較穩定。

Log Fragmentation 交易紀錄碎片

當交易紀錄因為 SIMPLE recovery model 中的 Checkpoint 作業而被截斷時，實際發生的是 :

任何可以被重複使用的 VLF 都會被截斷。

某個 VLF 可能無法被重複使用的原因包括 :

1. VLF 內含有與 active transactions 相關的 log records
2. 交易尚未被送到其他資料庫
3. Replication 或 AlwaysOn 拓樸仍然需要這 log records

交易紀錄檔內的 VLF 數量，並沒有一個絕對的固定規則，我通常會嘗試維持大約每 1 GB 2 個 VLF。

Transaction log 裡面的 VLF 數量不能太多，也不能太少。太多會碎片化、管理成本高；太少代表每個 VLF 太大，清理時也會變慢。所以大型 log 檔建議用適中的成長單位，例如每次 8GB。

可以用這個去看 VLF 的狀態，但是這不是微軟官方用法，隨時都會不支援，建議我寫的另外一種方法

DBCC LOGINFO()

前八個 VLF 的 CreateLSN 是 0，所以就可以知道這些 VLF 是在交易紀錄檔本身一開始建立的時候所產生的 VLF 。

其餘的 VLF 則是因為交易記錄檔擴充而建立的。

如果 vlf_parity = 0 代表他沒有有效的 log record。

vlf_sequence_number 最大個就是當前 VLF 正在寫的檔案。
所以這裡就可以看出，目前只有第二張圖框起來的那些 VLF 是可以被移除的。
如果交易紀錄持續成長，而不能重用的 VLF 沒有變更狀態一直保持現在這個樣子，那交易紀錄檔會越來越大是一個必然的結果。
要讓交易紀錄檔的 VLF 變更重用的狀態，就要做交易紀錄備份去截斷他；如此新的交易紀錄才會去寫在前面那些可重用的空間，進而讓整個 LDF 不再擴大。

下面這個查詢會查詢 sys.databases 目錄檢視表，並回傳最後一次導致某個 VLF 無法被重複使用的原因。

log_reuse_wait_desc 這是在查為什麼 SQL Server 不能把舊的 VLF 標記成可重用? 他會有多種結果如下：

但是這個不能保證是當下即時狀態，因為這是上一次 SQL Server 嘗試循環使用 VLF 的時候，被什麼原因擋住。

備份的東西寫在別的頁面，不在這裡細說，但是

在 Full recovery model 裡，SQL Server 的原則是 :
交易紀錄不能隨便丟掉，必須先被 log backup 之後才可以截斷。

至於什麼是 Full recovery model、什麼是 log backup這先不用管，反正這邊要說的就是，如果在 Full recovery model 下會發生

1. 交易發生
2. 寫進 transaction log
3. 做 transaction log backup
4. SQL Server 才能把舊 VLF 標記成可重用

那如果切換到 Simple recovery model，的確 log 會自動截斷，但是代價是會破壞 log chain。

LOG CHAIN 交易鏈一樣會在備份的時候完整說明
Log chain : 從一次完整備份開始，後面一連串 transaction log backup 串起來的復原鍊。

正確處理 交易紀錄檔碎片的流程

1. 先去了解為何 log 無法重複使用
2. 如果是 Full recovery model，而且原因是 Log_backup，就先做 transaction log backup
3. 讓大部分 VLF 變成 reusable
4. shrink log，把尾端可回收 VLF 移除
5. 用較合理的大小一次擴大 log
6. 減少 VLF 數量，改善 log fragmentation

下一篇 TABLE 分割
下一篇會有更多實作的語法，會很長很難貼圖，有人有什麼好方法嗎?