資料庫交易紀錄的備份、壓縮與刪除

這個標題其實有點問題,但懂資料庫的人就知道,交易紀錄會暴增,就是因為主資料庫檔案一直不斷的被存取所致(新增、修改、刪除),資料庫為了保護整個資料的完整性,所以會將這些動作原封不動的逐一記錄下來,這就是交易紀錄的原型了。

因此,交易紀錄基本上是無法被刪除的(除非你透過不正常的手段),或者話應該是說,備份做好了交易紀錄他自然就消失。所以當我們備份好資料庫,接著壓縮交易紀錄檔,交易紀錄自然就縮小到可忽略的程度了。

資料庫交易紀錄的備份與壓縮

首先你要先查詢好你的「資料庫桶子名稱」,以及「你的資料庫桶子之記錄檔名稱」。查詢的方式很簡單,只要在資料庫桶子上面按下右鍵選擇屬性,這樣就可以看到資料庫屬性了。如下圖所示:

指令碼如下:

BACKUP LOG 你的資料庫桶子名稱 WITH TRUNCATE_ONLY
DBCC SHRINKFILE(你的資料庫桶子之記錄檔名稱)

SHRINKFILE其實後面有延伸參數可以下,坊間都習慣來個DBCC SHRINKFILE(你的資料庫桶子之記錄檔名稱, 2)之類的,我個人認為沒有其必要性,用預設的就好了。

Update: Microsoft SQL Server 2008之後的作法

如果你是在Microsoft SQL Server 2008(含)版本以上的資料庫進行上述指令,將會引發下列錯誤訊息:

'TRUNCATE_ONLY' 不是可辨識的 BACKUP 選項。

原因是因為從Microsoft SQL Server 2008開始,在也不支援這樣子拋棄交易紀錄檔的寫法,而是統一改採用將復原模式改為「簡單」的方式來處理。(亦即一旦你設定了這個模式後,就再也無法使用任何依存交易紀錄的功能了,例如資料庫還原!)

這裡介紹如何使用SSMS介面式的管理,來達到這樣的設定。

  1. 在資料庫桶子上,按下右鍵選擇「屬性」。
  2. 在左邊選取頁面頁籤中,選擇「選項」。
  3. 看向右邊視窗的「復原模式」選項,將原來設定的「完整」改選取為「簡單」。

亦可以使用T-SQL的方式來進行設定:

--將資料庫切換為「簡單復原模式」,以利停止交易記錄
ALTER DATABASE 資料庫桶子名稱
SET RECOVERY SIMPLE
GO
Microsoft SqlServer TransactionLog Delete Remove Shrink