每次事務日誌已滿時數據庫進入恢復模式
我面臨的情況有點難以解決。我需要幫助來了解正在發生的事情。
TL;DR:每次事務日誌在 SQL Server 中變滿時,都需要關閉數據庫才能進入恢復模式並回滾有問題的事務?這總是按設計完成的,還是僅在發生不好的事情時才會發生?
場景:
我們大量使用的生產數據庫之一執行多個 ETL 作業和長時間執行的表批處理,進入恢復模式並在一段時間內無法訪問。本週發生了 3 次(此伺服器已執行約 2 年,過去我們沒有註意到此問題)。
查看錯誤日誌,發生的事情很清楚:事務日誌已滿,數據庫需要回滾事務,回滾失敗,數據庫關閉,並以恢復模式啟動。
DBA 認為這是 SQL Server 的正常行為。也就是說,在他看來,每次事務日誌滿了,事務需要回滾時,數據庫都會因為日誌空間不足而進入恢復模式。回滾後(據他說只能在恢復模式下完成),數據庫將再次可用。
我沒有找到此資訊的參考。所以我強烈反對。如果有人說服我我錯了,我將不勝感激。
我的觀點:
據我所知,DBMS 是用來管理/執行查詢的。如果缺少空間,查詢將失敗。很簡單。而且我不是在談論其他任何東西的性能,而只是在談論可用性。
對我來說,接受 DBMS 需要通過設計關閉自身以回滾任何事務是沒有意義的。據我了解,如果我執行大量查詢或查詢設計不當,這並不重要。錯誤的查詢應該失敗並且生活繼續。不是嗎?
我的猜測是其他原因使其失敗,我需要跟踪正在發生的事情。
我的理解是錯誤的還是這就是 SQL Server 的設計方式?假設我沒有錯,我還能做些什麼來追踪這個問題的根源?
一些附加資訊
select @@version
: Microsoft SQL Server 2012 (SP1) - 11.0.3156.0 (X64) May 4 2015 18:48:09 版權所有 (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: )- 該數據庫處於簡單恢復模式。
- 同一實例中還有其他數據庫。它們不存在相同的問題,但它們也沒有大量使用。
- 只有事務日誌已滿,磁碟未滿。磁碟有足夠的空間,但數據庫的日誌大小是有限的。
- 我們監控這台伺服器,CPU 負載正常,記憶體使用正常,磁碟使用 RAID-5,控制器沒有崩潰或讀取失敗。資源使用有一些高峰,但並不少見。
- 我知道可以改進查詢以有效地使用日誌。我也知道我可以增加事務日誌空間。但這真的不是我的意思。
- 最近聘請了一位 DBA 來管理這個數據庫。因此,最近更改了幾個配置,以進行調整。他讓我知道了所有的變化(比如禁用自動收縮、增加自動增長大小等)。我沒有發現任何可能損害數據庫的東西。
日誌轉儲(按出現順序,刪除重複項)
[02:58:37am ~ 04:47:42pm, 12 times]
錯誤:845。嚴重性:17。狀態:1。在等待頁面的緩衝區鎖存器類型 3 時發生超時 (1:8728760)。數據庫 ID 7。FlushCache:在 77540 毫秒內清理了 10460 個緩衝區和 6709 次寫入(避免了 864 個新的髒緩衝區),db 7:0 平均吞吐量:1.05 MB/秒。I/O 飽和度:107。上下文切換 391 最後一個未完成的目標:4800。avgWriteLatency 0 FlushCache:在 85820 毫秒內清理了 95448 個緩衝區和 37560 次寫入(避免了 60465 個新的髒緩衝區),db 7:0 平均吞吐量:8.69 MB/秒。I/O 飽和度:17026。上下文切換 20713 最後一個未完成的目標:446。avgWriteLatency 3。
[02:58:37am ~ 04:47:42pm, 13 times]
等待緩衝區鎖存器時發生超時 - 類型 3。bp 000000109B9E69C0。第 1 頁:73430228。統計 0x10b。數據庫ID:7。分配單元ID:72057594304790528。任務0x00000008BC0850C8:1。等待時間300秒。標誌 0x100000001a。擁有任務 0x0000000827B38188。沒有繼續等待。
[02:58:37am ~ 04:47:42pm, 12 times]
錯誤:5901。嚴重性:16。狀態:1。屬於數據庫“XXXXXXXXXX”的一個或多個恢復單元未能生成檢查點。這通常是由於缺乏系統資源(例如磁碟或記憶體)或在某些情況下由於數據庫損壞造成的。檢查錯誤日誌中以前的條目以獲取有關此故障的更多詳細資訊。
[05:14:29pm ~ 05:14:53pm, 9 times]
錯誤:9002。嚴重性:17。狀態:4。由於“ACTIVE_TRANSACTION”,數據庫“XXXXXXXXXX”的事務日誌已滿。
[05:14:53pm, once]
錯誤:3314。嚴重性:21。狀態:3。由於常式“XdesRMReadWrite::RollbackToLsn”中的錯誤 3314,數據庫 XXXXXXXXXX 已關閉。在與數據庫的所有連接都被中止後,將嘗試重新啟動非快照數據庫。
[05:14:53pm ~ 05:14:53pm, 16 times]
錯誤:3314。嚴重性:21。狀態:3。在撤消數據庫“XXXXXXXXXX”中記錄的操作期間,日誌記錄 ID (8064074:20971:110) 發生錯誤。通常,特定故障以前會作為錯誤記錄在 Windows 事件日誌服務中。從備份中恢復數據庫或文件或修復數據庫。
[05:14:53pm ~ 05:14:53pm, 9 times]
錯誤:9001。嚴重性:21。狀態:5.數據庫“XXXXXXXXXX”的日誌不可用。檢查事件日誌以獲取相關的錯誤消息。解決所有錯誤並重新啟動數據庫。
[05:14:58, once]
啟動數據庫“XXXXXXXXXX”。
[05:15:02, once]
數據庫“XXXXXXXXXX”(7) 的恢復已完成 0%(大約還剩 2931 秒)。第 1 階段,共 3 階段。這只是一條資訊性消息。無需使用者操作。…
[05:51:01pm, once]
6 個事務在數據庫“XXXXXXXXXX”(7:0) 中回滾。這只是一條資訊性消息。無需使用者操作。
[05:51:01pm, once]
恢復正在數據庫“XXXXXXXXXX”(7) 中寫入檢查點。這只是一條資訊性消息。無需使用者操作。
[05:56:47pm, once]
數據庫 XXXXXXXXXX(數據庫 ID 7)的恢復在 2505 秒內完成(分析 1774 毫秒重做 406623 毫秒撤消 1749182 毫秒。)這只是一條資訊性消息。無需使用者操作。我在錯誤日誌或事件查看器中沒有發現其他相關的日誌條目。事件查看器中發生的最接近的錯誤是:
[04:56:45pm ~ 05:27:24pm, 13 times]
特定於應用程序的權限設置不會將具有 CLSID {FDC3723D-1588-4BA3-92D4-42C430735D7D} 和 APPID {83B33982-693D-4824-B42E-7196AE61BB05} 的 COM 伺服器應用程序的本地啟動權限授予使用者 MY_DOMAIN\dba。 personal.user SID (S-1-5-21-000000000-000000000-0000000000-00000) 來自在應用程序容器中執行的地址 LocalHost (使用 LRPC) 不可用 SID (不可用)。可以使用組件服務管理工具修改此安全權限。此錯誤發生在數據庫開始恢復過程前約 18 分鐘,並且有時在恢復開始期間重複出現。和DBA使用者有點關係,但我真的不知道是什麼(我還沒來得及問DBA)。
首先是一些家政規則。
- 您(或您的 DBA)應根據您的恢復模式管理事務日誌空間。
- 不要讓事務日誌變滿並影響您的數據庫/應用程序的可用性。
以下兩個連結可以幫助您更好地管理事務日誌文件。
- SQL Server 事務日誌體系結構和管理指南
- 如何清除 SQL Server 事務日誌?由 Aaron Bertrand 回答,其中還列出了有關事務日誌問題的一些重要資源。
當事務日誌文件已滿且無法進一步增長時,您遇到的不是正常行為。
當事務日誌已滿時,SQL Server 數據庫引擎會發出 9002 錯誤。日誌可以在數據庫聯機或恢復時填充。如果在數據庫聯機時日誌已滿,則數據庫保持聯機但只能讀取,不能更新。如果在恢復期間日誌已滿,數據庫引擎會將數據庫標記為 RESOURCE PENDING。在任何一種情況下,都需要使用者操作才能使日誌空間可用。
對完整事務日誌的適當響應部分取決於導致日誌填滿的條件。要發現在給定情況下阻止日誌截斷的原因,請使用 sys.database 目錄視圖的 log_reuse_wait 和 log_reuse_wait_desc 列。
您看到的是事務回滾失敗。有關更多詳細資訊,請閱讀這篇文章。
根據 Paul Randal 的部落格文章,您遇到了一個在 SQL 2012 SP4 中修復的錯誤。
有關錯誤 3314 的更多詳細資訊:
參考: