文章編號: 317375 - 上次校閱: 2011年5月10日 - 版次: 7.0

執行 SQL Server 之電腦上的交易記錄檔意外地擴充或滿溢

檢視此文章適用的產品。
系統提示本文適用於您使用的作業系統之外的作業系統。與您不相關的文章內容已停用。

在此頁中

全部展開 | 全部摺疊

結論

在 SQL Server 7.0、SQL Server 2000 與 SQL Server 2005 中，使用自動成長設定，便可讓交易記錄檔自動擴充。

一般而言，檢查點或交易記錄備份會觸發交易記錄截斷，如果交易記錄檔能保留其間發生的交易最大數目，那麼檔案大小就會十分固定。

但是，在某些情況下，交易記錄可能會變得非常大，而導致空間不足或是滿溢。一般而言，當交易記錄檔佔據可用的硬碟空間而且無法繼續擴充時，您就會收到下列錯誤訊息：
Error:9002, Severity:17, State:2
The log file for database '%.*ls' is full. (錯誤：9002，嚴重性：17，狀態：資料庫 '%.*ls' 的記錄檔已滿)
如果您使用 SQL Server 2005，可能會收到類似下列的錯誤訊息：
Error:9002, Severity:17, State:2
The transaction log for database '%.*ls' 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 資料庫 %.*ls 的交易記錄檔已滿。如果要瞭解為何無法重複使用記錄檔中的空間，請參閱 sys.databases 中的 log_reuse_wait_desc 欄位。)
除了這個錯誤訊息以外，因為交易記錄空間不足無法繼續擴充，所以 SQL Server 可能會將資料庫標記為可疑。如需如何修復這個情況的詳細資訊，請參閱《SQL Server 線上叢書》的＜磁碟空間不足＞主題。

此外，交易記錄擴充可能會導致下列情況：
  • 大型交易記錄檔。
  • 交易可能會失敗並開始復原。
  • 交易完成可能要花費一段時間。
  • 可能會發生效能問題。
  • 可能會發生封鎖。
回此頁最上方

原因

因為下列的原因或情況，可能會擴充交易記錄檔：

未認可的交易

