No Money No Honey

最近在看SQL備份的方式，找到了這篇文章，怕有些文章會消失，固完整轉貼保存，並標明出處：
http://sharedderrick.blogspot.com/2009/03/transaction-log-for-database-is-full.html

 

交易記錄檔已滿、爆掉；截斷交易記錄檔。（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

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

遇到的錯誤訊息：

view plaincopy to clipboardprint?

1. 訊息 9002，層級 17，狀態 2，行 2 
2. 資料庫 ‘db1’ 的交易記錄已滿。如果要了解為何無法重複使用記錄中的空間，請參閱 sys.databases 中的 log_reuse_wait_desc 資料行。 
4. Msg 9002, Level 17, State 2, Line 2 
5. 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 

訊息 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

請參考下圖所示：

---

若使用的版本是 SQL Server 2000、2005、2008：

可以使用 SSMS 圖型介面管理工具，直接截斷、清空交易記錄檔的內容。流程如下：

將資料庫切換為「簡單復原模式」，便會自動截斷、清空交易記錄。

若決定要備份交易記錄檔(*.ldf)，請再切換回「完整復原模式」。

步驟如下：

步驟01. 執行SSMS，在「物件總管」，展開「資料庫」，選擇指定的資料庫，滑鼠右鍵，選擇：「屬性」。

步驟02. 在「資料庫屬性」視窗，在左邊「選取頁面」頁籤，點選：「選項」 。

步驟03. 在右邊的「復原模式」方塊，將原來設定的「完整」，改選取為：「簡單」。 請參考下圖所示：

步驟04. 點選「確定」，完成動截斷、清空交易記錄。

步驟05. 重複執行步驟01~04，但將此資料庫調整為「完整復原模式」。

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

view plaincopy to clipboardprint?

1. /* 
2. 當資料庫使用簡單復原模式時，便會自動截斷交易記錄。 
3. 如果您必須從資料庫中移除記錄備份鏈結，請切換到簡單復原模式。 
4. */ 
5. –01 將資料庫 Northwind 切換為「簡單復原模式」，便會自動截斷交易記錄。
6. ALTER DATABASE Northwind 
7. SET RECOVERY SIMPLE 
8. GO 
10. –02 若決定要備份交易記錄檔(*.ldf)，再將資料庫 Northwind 切換回「完整復原模式」
11. ALTER DATABASE Northwind 
12. SET RECOVERY FULL
13. GO 

/*
當資料庫使用簡單復原模式時，便會自動截斷交易記錄。
如果您必須從資料庫中移除記錄備份鏈結，請切換到簡單復原模式。
*/
--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 已經不再支援以下的寫法：

view plaincopy to clipboardprint?

1. –01 使用 BACKUP LOG … WITH NO_LOG
2. BACKUP LOG Northwind 
3. WITH NO_LOG 
4. GO 
6. –02 或是，使用 BACKUP LOG … WITH TRUNCATE_ONLY
7. BACKUP LOG Northwind 
8. WITH TRUNCATE_ONLY 
9. GO 

--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 上執行所產生的錯誤訊息：

view plaincopy to clipboardprint?

1. –01 在SQL Server 2008上，使用 BACKUP LOG … WITH NO_LOG，遇到的錯誤訊息：
2. BACKUP LOG Northwind 
3. WITH NO_LOG 
4. /* 
5. 訊息 3032，層級 16，狀態 2，行 1 
6. 這個陳述式不支援一或多個選項 (no_log)。請查閱文件集，了解支援的選項。 
7. */ 
9. –01 在SQL Server 2008上，使用 BACKUP LOG … WITH TRUNCATE_ONLY，遇到的錯誤訊息：
10. BACKUP LOG Northwind 
11. WITH TRUNCATE_ONLY 
12. GO 
14. /* 
15. 訊息 155，層級 15，狀態 1，行 3 
16. ‘TRUNCATE_ONLY’ 不是可辨識的 BACKUP 選項。 
17. */ 

--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

張貼者： Derrick Chen 位於 3/22/2009 11:55:00 上午