文章編號: 110139 - 上次校閱: 2006年11月22日 - 版次: 4.0

INF：SQL 交易記錄檔滿溢的原因

Retired KB Article依現狀不再更新的知識庫內容免責聲明
檢視此文章適用的產品。
系統提示本文適用於您使用的作業系統之外的作業系統。與您不相關的文章內容已停用。
本文曾發行於 CHT110139

在此頁中

全部展開 | 全部摺疊

結論

SQL Server 交易記錄檔會滿溢，進而導致資料庫後續的 UPDATE、DELETE 或 INSERT 活動無法執行，其中亦包括 CHECKPOINT 在內。此問題通常會以錯誤 1105 表示：
Can't allocate space for object syslogs in database dbname because the logsegment is full.If you ran out of space in syslogs, dump the transaction log.Otherwise use ALTER DATABASE or sp_extendsegment to increase the size of the segment. (無法在資料庫 dbname 中配置物件 syslog 的空間，因為記錄片段已滿。如果 syslog 的空間已經用盡，請傾印交易記錄檔。否則，請使用 ALTER DATABASE 或 sp_extendsegment 增加片段大小。)
所有資料庫怕可能發生此問題，包括 master 或 tempdb。本文將討論造成錯誤 1105 問題的可能原因及解決方案。如果交易記錄檔已滿，且近期內曾收到錯誤 1105，便須使用 DUMP TRANSACTION 陳述式清空記錄檔。如需有關使用 DUMP TRANSACTION 的詳細資訊，請參閱 SQL Server 說明文件。
回此頁最上方

其他相關資訊

交易完整性是標準關聯式資料庫 (如 Microsoft SQL Server) 的基本特性。任何交易絕不可以只完成部分 (亦即在功能上不可分割)，且其所有變更均須全數套用或全數不套用，即使發生系統失敗亦是如此。在使用者定義的交易中，所有以 BEGIN TRANSACTION 與 COMMIT TRANSACTION 陳述式括起的陳述式，皆會全數套用或全數不套用。在隱含交易中，會將所有單一 SQL 陳述式視為不可部分完成的單位。

這項功能會可以讓 SQL Server 如果在運作期間及重新啟動後發生電源中斷、作業系統當機等狀況時，自動將資料庫復原為一致的狀態，而無需和使用者進行互動。這與非關聯式系統形成對比，因為後者經常需要透過冗長的手動程序，檢查系統失敗後隨之而來的資料庫一致性問題。

此功能是由交易記錄檔機制所提供。由於交易完整性是 SQL Server 的基本內建特性，因此無法停用記錄。某些公用程式或維護作業 (如快速 BCP 及 SELECT INTO 等) 僅會執行基本記錄，但即使如此，其仍需擴充配置，才能夠進行復原。

記錄所需要的空間可能相當可觀。例如，在大多數情況下，會記錄每一個更新資料列的更新前與更新後影像，加上所有受影響之索引列的影像。由於所記錄的各列皆會額外記錄固定數量的交易記錄，因此更新資料的記錄空間耗用率，會隨著列寬而不同。較窄列對於特定 UPDATE、DELETE 或 INSERT 所耗用的記錄空間量，可能是所耗資料空間的十倍。而較寬列所耗用的記錄空間量在比例上反而較少。耗用記錄空間是保有交易完整性的必然結果。資料庫管理員必須針對特定的安裝提供足夠的記錄空間。

所需要的記錄空間量會隨不同的因素而改變，因此很難事前精確預估。根據一般的經驗法則，通常會從資料庫大小的 15% 至 30% 開始設定，但實際上的差異卻很大。成功的 SQL Server 安裝通常會執行簡單的實證測試，以概略估計其特定資料及應用程式的記錄空間需求，然後再根據此結果決定記錄檔大小。單憑計算而不進行任何測試，不僅不容易決定記錄檔大小，所得結果亦不精確。

有多項因素因為預測不易，可能會造成記錄空間耗用量的變化。因素之一是查詢最佳化工具。對於特定的 SQL 資料修改陳述式，其存取規劃可能會因為資料的統計分佈不同而時有變動。不同之存取規劃所耗用的記錄空間數量亦不相同。另一個因素則是無可避免的內部資料庫必然分段；這會影響分頁數。我們無需採取任何措施試圖檢查或影響此處理程序，因為 SQL Server 會自動管理使用者的資料。