如果您沒有發出明確的 COMMIT 或 ROLLBACK 命令，則明確交易會維持在未認可的狀態。當應用程式發出 CANCEL 或 Transact SQL KILL 命令，但沒有對應的 ROLLBACK 命令時，最常發生這個問題。發生交易取消的情況，但並未復原；因此，SQL Server 無法截斷這個動作之後的每個交易，因為中止的交易仍在開啟中。您可以使用 DBCC OPENTRAN Transact-SQL 參照，以確定在特定時間中，資料庫中是否有現用交易。 如需有關這個特定案例的詳細資訊，請按一下下列文件編號，檢視「Microsoft 知識庫」中的下列文件：
295108? (http://support.microsoft.com/kb/295108/ ) Incomplete transaction may hold large number of locks and case blocking
171224? (http://support.microsoft.com/kb/171224/ ) INF: Understanding How the Transact-SQL KILL Command Works
此外，請參閱《SQL Server 線上叢書》中的＜DBCC OPENTRAN＞主題。

可能會產生未認可交易的案例：
  • 假設所有錯誤原因都會復原的應用程式設計。
  • 在復原到具名交易或特別巢狀的具名交易時，並不會完全考慮到 SQL Server 行為的應用程式設計。如果您嘗試復原到內部命名的交易，則會收到下列錯誤訊息：
    Server:Msg 6401, Level 16, State 1, Line 13 Cannot roll back InnerTran.(伺服器：Msg 6401，Level 16，State 1，Line 13 無法復原 InnerTran。找不到該名稱的交易或檢查點)
    SQL Server 產生錯誤訊息之後，就會繼續執行下一個陳述式。這是原本設計的作法。如需詳細資訊，請參閱《SQL Server 線上叢書》的＜巢狀交易＞或＜SQL Server 內部＞主題。

    當您設計應用程式時，Microsoft 建議您執行下列動作：
    • 只開啟一個交易單元 (請先考慮另一個程序可能會呼叫您的程序)。
    • 請先檢查 @@TRANCOUNT，然後再發出 COMMIT、ROLLBACK、RETURN 或類似的命令或陳述式。
    • 在撰寫程式碼時，請假設另一個 @@TRANCOUNT 可能會「巢串」您的 @@TRANCOUNT，並規劃在發生錯誤時，復原外部的 @@TRANCOUNT。
    • 檢視交易的檢查點及標記選項(這些動作並不會釋放鎖定！)
    • 執行完整的測試。
  • 允許在交易內部執行使用者動作的應用程式。因為無法截斷開啟的交易且會將新交易增加到開啟交易後的記錄中，因此會導致交易維持開啟狀態一段很長的時間，並造成封鎖及交易記錄擴充。
  • 未檢查 @@TRANCOUNT 以確定沒有開啟交易的應用程式。
  • 網路或其他錯誤，會在未通知 SQL Server 的情形下，逕行關閉連至 SQL Server 的用戶端應用程式連線。
  • 連接共用。建立工作者執行緒之後，如果這些執行緒沒有提供連線的服務，SQL Server 就會予以重新使用。如果使用者連線開始交易，而且在認可或復原交易之前中斷連線，接著之後的連線便會重新使用相同的執行緒，則之前的交易則會仍然維持開啟的狀態。這個情況會導致鎖定 (從之前的交易就保持開啟的狀態)，而且可以避免截斷記錄中的認可交易，這樣就會造成很大的記錄檔大小。如需有關連接共用的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
    164221? (http://support.microsoft.com/kb/164221/ ) INFO: How to Enable Connection Pooling in an ODBC Application

超大型交易

根據每次的交易，截斷交易記錄檔中的記錄。如果交易範圍相當大，除非交易完成，否則都不會將之後開始的交易及任何交易從交易記錄中移除。這可能會造成大型的記錄檔。如果交易夠大，記錄檔可能會用盡可用的硬碟空間，並產生「交易記錄已滿」類型的錯誤訊息，例如「錯誤 9002」。當您收到此類型的錯誤訊息，如需如何處理的詳細資訊，請參閱本文的＜其他相關資訊＞一節。此外，將需要許多時間及 SQL Server 負荷才能復原大型交易。

操作：DBCC DBREINDEX 及 CREATE INDEX

因為 SQL Server 2000 的復原模型產生的變更，所以當您使用完整復原模式並執行 DBCC DBREINDEX，交易記錄擴充的程度會比 SQL Server 7.0 在相同的復原模式中，藉由使用 SELECT INTO 或 BULK COPY，並關閉 "Trunc.Log on chkpt."。

雖然在 DBREINDEX 作業之後的交易記錄大小可能會有問題，但這個方法的確提供了較佳的記錄還原效能。

還原交易記錄檔備份時

這會在下列「Microsoft 知識庫」文件中進行說明：
232196? (http://support.microsoft.com/kb/232196/ ) INF: Log Space Used Appears to Grow After Restoring from Backup

如果您將 SQL Server 2000 設定為使用大量登入模式，並發出 BULK COPY 或 SELECT INTO 陳述式，則在您備份交易記錄時，將會標記每個變更的範圍，並在之後進行備份。雖然這可以讓您在執行大量作業之後，備份交易記錄並從失敗中復原，但這也會增加交易記錄的大小。SQL Server 7.0 不具備這個功能。SQL Server 7.0 只會記錄已變更的程度，但不會記錄實際的程度。因此，雖然在大量登入模式中，記錄作業在 SQL Server 2000 會比在 SQL Server 7.0 佔據較多的空間，但不會大於完整模式所使用的空間。

用戶端應用程式未處理所有結果

如果您向 SQL Server 發出查詢，但未立即處理結果，則可能會保留鎖定並降低伺服器上的並行。

例如，假設您發出的查詢，需要兩頁的資料列才能填寫結果集。SQL Server 會剖析、編譯及執行查詢。這代表會將共用鎖定置放在這兩頁上，其中會包含您必須符合查詢的資料列。此外，假設並非所有資料列都可放入一個 SQL Server TDS 封包 (亦即伺服器與用戶端通訊的方法)。則會填入 TDS 封包並將之傳送到用戶端。如果第一頁的所有資料列都可容納在 TDS 封包中，SQL Server 就會釋放該頁上的共用鎖定，但會保留第二頁的共用鎖定。SQL Server 之後會等候用戶端要求其他資料 (例如，您可以使用 DBNEXTROW/DBRESULTS、SQLNextRow/SQLResults 或 FetchLast/FetchFirst 來執行這項操作)。

這代表在用戶端要求其餘資料之前，共用鎖定都會予以保留。要求第二頁資料的其他處理序可能會被封鎖。

在交易記錄檔完成擴充之前，查詢逾時，且您收到錯誤的「記錄已滿」錯誤訊息

在這種情況下，雖然有足夠的磁碟空間，您仍會收到「空間不足」錯誤訊息。

這種情形在 SQL Server 7.0 和 SQL Server 2000 中會有所不同。

如果交易記錄幾乎滿溢，查詢可能會導致交易記錄自動進行擴充。這會花費額外的時間，而且查詢可能會因此停止，或超過其逾時期間。SQL Server 7.0 在這個情況下會傳回錯誤 9002。這個問題不適用於 SQL Server 2000。

在 SQL Server 2000 中，如果您開啟資料庫的 [自動壓縮] 選項，則會有一小段時間，交易記錄會嘗試自動擴充，但是因為同時間會執行 [自動壓縮] 功能，所以無法進行此項操作。這也可能造成錯誤 9002 的錯誤執行個體。

一般而言，會迅速自動擴充交易記錄檔。但是，在下列情況下，可能會花費比一般更長的時間：
  • 擴充的遞增單位太小。
  • 伺服器因為各種原因而速度緩慢。
  • 硬碟機速度不夠快。

未複寫的交易

如果您使用複寫，便可以擴充 publisher 資料庫的交易記錄檔大小。對已複寫物件造成影響的交易會被標記為「可供複寫」。直到記錄讀取器工作將交易複製到散發資料庫並予以取消標記之前，在檢查點或您備份交易記錄之後，將不會刪除這些交易 (例如未認可交易)。如果記錄讀取器工作發生問題，因而無法讀取 publisher 資料庫中的這些交易，則交易記錄的大小可能會隨著未複寫交易數量的增加而繼續擴充。您可以使用 DBCC OPENTRAN Transact-SQL 參照以辨別最舊的未複寫交易。

如需有關疑難排解未複寫交易的其他資訊，請參閱《SQL Server 線上叢書》的＜sp_replcounters＞和＜sp_repldone＞主題。

如需詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
306769? (http://support.microsoft.com/kb/306769/ ) FIX: Transaction Log of Snapshot Published Database Cannot Be Truncated
240039? (http://support.microsoft.com/kb/240039/ ) FIX: DBCC OPENTRAN Does Not Report Replication Information
198514? (http://support.microsoft.com/kb/198514/ ) FIX: Restore to New Server Causes Transactions to Remain in Log
回此頁最上方

其他相關資訊

會將任何資料庫的交易記錄管理為一組虛擬記錄檔 (VLF)，其大小是 SQL Server 在內部根據記錄檔大小和記錄擴充程度而加以判斷。記錄會持續以整個 VLF 的單位進行擴充，而且只可以壓縮成 VLF 範圍。VLF 存在於三個狀態其中之一：ACTIVE、RECOVERABLE 及 REUSABLE。
  • ACTIVE：記錄的現用部分會從最小序列號 (LSN) 開始 (此序列號代表現用 (未認可) 的交易)。記錄的現用部分則會在最後寫入的 LSN 結束。任何包含現用記錄部分的 VLF 都會被視為現用的 VLF (實體記錄中的未使用空間不是任何 VLF 的一部分)。
  • RECOVERABLE：只有在維護記錄備份序列以進行修復時，才需要最舊現用交易之前的記錄部分。
  • RESUABLE：如果您並未維護交易記錄備份，或是您已經備份記錄，則 SQL Server 會重新使用最舊現用交易之前的 VLF。
當 SQL Server 達到實體記錄檔的結尾，則會藉由將 CIRCLING BACK 作業發到檔案的開頭，開始重新使用實體檔案中的空間。實際上，SQL Server 會針對記錄檔中復原或備份時不再需要的空間，進行重複使用。如果您正在維護備份序列，則在您備份或截斷那些記錄之前，將無法覆寫最小 LSN 之前的記錄部分。在您執行記錄備份之後，SQL Server 可以重新回到檔案的開頭。在 SQL Server 重新開始撰寫記錄檔先前的記錄之後，記錄的可重複使用部分就會位於邏輯記錄的結尾和記錄的現用部分之間。

如需詳細資訊，請參閱《SQL Server 線上叢書》的＜交易記錄實體架構＞主題。此外，您可以在 Inside SQL Server 7.0 的 190 頁 (Soukup, Ron.Inside Microsoft SQL Server 7.0, Microsoft Press, 1999)、以及 Inside SQL Server 2000 的 182 到 186 頁 (Delaney, Kalen.Inside Microsoft SQL Server 2000, Microsoft Press, 2000) 中看到這個主題的絕佳圖表和討論。 SQL Server 7.0 及 SQL Server 2000 資料庫可以進行自動擴充和自動壓縮。您可以使用這些選項協助您壓縮或擴充交易記錄。

如需有關這些選項將如何影響伺服器的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
315512? (http://support.microsoft.com/kb/315512/ ) INF：SQL Server 中的 Autogrow 及 Autoshrink 設定考量
交易記錄檔的截斷和壓縮之間有差異。當 SQL Server 截斷交易記錄檔時，這代表該檔案 (例如已認可的交易) 的內容已經遭到刪除。但是，當您從磁碟空間的觀點 (例如，在 Windows Explorer 中或使用 dir 命令) 來檢視檔案的大小，大小是維持不變的。但是，新的交易目前可以重新使用 .ldf 檔案內部的空間。只有當 SQL Server 壓縮交易記錄檔的大小時，您才可以確實看到記錄檔的實體大小中的變更。

如需有關如何壓縮交易記錄的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
256650? (http://support.microsoft.com/kb/256650/ ) INF：如何將 SQL Server 交易記錄檔壓縮
272318? (http://support.microsoft.com/kb/272318/ ) INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
如需有關 SQL Server 6.5 交易記錄使用率的詳細資訊，請按一下下面的文件編號，檢視「Microsoft 知識庫」中的文件：
110139? (http://support.microsoft.com/kb/110139/ ) INF：SQL 交易記錄檔滿溢的原因
回此頁最上方
這篇文章中的資訊適用於:
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
回此頁最上方
關鍵字：?
kbsqldeveloper kbinfo KB317375
回此頁最上方
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應，不負任何擔保責任。Microsoft及(或)其供應商謹此聲明，不負任何對與此資訊有關之擔保責任，包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。