SQL 事務日誌填滿的原因

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.

[@more@]

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.

這可能會發生在任何資料庫（包括 master 或 tempdb）上。本文討論導致錯誤 1105 的那些問題的可能原因和解決方案。如果您的事務日誌已滿，並且當前收到錯誤 1105，則需要使用 DUMP TRANSACTION 語句清空該日誌。有關使用 DUMP TRANSACTION 的更多資訊，請參見 SQL Server 文件。

真正的關聯式資料庫（如 Microsoft 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 個虛擬日誌檔案。有時，資料庫管理員會啟用資料庫的“在檢查點截斷日誌”選項，以避免用盡日誌空間。此選項的目的在於提供一種自動截斷日誌的方法，主要用於那些不依賴日誌轉儲進行備份的開發資料庫或測試資料庫。此選項不禁用日誌記錄或事務完整性功能。它只會導致檢查點處理程式大約每 60 秒鐘嘗試進行一次截斷日誌操作。請注意，在啟用“在檢查點截斷日誌”選項的情況下，在資料庫中發出手動檢查點命令時，日誌不會被截斷。對於 tempdb 資料庫，此選項是始終啟用的，即使在 sp_help 儲存過程輸出的狀態列中未指示為要啟用時也是如此。即使啟用“在檢查點截斷日誌”選項，仍然有許多因素可能會導致用盡日誌空間。下面列出了這些可能的因素：

1. 大型原子事務（特別是批次 UPDATE、INSERT 或 DELETE）：每個單一 SQL 語句被視為一個原子單位，不論應用與否都必須作為一個整體。因此，必須記錄所有行變動，並且在事務期間不能將其截斷。例如，如果發出一個命令執行大批次 INSERT 操作，該操作需用時五分鐘，則在操作期間此事務所用的日誌不能被截斷。資料庫管理員必須為預期的最大批次操作提供足夠的日誌空間，或者必須在較小的組中執行批次操作。

2. 未提交的事務：只能在最早的未提交事務之前截斷日誌。有幾種可能的原因會導致未提交的事務，其中大部分是應用程式錯誤。原因包括：

a. 大容量事務：由於上述的原因，在大容量事務期間，不能截斷所生成的日誌記錄。不過，此類事務也會阻止同一期間提交的其他較短事務的日誌被截斷。例如，假設資料庫管理員設定的日誌大小足以支援能想象到的最大的大容量事務。然而在此事務執行時，其他較短的資料修改語句也會使用日誌空間。此日誌空間不能被截斷，因為大容量事務最先開始，因此成為最早的未提交事務。管理員必須意識到這種併發性和大容量事務的日誌影響，從而恰當地設定日誌的大小。

b. 設計糟糕的應用程式，這些應用程式允許在使用者定義的事務內進行使用者輸入或其他比較耗時的活動：例如，在發出 BEGIN TRANSACTION 後，應用程式可能提示使用者進行一個用時較長的輸入（具體輸入時間取決於使用者）。在使用者進行響應以及應用程式發出 COMMIT 命令前，不可能進行日誌截斷。

c. 未提交的事務中的應用程式錯誤：此情況的常見原因是在使用者定義的事務內未正確處理 DB-Library 呼叫 dbcancel()。當使用 dbcancel() 取消查詢時，當前執行的 SQL 語句將中止並回滾，但外部事務不會。應用程式必須意識到這一點，併發出必要的 ROLLBACK TRANSACTION 或 COMMIT TRANSACTION 語句以關閉該事務。如果未能執行此操作，通常會導致錯誤 3902：

The commit transaction has no corresponding BEGIN TRANSACTION.

