[SQL SERVER][Maintain]管理交易記錄檔(3)
這是該系列第三篇，
會寫這個系列是因為某天某位朋友向我請教如何管理交易記錄檔，
當時因為工作還在忙，所以我只對他提了大概方向，
外加自己也經常處理肥大交易記錄檔問題，
所以心想不如把自己以前處理交易記錄檔的經驗寫下來。
該系列文章都是自己在處理交易記錄檔吃過的虧和一些心得分享，
所以文章內容大部分都是自己的見解，如果有那些錯誤內容，煩情告知我一下。
第一篇介紹長時間交易對交易記錄檔的影響。
第二篇介紹如何緊急處理交易記錄檔吃光硬碟空間災難(沒有其他storage情況下)。
第三篇介紹如何快速確認造成交易記錄檔暴增主因。
每當我收到交易記錄暴增問題時，往往第一步就是要確認是什麼原因所造成，
透過下面語法你大概可以快速掌握兇手候選名單。
SELECT name ,
recovery_model_desc ,
log_reuse_wait_desc
FROM sys.databases
WHERE name ='ricotest1'
log_reuse_wait_desc 這欄位會指出目前交易記錄檔，
是什麼原因造成無法截斷非活動紀錄，導致無法重用內部空間，
下面我大概簡單說明我常見的原因和處理方法。
CHECKPOINT
如果你看到顯示Checkpoint，
就說明交易記錄檔大小尚未超過VLF起始大小，
所以還未觸發checkpoint來截斷交易記錄檔，
至於為什麼沒有觸發checkpoint最主要原因，
往往都是因為長時間交易活動所造成，
這時可以手動執行checkpoint並備份交易記錄檔，
同時找出那些未提交交易真正原因(存在孤兒交易)。
LOG_BACKUP
這是我最常見的原因，大多正式資料庫都使用完整復原模式，
雖然有執行資料庫完整備份，但往往都沒執行交易記錄檔備份，
由於完整復原模式下，沒有備份交易記錄檔，
導致不會自動截斷交易記錄檔，
所以無法重用交易記錄檔案內部空間。
ACTIVE_TRANSACTION
這也是很常見的原因，
長時間的交易或未提交的交易都將造成交易記錄檔暴增，
當你執行備份和checkpoint都無法截斷活動中的交易，
而且活動中的交易需要額外的空間來存放復原交易記錄，
這是SQL SERVER要確認該交易可以正常Rollback所必要的考量。
參考[SQL SERVER][Maintain]管理交易記錄檔(1)
ACTIVE_BACKUP_OR_RESTORE
表示資料庫正在執行長時間的完整或差異備份，
造成交易記錄檔空間無法被重複使用(備份其間不會截斷交易記錄檔)，
而這原因大部分是因為沒有善用差異備份為主要兇手，
很多人備份策略中只存在完整備份，又或者只有一個完整備份，
導致差異備份處理過久(差異備份只會和上一個完整備份做比對)，
因為長時間備份處理，造成交易記錄檔延遲截斷，
這時你須優化改善備份作業或調整備份策略，
甚至查看相關I/O Latency是否為主要殺手。
DATABASE_MIRRORING
表示同步資料庫鏡像操作可能是影響交易記錄檔暴增的主因。
Mirroring環境中，主體只會傳送已經提交的交易到鏡像資料庫，
如果這中間連線相當緩慢或是發生suupended，
那麼主體將會產生相當大的交易數量，
這將造成交易記錄檔空間無法重複使用，
直到這些紀錄被傳送到鏡像資料庫後才可解決。
一開始我會先確認主體和鏡像資料庫連線同步是否正常，
但我大部分遇到的是endpoint認證過期或兩台server使用帳戶和密碼沒有一致(not join domain)，
還有另一個就是主體交易數量過於龐大且頻繁，來不及傳送到鏡像資料庫，
解決這樣的問題最簡單最快就是使用SQL 2008所提供的壓縮特性，
不然就得一一調整這些交易量大小和頻率了。
REPLICATION & CDC
由於Log reader agent會讀取交易記錄檔，確認有那些紀錄改變並將這些記錄傳送到訂閱端，
所以Log reader agent過慢或長時間等待都將造成交易記錄檔暴增，
直到這些紀錄被標示為以複寫才能解決。
而這樣的情況也會發生在有啟用CDC功能的資料庫中，
對於Replication環境來說，處理這樣的問題似乎沒有一個快速又完善的方法(對我來說)，
通常第一步我會先確認Log reader SQL Agent jobs是否有正常執行，
如果沒有的話，就要找出為什麼沒有執行的主因(透過複寫監視器和複寫紀錄)，
其次還有因為交易記錄檔所存放的I/O效能和吞吐量所導致(連續讀取和寫入所造成的資源競爭)，
不過如我之前所說，對於Replication環境來說似乎沒有一個快速又完善的解決方法，
但我會建議你看看 複寫的如何主題，這有助你快速排除Replication環境相關問題，
至於針對CDC我大部份會先採取手動同步資料後，在詳細追查發生的真正原因。
參考