[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檔不會無限制的過度膨脹。

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

Popular posts from this blog

[SQL SERVER] 找出LOCK方法懶人包

[開箱] Dell P2415Q 4K螢幕開箱