執行 DBCC CHECKTABLE(syslogs) 即為一例簡單的測試，其會傳回記錄檔中 2048 個位元組資料頁的數量，執行資料修改查詢代表範例前後的結果都會傳回。這項作業可以提供此種查詢所需要的記錄空間概數。通常最好能在為 SQL Server 等關聯式資料庫提供記錄或資料磁碟空間時，於超額方面犯錯。

SQL Server 7.0 及 2000 等級的伺服器，交易記錄檔有能力依需要擴充。成長量可由使用者控制，或利用所有可用的磁碟容量。記錄檔是由數個虛擬記錄檔案組成。這些虛擬記錄檔案的數量及大小是由 SQL Server 決定，無法加以設定。最初建立資料庫時，每個實體記錄檔皆至少有 2 個虛擬記錄檔案。資料庫管理員有時會啟用資料庫的 [truncate log on checkpoint] 選項，以免耗盡記錄空間。這個選項的目的在提供截斷記錄的自動方法，主要用於開發或測試不依賴記錄傾印以備份的資料庫。這個選項不會停用記錄或交易式完整性。它只會造成檢查點處理常式約每隔 60 秒即嘗試截斷記錄。請注意，當您在啟用 [truncate log on checkpoint] 的資料庫中發出手動檢查點命令時，不會截斷記錄檔。雖然儲存程序輸出之 sp_help 的狀態欄中未特別指明，但 tempdb 資料庫一律啟用這個選項。

