[SQL Server] 解決log檔(ldf file)過度膨脹的實戰經驗

背景：
公司最近把一套每天有相對大量交易(之前公司更大很多很多倍)的系統轉移到SQL Server去，不到一個月交易檔(ldf)已經貼近數據檔(mdf)的size，真的好可怕啊。

身為SQL Server的DBA當然 要替月行道，警惡懲奸 不能讓這種情況繼續下去。

解決方案選擇：

• 第一個我想到的方法是把Database的Recovery model設成Simple，簡單來說就是不需要交易記錄，對於Insert Delete Update 很少的系統勉強還說得過去，不過對於交易量大的系統來說就不行了，沒有交易記錄萬一資料庫突然往生，總不能用full backup還原然後要User重新輸入一天的交易吧。
  
• 第二個方法是定時進行備份。為什麼log大小跟備份有關係呢? 簡單來說，資料庫的ldf檔就是用來儲存Full Backup後所發生的所有交易。如果你從今天從來沒有為資料庫進行過備份，理論上ldf檔會無限的膨脹下去，而且利用Shrink指令也無法把交易檔壓縮。因為沒備份的話就等於ldf檔裡面的東西統統有用，當然沒辦法壓縮了。所以要保持交易檔案的size就是要持續保持備份，在每次備份完了以後自動把交易檔Truncate成初始大小，這樣可以長期保持相對小的交易檔。

所以，最後我選擇了方案二。

實作流程考慮因素：
SQL Server的backup model一定要一份full backup再塔配其他備份檔一起使用(SQL Server的備份model解釋在此)，要達到控制ldf檔案大小的目的，備份可以每天只是Full Backup，也可以是Full->T-log，也可以是最複雜Full->Diff->T-log，我考慮使用那一種的因素主要有以下：

• 資料庫本身只是約55GB不是太大；
• 每天交易量不多，每分鐘約10筆交易；
• 使用者允許少量的data loss，一天Data loss肯定不行；
• 資料庫只是辦公時間才會用。
  

我個人認為Backup Plan越簡單越好，發生狀況的時候已經很緊張，複雜的Backup只會令事情更糟。反正今天硬碟實在是太便宜，天天備份幾次也無所謂，因此在儘量簡化備份流程的前題之下，小弟傾向每天Full BackUp一次，每小時備份一次T-log，就是Full->T-log的塔配。

實現步驟：
好吧，根據之前實作流程的結論動手做看看吧。要自動化整個備份工作，SQL Server 2008以後有一個很好用的工具叫Maintainence Plan，可以把需要routie的日常維護的工作放到這裡。

1. 首先用有sysadmin權限的帳號登入，然後到Management-> Maintenance Plan按右鍵-> New Maintainance Plan
   
   
   
2. 輸入Maintenance Plan的名字，例如 XXX Server Backup Plan，按OK。
   
   
   
3. 接下來會看到一個空白的Maintenance Plan，如上面所述我們需要每天Full Backup一次，然後每小時來一個T-log的備份，所以Maintenance Plan裡應該有兩項的Subplan，一個是負責Full Backup，另一個是負責T-log Backup。
   
   
   
4. 先設定Full Backup。先點一下選取Subplan_1，然後在左邊的Toolbox欄拉出一個Back up Database Task，在拉出的Backup元件中按右鍵-> Edit
   
   
   
   
   
5. 備份的位置按公司的規定，小弟公司習慣放回資料庫裡。Compression選項就要看實際情況了，我習慣把備份壓縮，因為小弟公司不是24小時運作，淩晨的時候資料庫反正也很閒，省個空間也不錯....不過要記得如果Full Backup是選擇壓縮的話，Diff或者T-log備份一定要一樣選擇壓縮，不然的話小弟試過最後合不起來，然後....(下刪一萬字悲情文)
   
   
   
   
   
6. 接下來是設定Full Backup的運作時間，按一下Sub_plan1右邊的小icon，進入到Properties畫面。
   
   
7. 回憶一下，我們的Full backup是每天non Office Hour一次。所以Schedule type選擇Recurring，Frequency選擇Daily，時間設在每天淩晨12點然後按OK，如下圖。
   
   
   
   
   
   
8. 設好FullBackup了以後重覆步驟設定T-Log備份。開一個新的Subplan，設好每小時跑一次，要比較注意的是BackUp Database Task中BackUp Type要選Transaction Log、If backup files exist選Append。為什麼是Append呢? 因為像我這個案例是每小時備份一次，如果每次也Overwrite的話T-log的備份永遠只有最近的那一小時，結果當然是T-log備份無法跟Full backup結合在一起，因為根本就不完整啊(哀)~!!!，不過話說回來，如果純是為了壓縮ldf檔的話選擇Overwrite也是OK的，不過小弟要弄的資料庫一小時才幾MB T-Log，幹嘛不順便備份一下呢? 最後記得要多新增一個Maintenance Cleanup Task每天把這個T-log刪掉，不然這個T-log的備份也會像通膨一樣無限的變大
   
   
   
9. 全部設好以後按save. Plan就會按你定的時間開始跑了，確定你的備份有按時進行並且無誤(可以把Full Backup及T-log backup 還原到testing environment看看)以後，可以為database進行Shrink動作，T-log應該會回到原始大小了。定時監控一下ldf檔案應該不會再發現爆大的情況了...

總結：

T-log ldf檔案過大除了浪費空間，還會直接影響系統的效能(想像一下每次要把資料寫進十幾GB的檔案)，所以定時清理是必須的，可透過定時的備份把沒用的T-log清理掉，來保持T-log ldf檔不會無限制的過度膨脹。

如發現小弟任何觀念錯了歡迎提出喔^口^