☰

sql server日誌空間使用率怎麼會超過100%？

tag-icon • tag-icon ms-sql
sql server日誌空間使用率怎麼會超過100%？

我們看到報告事務日誌的應用程式錯誤（異常）”由於 ACTIVE_TRANSACTION 已滿“。 沒有報告待處理的交易DBCC OPENTRAN。

運行時DBCC SQLPERF('logspace')我看到日誌大小僅為 1.3MB，但已用日誌空間報告為 107.7%

此資料庫配置的日誌檔案Maxsize超過2TB，初始大小為2MB，自動增長設定為10%。恢復模型設定為“簡單”。

日誌空間使用率怎麼會超過100%，這麼大的可用空間，為什麼還會產生異常呢？

答案1

尋找罪魁禍首

你可以跑sp_WhoIsActive看看誰在運行什麼，並查看那些正在運行的進程/活動事務的邏輯。檢查是否可以最佳化任何 T-SQL 查詢以運行得更快或更小的事務，以便更快地釋放日誌檔案中未使用的可用空間以供重複使用。

設計上是可能的

SQL Server 簡單復原模型

每個交易仍會寫入交易日誌，但一旦交易完成並且資料已寫入資料文件，交易日誌檔案中使用的空間現在就可以由新交易重新使用。

來源

我見過這樣的情況：在SIMPLE復原模型設定中，長時間運行的交易使交易日誌變得非常大。該事務實際上失敗了，並且需要同樣長的時間才能回滾。長時間運行的事務、效能不佳或未最佳化的編寫不當的查詢都可能導致此問題。

一旦空間被指派給SIMPLE復原模型資料庫事務日誌文件，未使用的交易日誌可用空間或者每個作業系統層級可用空間的自動成長，交易日誌檔案將保留新空間，直到發生檔案收縮操作，例如DBCC SHRINKFILE (database_log, 2048)。

重要的：例如，當發生檔案收縮操作時DBCC SHRINKFILE (database_log, 2048)，它只會將交易日誌中未使用的日誌空間作為可用空間釋放回作業系統。在檔案收縮操作期間，不會釋放交易日誌中寫入的活動運行事務。

縮小日誌文件

收縮日誌檔案的問題是，下次執行大型事務或寫得不好的查詢時，日誌檔案將再次填滿，您需要重複收縮操作。找到並解決根本問題，以永久解決此問題。同時，繼續縮小日誌檔。

修復根本原因

根本問題可能是一個查詢，因此確定誰在做什麼並聯繫他們並用您的發現報告問題會給他們帶來壓力，要求他們修復邏輯，不要破壞伺服器磁碟空間分割區；查看最佳化查詢調整的邏輯以提高效能。

  • 追蹤根本原因

    有時，根本原因不是長時間運行的事務 - 例如，可能是有人設定了複製，但從未正確地將其撤回。從檢查開始log_reuse_wait_desc in sys.databases:

    SELECT name, log_reuse_wait_desc FROM sys.databases;

    但請記住，這只是日誌現在無法縮小的原因的快照。

    然後，如果你在那裡沒有發現任何有趣的東西，你可以將 sp_WhoIsActive 記錄到表中 趕上人們做某事的時間BEGIN TRAN，並讓他們的會議持續幾個小時。尋找長期運行的事務，與所有者交談，看看他們是否可以以較小的塊而不是一個巨大的事務來完成工作。

  • 日誌檔案空間元數據

    DBCC SQLPERF(logspace)如果您只對資料庫日誌檔案的使用感興趣，那麼這是一個絕對有效的命令。它提供 SQL Server 執行個體上每個資料庫的每個日誌檔案的累積大小以及消耗的空間量（佔日誌檔案總大小的百分比）。 缺點是結果是資料庫的聚合。 如果您有多個日誌文件，結果將顯示在資料庫級別，而不是文件級別。

    雖然此DBCC 命令在您檢查因日誌備份計劃不充分或日誌檔案大小不正確而引起的問題時非常方便，但它並沒有為您提供在調整日誌檔案大小、調整備份計劃頻率方面做出明智決策所需的全部資訊或恢復模型。

    來源

相關內容