2009-03-22

交易記錄檔已滿、爆掉;截斷交易記錄檔。(The transaction log for database 'DB1' is full. ;Transaction Log Truncation)


本文僅討論當發生交易記錄檔已滿、爆掉時的災難處理方式,並未討論如何避免發生交易記錄檔已滿的作法,請參考「管理交易記錄」等相關主題。
http://technet.microsoft.com/zh-tw/library/ms345382.aspx
如何避免 SQL Server 資料庫的交易記錄檔超出預期大小
http://support.microsoft.com/kb/873235/zh-tw

若資料庫因故造成交易記錄檔已滿、爆掉,將導致無法執行新增、刪除等作業。

遇到的錯誤訊息:



訊息 9002,層級 17,狀態 2,行 2
資料庫 'db1' 的交易記錄已滿。如果要了解為何無法重複使用記錄中的空間,請參閱 sys.databases 中的 log_reuse_wait_desc 資料行。

Msg 9002, Level 17, State 2, Line 2
The transaction log for database 'DB1' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases



請參考下圖所示:

01_在查詢編輯器遇到的錯誤,Error9002








若使用的版本是 SQL Server 2000、2005、2008
可以使用 SSMS 圖型介面管理工具,直接截斷、清空交易記錄檔的內容。流程如下:
將資料庫切換為「簡單復原模式」,便會自動截斷、清空交易記錄。
若決定要備份交易記錄檔(*.ldf),請再切換回「完整復原模式」。

步驟如下:
步驟01. 執行SSMS,在「物件總管」,展開「資料庫」,選擇指定的資料庫,滑鼠右鍵,選擇:「屬性」。
步驟02. 在「資料庫屬性」視窗,在左邊「選取頁面」頁籤,點選:「選項」 。
步驟03. 在右邊的「復原模式」方塊,將原來設定的「完整」,改選取為:「簡單」。 請參考下圖所示:

04_切換為「簡單復原模式」

步驟04. 點選「確定」,完成動截斷、清空交易記錄。
步驟05. 重複執行步驟01~04,但將此資料庫調整為「完整復原模式」。

或是利用以下的 Transact-SQL 陳述式:



/*
當資料庫使用簡單復原模式時,便會自動截斷交易記錄。
如果您必須從資料庫中移除記錄備份鏈結,請切換到簡單復原模式。
*/
--01 將資料庫 Northwind 切換為「簡單復原模式」,便會自動截斷交易記錄。
ALTER DATABASE Northwind
SET RECOVERY SIMPLE
GO

--02 若決定要備份交易記錄檔(*.ldf),再將資料庫 Northwind 切換回「完整復原模式」
ALTER DATABASE Northwind
SET RECOVERY FULL
GO



造成無法截斷的原因可能很多,例如:仍有交易在執行中等。所以若您發覺無法截斷交易記錄檔,請再度執行上述的動作或是Transact-SQL 陳述式,或許就可以截斷交易記錄檔。

若執行數次後都無法截斷交易記錄,建議您可以查詢系統檢視 sys.databases 中的 log_reuse_wait_desc 資料行的描述說明,找出為何無法截斷的可能因素。



若使用的版本是 SQL Server 2000、2005,可以任選以下Transact-SQL 陳述式,來截斷、清空交易記錄檔的內容,但是 SQL Server 2008 已經不再支援以下的寫法:



--01 使用 BACKUP LOG ... WITH NO_LOG
BACKUP LOG Northwind
WITH NO_LOG
GO

--02 或是,使用 BACKUP LOG ... WITH TRUNCATE_ONLY
BACKUP LOG Northwind
WITH TRUNCATE_ONLY
GO


BACKUP LOG 陳述式的 NO_LOG 和 TRUNCATE_ONLY 選項會中斷記錄鏈,
因為它們會在沒有建立備份副本情況下,移除記錄的非使用中部分。
在下次進行完整或差異資料庫備份之前,無法保護資料庫免於媒體失敗。

下一版的 Microsoft SQL Server 將不再提供此功能。
請勿在新的開發工作中使用此功能,並且儘速修改使用此功能的應用程式。

