SQL Server 的交易記錄意外增大或充滿的處理方法

交易記錄檔Transaction Log File是用來記錄資料庫更新情況的檔案，副檔名為ldf。
在 SQL Server 7.0 和 SQL Server 2000 中，如果設定了自動成長功能，交易記錄檔將會自動擴充。
一般情況下，在能夠容納兩次交易記錄截斷之間發生的最大數量的事務時，交易記錄的大小是穩定的，交易記錄截斷由檢查點或者交易記錄備份觸發。
然而，在某些情況下，交易記錄可能會變得非常大，以致用盡空間或變滿。通常，在交易記錄檔佔盡可用磁碟空間且不能再擴充時，您將收到如下錯誤訊息：
Error:9002, Severity:17, State:2
The log file for database '%.*ls' is full.
除了出現此錯誤訊息之外，SQL Server 還可能因為缺少交易記錄擴充空間而將資料庫標記為 SUSPECT。有關如何從此情形中恢複的其他資訊，請參見 SQL Server 線上說明中的“磁碟空間不足”主題。

另外，交易記錄擴充可能導致下列情形： 
· 非常大的交易記錄檔。 
· 事務可能會失敗並可能開始復原。 
· 事務可能會用很長時間才能完成。 
· 可能發生效能問題。 
· 可能發生阻塞現象。

原因
交易記錄擴充可能由於以下原因或情形而發生： 
· 未提交的事務 
· 非常大的事務 
· 操作：DBCC DBREINDEX 和 CREATE INDEX 
· 在從交易記錄備份還原時 
· 用戶端應用程式不處理所有結果 
· 查詢在交易記錄完成擴充之前逾時，您收到假的“Log Full”錯誤訊息 
· 未複製的事務

解決方案
記錄檔滿而造成SQL資料庫無法寫入檔案時，可用兩種方法：
一種方法：清空日誌。
1．開啟查詢分析器，輸入命令
DUMP TRANSACTION 資料庫名 WITH NO_LOG
2.再開啟企業管理器--右鍵你要壓縮的資料庫--所有任務--收縮資料庫--收縮檔案--選擇記錄檔--在收縮方式裡選擇收縮至XXM,這裡會給出一個允許收縮到的最小M數,直接輸入這個數,確定就可以了。

另一種方法有一定的風險性，因為SQL SERVER的記錄檔不是即時寫入資料庫主檔案的，如處理不當，會造成資料的損失。
1: 刪除LOG
分離資料庫 企業管理器－＞伺服器－＞資料庫－＞右鍵－＞分離資料庫
2：刪除LOG檔案
附加資料庫 企業管理器－＞伺服器－＞資料庫－＞右鍵－＞附加資料庫
此法產生新的LOG，大小隻有500多K。

注意：建議使用第一種方法。

如果以後,不想要它變大。
SQL2000下使用：
在資料庫上點右鍵->屬性->選項->故障恢複-模型-選擇-簡單模型。
或用SQL語句：
alter database 資料庫名 set recovery simple

另外，如中資料庫屬性有兩個選項，與交易記錄的增長有關：
Truncate log on checkpoint
(此選項用於SQL7.0，SQL 2000中即故障恢複模型選擇為簡單模型)
當執行CHECKPOINT 命令時如果交易記錄檔超過其大小的70% 則將其內容清除在開發資料庫時時常將此選項設定為True
Auto shrink
定 期對資料庫進行檢查當資料庫檔案或記錄檔的未用空間超過其大小的25%時，系統將會自動縮減檔案使其未用空間等於25% 當檔案大小沒有超過其建立時的初始大小時不會縮減檔案縮減後的檔案也必須大於或等於其初始大小對交易記錄檔的縮減只有在對其作備份時或將 Truncate log on checkpoint 選項設為True 時才能進行。

注意：一般立成建立的資料庫預設屬性已設好，但碰到意外情況使資料庫屬性被更改，請使用者清空日誌後，檢查資料庫的以上屬性，以防交易記錄再次充滿。

 

摘自: http://www.bokesun.com/blogger/laobai/archives/2009/70717.shtml

 

P.S. 

The shrinking of  TL process is changed in SQL Server 2008. You just need to

(a) set the database to simple recovery model

(b) Shirnk the file

(c) Set back to Full (If required). ie. If you are not taking TL backup let it be in Simple recovery model only