看到一篇有關於交易紀錄檔案暴增的文章，算是提供了相當有用的資訊 ，而且這邊不是msdn翻譯機搞出來的詭異中文了。
連結如下：
http://msdn.microsoft.com/zh-tw/library/jj993254.aspx
節錄重點如下：
如何確認交易記錄檔案暴增主因
透過下面語法你大概可以快速掌握造成交易記錄檔案暴增的原因。
SELECT name ,recovery_model_desc ,log_reuse_wait_desc FROM sys.databases WHERE name ='ricotest1'
log_reuse_wait_desc 這欄位會反映目前交易記錄檔案，因為什麼因素造成無法截斷非活動紀錄，導致無法重用內部空間，進而造成交易記錄檔案持續成長，更詳細說明可參考可能會延遲記錄截斷的因素，下面我將依序介紹幾個常見原因。
沒有備份交易記錄檔案的影響
如果你查看 log_reuse_wait_desc欄位顯示Log Backup，那就表示交易紀錄檔案過大，而這也是最常見的原因，就是沒有執行交易紀錄檔案備份。我常看到很多資料庫都有完善的完整資料庫備份計畫，但是卻沒有任何交易記錄檔案備份計畫，因為大部分的DBA都以為完整備份已包含交易記錄檔案備份，但事實上，資料庫完整備份根本不包含交易記錄檔案備份，換句話說，沒有執行交易紀錄檔案備份，所以無法自動截斷記錄，並且也無法重用交易記錄檔案空間，所以交易記錄檔案只好持續成長。要解決過大的交易紀錄檔案，基本上要依企業可容許資料遺失風險來規劃交易紀錄檔案備份頻率，如每30分鐘執行交易記錄檔案備份(允許30分鐘資料遺失風險)，但如果你無法改變備份頻率的話，那麼請一開始就建立較大的交易紀錄檔案(最小化VLF數量)，並且設定自動成長，而成長大小請固定一個數值，我個人建議不要超過1.2GB，這是因為如果成長過大相對所需處理時間也拉長，如果剛好遇到交易紀錄檔案成長不夠快速，資料庫將可能遇到9002錯誤。
索引維護作業的影響
我相信大部分DBA都知道重建索引所帶來的好處，但很少人知道重建索引會為資料庫帶來什麼樣的壞處，最明顯的壞處就是交易紀錄檔案快速成長。大多數人執行重建索引都是在資料庫完整復原模式下進行(大部分正式環境資料庫都是完整復原模式)，在該模式下重建索引需要很大的交易紀錄檔案空間，而且重建索引大部分都在一個交易中處理，這表示重建索引作業是一個長時間交易作業，並且會產生很多密集交易活動，除非你很確定該索引破碎程度非常大並且已經對資料庫查詢效能影響夠大，那麼執行重建索引在所難免，但如果對資料庫查詢效能影響幅度不大或碎片幅度小的話，那麼請使用重新組織索引(Index Reorganization)來取代重建索引(Index Rebuild)操作。不管資料庫處於什麼模式下，重新組織索引所需要的交易紀錄檔案空間永遠比重建索引來的少，舉一個簡單例子來說，假設針對一個10GB索引執行重建，那麼可能需要該索引大小*1.5的交易紀錄檔案空間(15GB)才能完成該作業，但如果重新組織一個10GB索引，那只需要小於10GB交易紀錄檔案空間就可以完成，這是因為重新組織索引不會分配新頁面(page)，所以不會發生頁面分割，而且重新組織索引也使用較少的CPU和系統資源，下圖是微軟建議索引碎片臨界值，但我個人認為請依資料庫環境判斷較佳，最重要是只保留真正有用索引，刪除無用索引。
長時間交易的影響
如果你查看 log_reuse_wait_desc欄位顯示Active_Transaction，那就表示交易紀錄檔案因為長時間交易而持續成長中。交易記錄檔案內包含了資料庫詳細的資料操作過程和一些資料庫物件，所以不管任何資料操作都會產生交易記錄，而當一個交易被建立後，只有收到commit或rollback命令後，該交易作業才會真正結束。如果交易作業處理資料量龐大的話，那麼產生大量的交易記錄將無可避免，如果該資料表恰巧有FK或Trigger，那麼交易紀錄檔案成長量將更大，所以我們應該要盡量去最小化這些交易記錄活動，例如使用partition table來執行資料轉移作業(switch partition)，或是將資料庫復原模式切換成Simple後，在執行資料轉移作業，又或是縮小每次交易作業的資料大小，下面我簡單測試長時間交易(處理大資料量)所造成的影響。
形成長時間交易有幾個原因
- *應用程式沒有正確處理commit或rollback
- *應用程式逾時造成交易沒有commit
- *每一次交易作業處理資料量過於龐大
建立交易記錄檔初始大小=60MB 成長量=10MB，RecoveryMode=Full
頁面分割的影響
頁面分割作業是一項相當耗費資源的作業，會造成額外的I/O和空間浪費，但很多人可能不知道，頁面分割也會造成交易紀錄檔案快速成長，下面我簡單測試頁面分割將付出更多的交易紀錄檔案空間。
雖然我幾乎快貼了整篇文章，不過他提供的觀念真是很重要呢！