以下是使用 BACKUP LOG ... WITH NO_LOG 或 BACKUP LOG ... WITH TRUNCATE_ONLY 在 SQL Server 2008 上執行所產生的錯誤訊息:


--01 在SQL Server 2008上,使用 BACKUP LOG ... WITH NO_LOG,遇到的錯誤訊息:
BACKUP LOG Northwind
WITH NO_LOG
/*
訊息 3032,層級 16,狀態 2,行 1
這個陳述式不支援一或多個選項 (no_log)。請查閱文件集,了解支援的選項。
*/

--01 在SQL Server 2008上,使用 BACKUP LOG ... WITH TRUNCATE_ONLY,遇到的錯誤訊息:
BACKUP LOG Northwind
WITH TRUNCATE_ONLY
GO

/*
訊息 155,層級 15,狀態 1,行 3
'TRUNCATE_ONLY' 不是可辨識的 BACKUP 選項。
*/

參考資料:
管理交易記錄
http://technet.microsoft.com/zh-tw/library/ms345382.aspx

SQL Server 2008 中已停止的 Database Engine 功能
http://msdn.microsoft.com/zh-tw/library/ms144262.aspx

簡單復原模式下的備份
http://technet.microsoft.com/zh-tw/library/ms191164.aspx

交易記錄截斷
http://technet.microsoft.com/zh-tw/library/ms189085.aspx


如何避免 SQL Server 資料庫的交易記錄檔超出預期大小
http://support.microsoft.com/kb/873235/zh-tw

5 則留言:

  1. 德瑞克先生您好

    時常在找尋SQL server問題時,拜讀您的大作而得到解答
    非常感謝~
    最近我遇到了相當棘手困擾的問題
    就是,我公司以SQL server 2005 EXPRESS開發了一套軟體
    我現在要負責包裝成安裝程式(使用Installschield 2008)
    基本的SQL EXPRESS安裝由Visual Studio包裝的安裝檔作掉了
    所以我的安裝程式要做的事情就是,
    1. 設定自動啟動SQL browser
    2. 啟用Name pipe及TCP,且TCP port皆改為 1433
    3. 安全性改為 windows與SQLserver驗證模式
    4. 重新啟動SQL EXPRESS
    5. 附加上我們自己的資料庫
    6. 產生該資料庫所需使用的login與User
    7. 修改該User相關權限
    以上這些事情. 1-6都順利完成了..
    唯獨7...
    我在XP sp2環境下執行正常, 到了VISTA home..就不行了 ... ><..
    而且現在連下載的SQL Server Management Studio Express SP2 在VISTA Home都安裝不起來...
    安裝過程不會有錯誤. 但是安裝結束後
    連組態工具都沒有 ...
    找過很多文章都沒有提到相關解決辦法...
    不曉得您有沒有遇過這樣的問題? ...

    回覆刪除
  2. 很難確認您遇到的問題之原因,若是無法安裝SQL Server Management Studio Express SP2,或許可以先試試看:
    SQL Server Management Studio Express Service Pack 3
    http://www.microsoft.com/downloads/details.aspx?displaylang=zh-tw&FamilyID=5d76230d-580d-4874-8c7d-93491a29db15

    希望對您有幫助...^_^

    回覆刪除
  3. 網誌管理員已經移除這則留言。

    回覆刪除
  4. 德瑞克你好
    想請問一下使用 BACKUP LOG的方式是一定要用下指令的方式去執行嗎?
    如我設定排程就是要指令中去寫語法這樣嗎?

    是否使用備份記錄檔後也會自動做 BACKUP LOG的動作呢

    謝謝

    回覆刪除
  5. Hi:土豆的家,您好,
    關於您的問題,整理如下:
    Q1. 想請問一下使用 BACKUP LOG的方式是一定要用下指令的方式去執行嗎?如我設定排程就是要指令中去寫語法這樣嗎?
    Ans. 可以使用 SSMS 管理工具執行備份交易記錄檔。若是要搭配作業(Job)內的排程來執行,語法就是 BACKUP LOG 。

    Q2. 是否使用備份記錄檔後也會自動做 BACKUP LOG的動作呢
    Ans. 您所指的「備份記錄檔」,在 SQL Server 內應該是指BACKUP LOG的動作。

    謝謝。

    回覆刪除