SQL Server LDF 容量大爆炸 (SQL Server ldf truncate & shrink)

說明
實驗過程
參考資料
相關連結

筆記 SQL Server ldf 為什麼會容量大爆漲以及因應之道。

說明

會發生 ldf 容量大爆炸的可能原因如下：

資料庫復原模式選擇「完整」，但從未進行 log 備份
進行了大量的批次寫入動作或者是複雜龐大的交易所導致

本次說明如何處理第一種情境

復原模式

資料庫的復原模式選擇「完整」，主要是希望能夠使用 point-in-time 的還原能力，讓資料庫藉由交易紀錄還原到特定的時間點上。而選擇完整的復原模式，只有在交易紀錄做備份的時候，log file 才會「清空」檔案空間，從而容納新的交易紀錄檔，否則交易紀錄會繼續的成長，直到儲存空間用盡。

而簡單復原模式，在 CheckPoint 時間點上，就會自動清空 log file 檔案空間，因此才不會有第一種情境的問題，但簡單的復原模式仍有可能出現 ldf 容量大爆炸，那就可能是第二種情境所造成的，不在本次的討論之中。

網路上如果搜尋 ldf 可以找到很多的教學，千篇一律的都是告訴管理者將復原模式從「完整」調整為「簡單」，然後進行縮小交易紀錄檔或者備份交易紀錄（或各種觸發 CheckPoint的行為），最後再透過 DBCC 或者修改檔案容量的方式調降交易紀錄檔，最後再調整回完整模式。

雖然這樣子的方式真的能夠讓交易紀錄檔的容量變小，但卻不是根本的處理方式，因為真正的問題來自於資料庫管理初學者的對於資料庫備份策略、復原模式以及交易紀錄檔的誤會。

Truncate Log file 清空交易紀錄檔

當完整模式備份交易紀錄檔或者簡單模式觸發 CheckPoint 行為時，交易紀錄檔會進行「清空」。清空的意涵在於將檔案容器的空間釋放出來，但不會減少實際上在檔案系統中所占用的空間大小，這一點可以比較備份交易紀錄前後，並透過指令來做確認：

DBCC SQLPERF(LOGSPACE)

Shrink Log file 縮小交易紀錄檔

多數時候資料庫管理者以及系統管理者，真正想要解決的是縮小交易紀錄檔，也就是令交易紀錄檔在檔案系統中所佔用的大小減少，而藉由 DBCC 或者修改檔案容量的方式調降交易紀錄檔，可以讓交易紀錄的檔案大小降低。

但如果縮小交易紀錄的檔案大小，而交易紀錄卻有頻繁增長的需求，反而會造成資料庫伺服器的效能不佳。

逃避雖然可恥，但是很有用

ALTER DATABASE [LogTest] SET RECOVERY SIMPLE; --復原模式調整為簡單

dbcc shrinkfile(LogTest_log, 2); --縮小交易紀錄檔

ALTER DATABASE [LogTest] SET RECOVERY FULL; --恢復復原模式為完整

正確面對 ldf 爆炸的方式

復原模式選擇完整的資料庫，同時也定期進行資料庫交易紀錄備份，因此資料庫保持著 point-in-time 的還原能力，但僅需要一定時間範圍的交易紀錄備份，該如何處理？

落實備份策略，刪除特定期間以上的備份檔，如此一來不再需要的交易紀錄檔就會消失，所以 ldf 容量大爆炸在情境一中的問題，其實是來自於沒有適當的資料庫備份策略，所造成的現象。

所以當碰到 ldf 容量大爆炸的時候，不是急著縮小交易紀錄檔的大小，還是要替該資料庫安排備份策略，評估是否調整為簡單復原模式，或者是設定 JOB 或者維護計畫定期進行完整、交易紀錄備份，並且自動清除逾期的備份檔案，讓交易紀錄能夠發揮其用途。

實驗過程

以下驗證下列行為

只有交易紀錄備份(Backup Log)能夠清空交易紀錄檔的大小、完整備份並不會
清空並不會縮小檔案的大小
調整復原模式

-- 確認目前的交易紀錄檔案
DBCC SQLPERF(LOGSPACE)

-- 輸入 10,000 筆測試資料，讓交易紀錄增加
DECLARE @num INT;
SET @num=1;
WHILE @num < 10000
BEGIN
    INSERT tLogTest(col1)
    VALUES(@num);
    SET @num = @num + 1;
END

-- 確認目前的交易紀錄檔案，會發現空間使用增加、但檔案大小可能沒有發生改變
DBCC SQLPERF(LOGSPACE)

-- 完整備份
BACKUP DATABASE LogTest
TO DISK = 'C:\backup\LogTest.bak'
   WITH FORMAT,
      MEDIANAME = 'LogTest',
      NAME = 'Full Backup of LogTest';
GO

-- 確認交易紀錄檔案，會發現沒有改變
DBCC SQLPERF(LOGSPACE)

-- 備份交易紀錄檔案
BACKUP LOG LogTest
TO DISK = 'C:\backup\LogTest.trn'
   WITH FORMAT, DESCRIPTION = 'LogTest log backup';
GO

-- 確認交易紀錄檔案，會發現已經清空交易紀錄檔的使用率，但檔案大小不會改變
DBCC SQLPERF(LOGSPACE)

-- 輸入 20,000 筆測試資料，讓交易紀錄增加
DECLARE @num INT;
SET @num=1;
WHILE @num < 20000
BEGIN
    INSERT tLogTest(col1)
    VALUES(@num);
    SET @num = @num + 1;
END

-- 檢視交易紀錄後，發現檔案大小膨脹
DBCC SQLPERF(LOGSPACE)

-- 備份交易紀錄檔案
BACKUP LOG LogTest
TO DISK = 'C:\backup\LogTest.trn'
   WITH FORMAT, DESCRIPTION = 'LogTest log backup';
GO

-- 雖然清空交易紀錄，但檔案大小沒有改變
DBCC SQLPERF(LOGSPACE)

-- 調整為簡單復原模式
ALTER DATABASE [LogTest]
SET RECOVERY SIMPLE;

-- 縮小資料庫的交易紀錄檔案大小
dbcc shrinkfile(LogTest_log, 2)

-- 交易紀錄檔大幅的縮小
DBCC SQLPERF(LOGSPACE)

-- 恢復為完整復原模式
ALTER DATABASE [LogTest]
SET RECOVERY FULL;

參考資料

要如何有效的做跨伺服器資料庫備份

SQL Server Transaction Log Interview Questions

SQL Server Transaction Log Administration Best Practices

Why Does the Transaction Log Keep Growing or Run Out of Space?

說明