對於要傳送 SELECT @@TRANCOUNT 來確定事務巢狀級別的應用程式，此操作可能會有用。不過，應用程式不應該盲目地執行此操作，然後發出 COMMIT/ROLLBACK 以實現 @@TRANCOUNT=0。這是因為，如果 @@TRANCOUNT 始終與應用程式所預期的不同，則表示應用程式已經失去對事務巢狀級別的跟蹤（這是應用程式設計錯誤）。此時發出 COMMIT/ROLLBACK 可能會導致錯誤地應用或中止事務，因為應用程式不知道哪個事務會導致不希望的事務級別。相反，程式設計師應該除錯該應用程式和所有相關的儲存過程，以確定出現不希望的事務級別的原因。

d. 未通知 SQL Server 出現了網路連線中斷的網路錯誤：如果客戶端工作站在使用者定義的事務內掛起、重新啟動或關閉，則網路層應該通知 SQL Server 所發生的情況。如果網路未能正確地完成此操作，則從 SQL Server 的角度來講，客戶端將被認為正確聯機，來自該客戶端的開啟事務將保持。這是一個網路問題，必須從這方面著手處理。作為一種替代方法，管理員可以使用 sp_who、sp_lock 或網路實用工具來確定仍存在的客戶端會話，並手動將其終止。

e. 由於受到阻塞，事務未提交：在多使用者環境中，開啟的事務可能被另一程式持有的鎖阻塞。在這種情況下，事務仍將保持開啟狀態，阻止日誌截斷。要發現此情況，程式設計師或資料庫管理員需要使用 sp_who、sp_lock 或其他工具對併發環境進行分析。大多數情況下，可以透過適當的查詢、索引和資料庫設計來減少或消除阻塞問題。

f. 嘗試取消資料修改查詢失敗：如果應用程式發出 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 中的事務進行日誌記錄與任何其他資料庫類似。由於 TRUNCATE LOG ON CHECKPOINT 在 tempdb 中執行，大多數情況下，日誌將被截斷並且不會溢位。不過，上述任意環境均可能導致 tempdb 日誌填滿。Tempdb 通常是針對混合日誌和資料配置的 (sysusages.segmap=7)，所以資料和日誌操作將爭奪同一可用的空間。GROUP BY 和 ORDER BY DESC 等某些 Transact-SQL 構造將自動為 tempdb 請求工作空間。這還會在作為工作空間的 tempdb 中產生隱式 BEGIN TRANSACTION 記錄。在使用者資料庫的事務期間，此 tempdb 事務將繼續，這可能會推遲此期間的 tempdb 日誌截斷。如果使用者資料庫中的事務因某種原因（包括阻塞鎖）停止，或者應用程式未能完成對 dbnextrow() 的處理，則 tempdb 中的事務也可能保持開啟狀態，從而阻止截斷 tempdb 日誌。程式設計師必須對該應用程式進行除錯和/或解決導致此情況的併發問題。

6. SQL Server 7.0 和 2000 類伺服器中事務日誌的截斷是透過截斷虛擬日誌檔案來實現的。如果活動日誌的任意部分駐留在給定的 VLF 中，則虛擬日誌檔案不能被截斷。如果活動日誌駐留在所有虛擬日誌檔案中，則不能截斷該日誌。如果啟用了自動增長，並且在事務日誌駐留的捲上有空間，且未達到最大檔案大小，則事務日誌將按日誌檔案屬性中指定的數量增長。

下面討論 SQL 啟動時根據是否設定了 TRUNCATE LOG ON CHECKPOINT 來進行日誌截斷的行為。

• 如果設定了 TRUNCATE LOG ON CHECKPOINT 且在啟動時發現日誌是填滿的，將使用 no_log 自動轉儲日誌。
• TRUNCATE LOG ON CHECKPOINT 是 master 中的預設值，因為其日誌不能放置於單獨裝置中，所以永遠不能載入。唯一可行的選項就是在日誌填滿時丟棄該日誌。
• 如果未設定 TRUNCATE LOG ON CHECKPOINT，且在啟動時發現日誌是填滿的，恢復可以完成，但不會寫入最終檢查點。管理員可以進入資料庫，並使用 no_truncate 轉儲日誌以儲存資料，然後使用 no_log 進行轉儲以清除日誌（或僅僅將其清除）。