SQL Server 版本：SQL Server 2019
背景
在一個48T大小的單數(shù)據(jù)庫環(huán)境中，采用簡單恢復模式，日志文件大小限制為600G。執(zhí)行一次完整備份時，耗時超過12小時，導致日志文件無法截斷并達到上限，后續(xù)事務無法正常寫入，導致整個數(shù)據(jù)庫不可用。
問題現(xiàn)象
LDF日志文件中的虛擬日志文件 (VLF) 全部為活動狀態(tài)，導致日志無法正常截斷。由于日志文件大小達到了600G的限制，后續(xù)的事務寫入失敗，導致數(shù)據(jù)庫操作停滯。
排查思路
排查思路一般有下面幾個：
1、大事務導致日志無法截斷：可能是未提交的大事務阻止了日志的截斷。
2、特殊環(huán)境：如復制 (Replication)、鏡像 (Mirroring)、可用性組 (AG)、變更數(shù)據(jù)捕獲 (CDC) 等場景下，備用端異常會導致主端無法截斷日志。
3、未及時備份事務日志：在完整恢復模式下，未及時備份事務日志導致日志文件增長。
4、數(shù)據(jù)庫恢復時間設置：修改過數(shù)據(jù)庫恢復時間，可能導致checkpoint延遲，從而延長日志文件的截斷時間。
排查步驟
按照下面思路進行逐一排查
1、確認數(shù)據(jù)庫恢復模式：數(shù)據(jù)庫為簡單恢復模式，排除事務日志備份問題（思路3）。
2、檢查運行環(huán)境：數(shù)據(jù)庫為單機模式，排除復制、鏡像、AG等場景（思路2）。
3、檢查長時間事務：使用 `DBCC OPENTRAN` 檢查，未發(fā)現(xiàn)長時間運行的事務，排除大事務問題（思路1）。
4、檢查數(shù)據(jù)庫恢復設置：`TARGET_RECOVERY_TIME` 和 `recovery interval` 為默認值，排除恢復時間問題（思路4）。
5、檢查阻塞情況：未發(fā)現(xiàn)阻塞問題。
6、檢查SQL Agent作業(yè)：除了完整備份外，未發(fā)現(xiàn)其他作業(yè)運行。
7、檢查寫入邏輯：與開發(fā)人員溝通得知，該數(shù)據(jù)庫為日常批量數(shù)據(jù)寫入，日志寫入量超過400G。
問題原因分析
這里面我們需要從數(shù)據(jù)庫完整備份的原理入手，解剖備份的細節(jié)，分析原因，一般來說，數(shù)據(jù)庫完整備份分為兩個階段：
1、第一階段：對數(shù)據(jù)庫做一個Checkpoint，然后記錄備份開始時的LSN，生成快照式備份。
2、第二階段：快照備份結束后，記錄最新的LSN，并將這兩次LSN之間的事務日志寫入備份。
在數(shù)據(jù)庫完整備份過程中，即使數(shù)據(jù)庫處于簡單恢復模式，完整備份依然會拷貝未提交事務的日志。對于長時間運行的事務，完整備份需要包含足夠的日志信息來撤銷這些未提交的事務。用戶在還原完整備份的時候，實際上也是需要經(jīng)歷整個數(shù)據(jù)庫崩潰恢復（Crash Recovery）過程。由于客戶的數(shù)據(jù)庫實在太大，備份時間超過12個小時，導致備份操作一直處于第一階段，無法進入到第二階段。這期間，事務日志文件中的日志無法截斷，12小時內(nèi)的事務量超過600GB，導致ldf事務日志文件被撐滿到閾值。
優(yōu)化建議
最后的解決方案是，開啟SQL Server 2019的ADR（加速數(shù)據(jù)庫恢復）功能，完整備份時候，日志文件只有少量增長，問題解決。
啟用 ADR 的步驟
確保使用的是 SQL Server 2019 或 SQL Server 2022，因為 ADR 是從 SQL Server 2019 開始引入的。
--開啟數(shù)據(jù)庫的ADR功能
ALTER DATABASE [YourDatabaseName]
SET ACCELERATED_DATABASE_RECOVERY = ON;
GO
--檢查 ADR 狀態(tài)
SELECT name, is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = 'YourDatabaseName';
--關閉 ADR
ALTER DATABASE [YourDatabaseName]
SET ACCELERATED_DATABASE_RECOVERY = OFF;
GO
這里有如下幾個優(yōu)化思路：
1、多文件備份：將默認的單文件備份改為多文件備份，可以提升備份效率，縮短備份時間。（之前介紹過SQL Server的一個不顯眼的功能備份文件分割）
2、升級SQL Server版本：升級到SQL Server 2019或SQL Server 2022，啟用ADR（加速數(shù)據(jù)庫恢復）功能，通過SLOG實現(xiàn)日志及時截斷。
3、業(yè)務拆分：盡管不現(xiàn)實，但從業(yè)務上進行拆分也可以減少單一數(shù)據(jù)庫的事務量。
總結
在SQL Server 2019中，一個48TB數(shù)據(jù)庫因備份耗時過長，導致日志文件無法截斷并達到上限，阻礙事務寫入。
本文介紹了多種解決方案包括多文件分割備份、啟用數(shù)據(jù)庫ADR功能。
最終啟用數(shù)據(jù)庫ADR功能解決了問題。這里要注意的是，一定要盡量使用最新的數(shù)據(jù)庫版本，例如SQL Server 2019或者SQL Server 2022，
保證能使用到最新功能，可以擺脫很多麻煩。
補充：數(shù)據(jù)庫完整備份原理
在完整備份過程中，即使數(shù)據(jù)庫處于簡單恢復模式，備份依然會拷貝未提交事務的日志。對于長時間運行的事務，備份會包含足夠的日志信息來撤銷這些未提交的事務。
因此，即使數(shù)據(jù)庫的MDF文件較小，日志文件（LDF）也可能會導致備份文件非常大。
這與MySQL的Xtrabackup的原理幾乎是一樣的，備份開始時生成一個開始LSN，結束時生成一個結束LSN，如果有未提交事務，MySQL8.0的undoxx文件會非常大
轉自https://www.cnblogs.com/lyhabc/p/18402109/
該文章在 2025/5/15 10:18:24 編輯過
400 186 1886