即使啟用 [truncate log on checkpoint] 選項，仍有許多因素會造成記錄空間耗竭。如下所列：
  1. 大型的不可部分完成交易，特別是巨量的 UPDATE、INSERT 或 DELETE：每個單一 SQL 陳述式都視為不可部分完成的單位，必須整個全部套用或全不套用。因此之故，所有列的更動皆必須記錄，而且無法在作業期間截斷交易。例如，如果已發出執行時間為 5 分鐘的大型巨量 INSERT，則在此期間內無法截斷此交易所耗用的記錄檔。資料庫管理員必須為預期的大型巨量作業提供足夠的記錄空間，或必須將巨量作業分成較小的數個部分執行。
  2. 未認可的交易：記錄檔只能在最早未認可交易前的位置截斷。造成未認可交易的原因有好幾個，大部分是應用程式錯誤。包括：
    1. 大量交易：如上文所述，大型巨量交易期間，其產生的記錄無法截斷。但是，此種交易也會妨礙截斷同時間進行之其他較短的記錄。

      例如，假設資料庫管理員已決定記錄檔的大小，足供所能預見之最大的巨量交易所用。但當此交易執行時，其他較短的資料修改陳述式也可能耗用記錄空間。因為大型的巨量交易先開始，並成為最早的未認可交易，所以記錄空間無法截斷。管理員必須了解大型巨量交易的並行及記錄影響，以決定適當的記錄大小。
    2. 允許使用者輸入或在使用者定義的交易內進行其他冗長活動之設計不良的應用程式。例如，在發出 BEGIN TRANSACTION 之後，應用程式可能會提示使用者輸入，而此項作業可能會花費很長的時間，視使用者的行為而定。在使用者回應以及應用程式發出 COMMIT 之前，記錄檔都不可能截斷。
    3. 不認可交易的應用程式錯誤：此錯誤最常見的原因，是未在使用者定義交易內正確處理資料庫程式庫呼叫 dbcancel()。當查詢因 dbcancel() 而取消時，正在執行的 SQL 陳述式會中止並復原，但外部交易則不然。應用程式必須知道這一點，並發出必要的 ROLLBACK TRANSACTION 或 COMMIT TRANSACTION 陳述式關閉交易。無法執行這項操作經常會造成錯誤 3902：
      The commit transaction has no corresponding BEGIN TRANSACTION. (認可交易沒有對應的 BEGIN TRANSACTION。)
      應用程式傳送 SELECT @@TRANCOUNT 以判斷存在何種交易巢狀層次，可能會有幫助。但是，應用程式不應盲目執行此操作，然後發出 COMMIT/ROLLBACK 以達到 @@TRANCOUNT=0。這是因為，只要 @@TRANCOUNT 與應用程式預期的不同，即會指示應用程式遺失交易巢狀層次的軌跡，這是應用程式的設計錯誤。此時發出 COMMIT/ROLLBACK 可能導致套用或中止不想要的交易，因為應用程式不知道造成不想要之交易層次的交易為何。反倒是程式設計師應該對應用程式及與判斷不想要之交易層次成因有關的所有已儲存程序進行偵錯。
    4. 不通知 SQL Server 網路連線中斷的網路錯誤：如果用戶端工作站在使用者定義的交易內發生擱置、重新開機或關機現象，網路層應會通知 SQL Server。如果網路未正確執行此項作業，SQL Server 會認為用戶端一直存在，並維持該用戶端開啟的交易。這是網路問題，必須依網路相關問題處理。因應措施為，系統管理員可以判斷用戶端工作階段是在使用 sp_who、sp_lock 或網路公用程式時依然存在，再以手動方式予以刪除。
    5. 因封鎖而未認可的交易：在多使用者環境中，開啟的交易有可能為其他處理程序持有的鎖定所封鎖。發生此種情況時，交易仍然維持開啟狀態，阻止截斷記錄。如果要偵測此問題，程式設計師或資料庫管理員即必須使用 sp_who、sp_lock 或其他工具分析並行環境。多數情況下，透過適當的查詢、索引及資料庫設計，即可減少或消除封鎖問題。
    6. 無法嘗試取消資料修改查詢：如果應用程式發出 dbcancel()，而且因為網路或 SQL 問題未取消查詢，則查詢會繼續執行且交易維持開啟狀態。如果懷疑此處有問題，請使用 sp_who 查看是否已取消查詢。如果要嘗試從 TCP/IP 通訊端用戶端取消作業，請試從具名管道用戶端進行測試，或在使用本機管道的伺服器電腦上執行用戶端應用程式。這有助於分辨是網路或 SQL 問題阻止取消作業。
  3. 超過檢查點處理常式的截斷頻寬：雖然每隔 60 秒即截斷記錄檔，此截斷發生的頻率是有限的。這個案例不常見，您應先考慮其他可能的記錄檔溢位原因並予排除，再檢查此可能性。但是，如果同時有多個用戶端發出大量更新，即有可能超過最大截斷率。這很像是只能以特定流速滴漏液體的漏斗，甚至在滴漏時還可能溢出。在此案例中，您可重建應用程式以降低更新的列數，這應是所有關聯式資料庫不變的主要設計目標。

    如果不可行，您可以重新設定系統，增加磁碟 I/O 頻寬 (儘管有分段)、額外的控制站等。在此情況中，當其嘗試趕上記錄檔截斷時，常見檢查點處理常式程序花費更多時間於 DUMP TRANSACTION 狀態。一旦超過截斷臨界值 (如下)，在清除記錄檔之前，此資料庫中可能都看不到檢查點處理常式試圖進行截斷作業。
  4. 超過截斷臨界值：檢查點處理常式原是執行 DUMP TRANSACTION WITH TRUNCATE_ONLY。就好像原是以手動方式發出，如果記錄檔已滿溢至某個點，它不會每次都成功。例如，在兩次檢查點處理常式造訪之間，爆出巨量的更新活動可能會讓記錄檔滿至 95%。當檢查點處理常式嘗試截斷作業時，雖然記錄檔未全滿，卻可能沒有足夠空間執行截斷。這是因為截斷記錄檔必須自行記錄。此狀況唯一的解決方案，是使用 DUMP TRANSACTION WITH NO_LOG 手動截斷記錄檔。除非絕對必要，否則不建議使用 NO_LOG 選項，因為在此非記錄作業的期間，系統失敗可能會造成資料庫錯誤。
  5. 上述任一項目間的互動：例如，在密集更新環境的一般條件下，檢查點處理常式截斷率可保持記錄檔不滿溢。如果上述任一條件 (如爭用鎖定) 所導致暫時開啟的交易，假定造成記錄滿至 50%，用於處理其他更新情況的淨空高度就會很少，讓它更容易達到截斷臨界值，而在這個值點上不可能執行自動截斷。tempdb 像其他資料庫一樣會記錄截斷。因為 tempdb 啟用 [TRUNCATE LOG ON CHECKPOINT]，多數情況下記錄檔都會截斷，不發生溢位現象。但是，上述任一情況都可能造成 tempdb 記錄檔滿溢。Tempdb 通常設定用於混合式記錄與資料 (sysusages.segmap=7)，使資料與記錄作業爭用相同的可用空間。特定的 Transact-SQL 建構，如 GROUP BY、ORDER BY DESC 等，會自動向 tempdb 要求工作空間。這也會在工作空間中造成 tempdb 的隱含 BEGIN TRANSACTION 記錄。在使用者資料庫進行交易的期間，此 tempdb 交易會一直繼續，這會推遲此期間內的 tempdb 記錄檔截斷。如果使用者資料庫的交易因為任何原因而終止 (包括封鎖鎖定) 或是應用程式未處理 dbnextrow() 直至完成，tempdb 的交易都會保持開啟狀態，阻止 tempdb 記錄檔截斷。程式設計師必須偵錯應用程式及/或解決造成此問題的並行問題。
  6. SQL Server 7.0 及 2000 等級伺服器中的交易記錄檔截斷，是藉由截斷虛擬記錄檔案所完成。如果作用中的記錄檔有任何部分位於指定的 VLF，即無法截斷此虛擬記錄檔案。如果此作用中的記錄檔位於所有的虛擬記錄檔案，即無法截斷此記錄檔。如果啟用自動成長、交易記錄檔所在磁碟區還有空間，而且尚未達到最大檔案大小，則交易記錄檔會按照記錄檔內容所指定的量成長。
