壓縮 LDF & 交易

Issue Log

推測更改資料造成 ldf 增長大小 *(header + dataTypeSize * 2) * rows*

• header: 70 bytes
• dataTypeSize*2 : 各資料型態大小不同，乘2是因會記錄變更前後
• rows: 更動幾筆資料

shrink ldf 交易檔瘦身

交易流程簡介:

begin tran             -- LSN 200 : LOP_BEGIN_XACT  TXID1 (log buffer)
  insert into tbl1...  -- LSN 201 : LOP_INSERT_ROWS TXID1 (log buffer)
                       -- LSN 202 : LOP_MODIFY_ROW  TXID1 (log buffer)
  insert inot tbl2...  -- LSN 203 : LOP_INSERT_ROWS TXID1 (log buffer)
                       -- LSN 204 : LOP_MODIFY_ROW  TXID1 (log buffer)
commit tran            -- LSN 205 : LOP_COMMIT_XACT TXID1 (LSN 200-205 flush to ldf from log buffer)

1. INSERT → 交易紀錄 LSN 寫到 log buffer
2. COMMIT → 記錄從 log buffer flush to LDF，完成後才返回 commit 成功
3. Data page 還在記憶體 buffer pool（dirty page）
4. CHECKPOINT → Dirty page flush 到 data file

CHECKPOINT 頻率由 TARGET_RECOVERY_TIME (60s)設定決定， 也就決定 Crash Recovery 資料量，越久資料量越大

當機器 crash，重啟後 MSSQL 會自動回溯自上次 CHECKPOINT 後得資料， 也就是交易紀錄已存到 ldf ，但 data 還在記憶體，來不及 flush 到 mdf 的部分，資料量正比CHECKPOINT 頻率

Crash Recovery：

• 沒看到 COMMIT -> Undo(rollback)
• 有看到 COMMIT -> Redo

MSSQL 以 TXID (transaction ID) 分組，一個 begin tran 中的交易同屬一組 Undo & Redo 以 TXID 為單位(不是 batch)

Transaction 與 Batch 怎麼區分?

1.Transaction

• 一個 BEGIN TRAN 內的動作屬同一交易，有同 TXID
• 沒有 BEGIN TRAN 的動作會被自動分別包成不同 TXID

2.Batch

• 指一次送到 SQL Server 執行的 T-SQL 語句集合
• 只要分開送出就等於兩個 batch

-- batch1
begin tran
  insert into tbl1 -- 送出執行

-- batch2
  insert into tbl2
commit  -- 送出執行

1.Stand Alone DB

SIMPLE recovery model 不支援時間點還原， checkpoint 後 vlf 全部變成可回收狀態，才能夠 shrink

Steps: 1.將 recovery model 改成 simple 2.DBCC SHRINKFILE( N'db_log_logical_nam', filesizeMb ) 3.將 recovery model 改回 full

note: 原本就是 SIMPLE 者幾乎不會有 ldf 過大問題 此模式下 checkpoint 時就會觸發 truncate log，也就是標註 VLF 為可用 但也因此無 log 可備份，無法使用 log 做還原

2. Always On DB

加入 AG 的 DB 必須為 recovery model FULL 在 FULL 模式下，一定要做 ldf 備份才會觸發 truncate log

SELECT name, log_reuse_wait_desc FROM sys.databases where name = '<dbnam>'

-- NUL是虛擬的空設備，寫入到NUL設備的資料將被丟棄
-- TO DISK = 'NUL' 執行備份作業，但不產生實體備份檔案，因此不能拿來還原，適用於非正式
BACKUP LOG [<dbnam>] TO DISK = 'NUL' WITH NOFORMAT, NOINIT, STATS =1

SELECT name FROM sys.database_files WHERE type_desc = 'LOG'

DBCC SHRINKFILE( N'db_log_logical_nam', filesizeMb )

NOTE truncate log 是標註 VLF 為可用 truncate table 是刪除表格資料 dbcc shrinkfile truncateonly 選項是刪除尾部可用 VLF

有時候還沒做 log backup 就發現 log 實際使用量有下降， 這是因為交易在執行時，MSSQL 有預留 rollback 需使用的 log 量， 交易結束後會還回來

-- fn_dblog 查看交易紀錄內容
-- 此為 microsoft 未公開的 func，可隨時異動，不宜用在正式環境
-- 只能查看上一筆交易或還 active 的交易紀錄，用來看大概，計算會不準確
 SELECT
   [transaction ID],
   allocunitname,
   [Current LSN],
   operation [MINIMALLY LOGGED OPERATION],
   context, 
   [log record fixed length]+[log record length] as log_bytes, 
   AllocUnitId
 FROM fn_dblog(null, null)
 WHERE allocunitname like '%TarTable%'
 ORDER BY [Log Record Length] DESC;

--以下兩法紀錄 log 使用量會受到預留 rollback log 空間影響
--1.
select * into #TmpLOGSPACE from sys.dm_db_log_stats(DB_ID())
--2.
create table #TmpLOGSPACE(
  DatabaseName varchar(100)
  , LOGSIZE_MB decimal(18, 9)
  , LOGSPACE_USED decimal(18, 9)
  , LOGSTATUS decimal(18, 9)) 
