SQL Server - AlwaysOn 組中的日誌文件未縮小

我們在 SQL Server 中設置了三節點 AO。主節點，一個處於同步模式的輔助節點和另一個處於非同步模式的輔助節點。主節點上的主數據庫，日誌文件大小為 434GB，已使用 99.91%。日誌備份僅在主節點上每小時發生一次。

在主伺服器和輔助伺服器上執行以下查詢。

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'Main DB Name'

結果：

node        log_reuse_wait_desc
------------------------------------------------------------
primary     AVAILABILITY_REPLICA
Secondary-1 LOG_BACKUP
Secondary-2 AVAILABILITY_REPLICA

雖然 AO 組中的其他 DB（遠小於主 DB）的日誌使用率非常低（1 到 10%）並且可以縮小，但主 DB 日誌使用率持續顯示超過 99% 並且沒有縮小。我猜是因為 log_reuse_wait_desc 在主節點上顯示“AVAILABILITY_REPLICA”，這意味著並非所有日誌都傳輸到輔助副本，因此它仍然已滿。

我檢查了 Secondary-1 節點中的重做隊列大小顯示 27GB，Secondary-2 沒有顯示任何內容。因此，輔助 1 很可能需要從主伺服器獲取所有日誌。問題是 Secondary-1 節點中的重做隊列大小正在增加，而不是像我應該通過的那樣減少。主節點上的日誌文件也在緩慢增加，這將填滿整個磁碟空間。

現在 Secondary-1 節點顯示 LOG-BACKUP。我該如何解決這個問題？我應該在 Secondary-1 節點上進行日誌備份並檢查嗎？

問題已解決。顯然，連接到同一個大型數據庫的程序之一已執行超過 24 小時，似乎處於“掛起”狀態，與輔助副本數據庫的多個打開連接導致阻塞。我猜這導致沒有完成從主節點到輔助節點的日誌複製，因為當我結束這些程序時，阻塞立即消失，重做隊列大小開始減少而不是之前增加。大約一個小時左右後，主節點中的日誌文件顯示可用空間超過 80%。

dbcc opentran 和 sp_who2 沒有提供太多細節來跟踪這個問題。有幫助的是

select * from sys.sysprocesses where blocked<>0
dbcc inputbuffer(spid)

第一個語句是找出導致阻塞的原因，第二個語句是殺死。我只是分享它是否可以幫助處於類似情況的其他人。

如我的文章中所述，僅在主節點上每小時對所有使用者數據庫進行 TLog 備份。到目前為止，TLog 物理文件的大小很小，不會增加，而且大部分時間都有超過 90% 的可用空間——這就是它應該的樣子。所以問題解決後，唯一的區別是文件大小非常大，我理解這是因為它一直在增長。現在我需要做一個 TLog 收縮（我們之前做過），它應該回到初始大小。

TLog back 不需要在所有副本上進行。從技術上講，您可以，但是如果您要處理所有副本，則在數據庫恢復的情況下，您必須從所有節點收集所有 TLog 備份並恢復它們，這可能很複雜。

跟踪日誌備份Secondary-1可能會也可能不會解決問題：

我檢查了 Secondary-1 節點中的重做隊列大小顯示 27GB，Secondary-2 沒有顯示任何內容。因此，輔助 1 很可能需要從主節點獲取所有日誌。問題是 Secondary-1 節點中的重做隊列大小正在增加，而不是像我應該通過的那樣減少。主節點上的日誌文件也在緩慢增加，這將填滿整個磁碟空間。

原因：

日誌的持續增加可能是由於在輔助副本上長時間執行選擇查詢，它阻塞了主副本發送的 DDL 操作。這導致輔助副本積壓和重做隊列增加。

辨識：

在這種情況下，我們可以Secondary-1通過 Extended events -> Sessions ->來驗證重做阻塞AlwaysOn_health。

解決方案：

一旦通過擴展事件辨識出重做阻塞，執行KILL特定select操作（在輔助）副本將釋放重做阻塞，並最終redo_queue_size清除積壓（）。 有關類似案例研究和修復的詳細步驟..

如果在次要副本的擴展事件中沒有出現阻塞， 請WAIT_TYPES特別考慮REDO執行緒，詳細步驟。

---

以下步驟可能有助於快速修復以阻止登錄的進一步增長Secondary-1 Replica。

1. 暫停特定的數據庫同步Secondary-1 Replica，等到長查詢完成Primary replica，然後恢復同步Secondary-1 Replica。您可以通過

SSMS -> 可用性組（組名稱）-> 可用性數據庫來執行此操作

---

如果問題仍然存在，您可以繼續執行以下步驟：

刪除並重新加入輔助數據庫，而不會中斷同一 AG 中的其他數據庫。（考慮一下，如果就地只讀路由配置會產生影響：）

1. 在Secondary-1 Replica

Using SSMS -> availability groups (Group Name) -> availability databases (右鍵點擊 DB)上刪除輔助數據庫。或者

 -- On SECONDARY 1
  ALTER DATABASE DbName SET HADR OFF;

通過這樣做，此Secondary-1 Replica數據庫變為Restoring模式，但對 AG 或主副本沒有影響

2. 恢復最近的備份 Full並Log打開Secondary-1 Replica，不要忘記選擇RESTORE WITH NORECOVERY
3. 恢復完成後，通過 使用SSMS -> 可用性組（組名）-> 可用性數據庫（右鍵點擊數據庫）加入數據庫或 Secondary-1 Replica

  -- On SECONDARY 1
  ALTER DATABASE DbName SET HADR AVAILABILITY GROUP = AG_NAME;

引用自：https://dba.stackexchange.com/questions/245739