下文會根據 [TRUNCATE LOG ON CHECKPOINT] 設定與否，說明 SQL 啟動時的記錄檔截斷行為。
  • 如果設定 [TRUNCATE LOG ON CHECKPOINT] 且記錄檔於啟動時已滿溢，它會自動使用 no_log 傾印。
  • [TRUNCATE LOG ON CHECKPOINT] 現在是 master 的預設值，因為其記錄檔無法放到其他裝置，所以永遠無法載入。唯一可行的選擇，是捨棄滿溢的記錄檔。
  • 如果未設定 [TRUNCATE LOG ON CHECKPOINT] 且記錄檔於啟動時已滿溢，資料庫會完成修復，但不寫入最後的檢查點。系統管理員可以進入資料庫並使用 no_truncate 傾印記錄檔以儲存資料，然後再使用 no_log 傾印予以清除 (或僅予以清除)。

回此頁最上方

參考


如需有關 SQL Server 2000 的詳細資訊，請參閱下列書籍：
Microsoft Corporation Microsoft SQL Server 7.0 系統管理訓練套件 (http://go.microsoft.com/fwlink/?LinkId=8369 ) Microsoft Press, 2001
Microsoft Corporation MCSE 訓練套件：Microsoft SQL Server 2000 系統管理 (http://go.microsoft.com/fwlink/?LinkId=8370 ) Microsoft Press, 2001
Microsoft Corporation Microsoft SQL Server 2000 Resource Kit (http://go.microsoft.com/fwlink/?LinkId=8371 ) Microsoft Press, 2001
如需詳細資訊，請參閱下列 Microsoft Training & Certification 課程：
Microsoft Corporation 2072 管理 Microsoft SQL Server 2000 資料庫 (http://www.microsoft.com/redirect.asp?PageID=159&PARAM=BOKPSS87&TARGET=/traincert/syllabi/2072afinal.asp) (英文)
如需 SQL Server 7l0 (含) 以後版本的相關問題，請參閱下列「Microsoft 知識庫」中的文件：
317375? (http://support.microsoft.com/kb/317375/ ) INF：執行 SQL Server 之電腦上的交易記錄檔意外地擴充或滿溢
回此頁最上方
這篇文章中的資訊適用於:
  • Microsoft SQL Server 6.5 Standard Edition
  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 4.21a Standard Edition
回此頁最上方
關鍵字：?
kbinfo kbother KB110139
回此頁最上方
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應，不負任何擔保責任。Microsoft及(或)其供應商謹此聲明，不負任何對與此資訊有關之擔保責任，包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。
Retired KB Article依現狀不再更新的知識庫內容免責聲明
本文旨在說明 Microsoft 不再提供支援的產品。因此，本文係依「現狀」提供，不會再更新。
回此頁最上方