insert #TmpLOGSPACE(DatabaseName, LOGSIZE_MB, LOGSPACE_USED, LOGSTATUS) 
EXEC('DBCC SQLPERF(LOGSPACE);')

[embed]fn_dblog()和fn_full_dblog()的使用 - abce - 博客园 SQL Server 2017中，函数fn_full_dblog()对函数fn_dblog()做了改进，允许用户读取事务日志中的活跃部分，检索对数据库修改活动的相关信息。 使用fn_dblog()要记住以下几点：…www.cnblogs.com

3.Tools

exec master.dbo.xp_fixeddrives  -- 本機各磁碟剩餘空間
DBCC SQLPERF(LOGSPACE)  -- log 佔用空間
DBCC LOGINFO(dbnam)  -- VLF
DBCC OPENTRAN  -- 最早使用中的交易

Note: 很有可能無法一次降至目標大小，尤其在還有交易時 可以多備份幾次，等 log_reuse_wait_desc = nothig 再壓縮

狀況

ldf 所在硬碟已被撐爆，無法備份與壓縮

BACKUP DATABASE [WMS_CAL] TO DISK = N'NUL' WITH NOFORMAT, NOINIT
/*
BACKUP DATABASE 正在異常結束。 訊息 9002，層級 17，狀態 2，行 1 
因為發生 'LOG_BACKUP'，而且扣留 lsn 為 (5114:172:2)，
所以資料庫 'WMS_CAL' 的交易記錄已滿。*/

• SIMPLE RECOVERY MODEL : CHECKPOINT 即可截斷 log
• FULL RECOVERY MODEL : 必須要 log 備份才能截斷 log
• truncate log : 釋放 VLF， ldf 中被已 flush 的相關 LSN 空間標示為可回收利用

雖然 TO DISK = N'NUL' 即使不會真正寫檔案， 但依然會寫begin、metadata、end 等 LSN， 因此出現 log 已滿而無法備份的狀況

緊急處理方式

1. 熱加該硬碟空間，最無風險
2. 更改 ldf 位置設定到更大硬碟，停服務後移檔
3. 切成 SIMPLE RECOVERY MODEL (失去一段交易備份可用):

切換後自動會CHECKPOINT 此模式下會截斷 log ，應可繼續交易， CHECKPOINT 幾乎只產生一條 LSN，不會因容量滿而不能執行， 進一步 SHRINK ldf 騰出硬碟空間給其他 DB 的 ldf 用

注意若有 long tx 會讓 truncate log 效果差， 也會發現 shrink 不下來，需處理如 kill 該session

Sup.

ldf為 MSSQL 交易紀錄實體檔，由多個邏輯 VLF 組成， 藉由 ldf 檔案備份的 TRUNCATE 功能循環利用

ldf 檔案備份 TRUNCATE : 將 ldf 中已被 flush 的相關 LSN 空間，標為可回收利用，釋放 VLF 只有在最舊活躍LSN(minLSN) 前的 VLF 可回收

在 Recovery Model = Simple 時， MSSQL 在自動執行 CHECKPOINT 後觸發 truncate ， 將舊的 VLF 清空使空間能夠循環利用

在 Recovery Model = Full 時， MSSQL 為了確保 PITR 功能， 只有在執行 log backup 才觸發 truncate 若一直忘記 log backup 則 LDF 檔案會擴張，增加更多 VLF

若已經肥到硬碟上限，則交易會開始失敗，此時要瘦身了 這邊即使做 log backup (truncate) 只能清理檔案內容，循環利用 但分配給 LDF 的空間還是不變

若想要將 LDF 恢復成原來的大小，要用 SHRINK SHRINK 機制是從最尾端的 reusable VLF 開始刪除， 1.直到指定大小或 2.遇到使用中的 VLF

過多的 VLF 數量會影響 DB restore 與啟動(online) 速度，建議是在 100 個

AG 或 複寫 節點間同步未完成，即使 log 備份後一樣不能 shrink DBCC LOGINFO 的 status 不會是 0 ( 0 表示 reusable)，要變 0 須滿足:

1. VLF 中的交易都已提交或回滾
2. VLF 已經備份
3. 所有節點已收到且 redo log

NO_TRUNCATE

當執行資料庫還原時，系統會要求做 tail log backup，將 ldf 更到最新狀況， 此時有 NO_TRUNCATE 選項，目的是即使 DB offline 或 suspect 一樣可以備份

• MSSQL truncate log 前會檢查那些 VLF 中的所有TXID 已 commit，決定該 VLF 是否可以標註回可回收使用
• DB 異常連檢查都不行時，tail log 備份就會錯誤(因為他會做 truncate)
• NO_TRUNCATE就是備份但跳過 truncate log
• 只要 transaction log 本身還能讀取，它就會把 log 的內容完整備份出來。

Reference

Microsoft learn

the will will web

SQL worker

德瑞克 SQL Server 學習筆記