SQL Server 版本：SQL Server 2019
背景
在一個48T大小的單數據庫環境中，采用簡單恢復模式，日志文件大小限制為600G。執行一次完整備份時，耗時超過12小時，導致日志文件無法截斷并達到上限，后續事務無法正常寫入，導致整個數據庫不可用。

問題現象
LDF日志文件中的虛擬日志文件 (VLF) 全部為活動狀態，導致日志無法正常截斷。由于日志文件大小達到了600G的限制，后續的事務寫入失敗，導致數據庫操作停滯。

排查思路
排查思路一般有下面幾個：
1、大事務導致日志無法截斷：可能是未提交的大事務阻止了日志的截斷。
2、特殊環境：如復制 (Replication)、鏡像 (Mirroring)、可用性組 (AG)、變更數據捕獲 (CDC) 等場景下，備用端異常會導致主端無法截斷日志。
3、未及時備份事務日志：在完整恢復模式下，未及時備份事務日志導致日志文件增長。
4、數據庫恢復時間設置：修改過數據庫恢復時間，可能導致checkpoint延遲，從而延長日志文件的截斷時間。

排查步驟
按照下面思路進行逐一排查
1、確認數據庫恢復模式：數據庫為簡單恢復模式，排除事務日志備份問題（思路3）。
2、檢查運行環境：數據庫為單機模式，排除復制、鏡像、AG等場景（思路2）。
3、檢查長時間事務：使用 `DBCC OPENTRAN` 檢查，未發現長時間運行的事務，排除大事務問題（思路1）。
4、檢查數據庫恢復設置：`TARGET_RECOVERY_TIME` 和 `recovery interval` 為默認值，排除恢復時間問題（思路4）。
5、檢查阻塞情況：未發現阻塞問題。
6、檢查SQL Agent作業：除了完整備份外，未發現其他作業運行。
7、檢查寫入邏輯：與開發人員溝通得知，該數據庫為日常批量數據寫入，日志寫入量超過400G。

問題原因分析
這里面我們需要從數據庫完整備份的原理入手，解剖備份的細節，分析原因，一般來說，數據庫完整備份分為兩個階段：
1、第一階段：對數據庫做一個Checkpoint，然后記錄備份開始時的LSN，生成快照式備份。
2、第二階段：快照備份結束后，記錄最新的LSN，并將這兩次LSN之間的事務日志寫入備份。

在數據庫完整備份過程中，即使數據庫處于簡單恢復模式，完整備份依然會拷貝未提交事務的日志。對于長時間運行的事務，完整備份需要包含足夠的日志信息來撤銷這些未提交的事務。用戶在還原完整備份的時候，實際上也是需要經歷整個數據庫崩潰恢復（Crash Recovery）過程。由于客戶的數據庫實在太大，備份時間超過12個小時，導致備份操作一直處于第一階段，無法進入到第二階段。這期間，事務日志文件中的日志無法截斷，12小時內的事務量超過600GB，導致ldf事務日志文件被撐滿到閾值。

優化建議
最后的解決方案是，開啟SQL Server 2019的ADR（加速數據庫恢復）功能，完整備份時候，日志文件只有少量增長，問題解決。
啟用 ADR 的步驟
確保使用的是 SQL Server 2019 或 SQL Server 2022，因為 ADR 是從 SQL Server 2019 開始引入的。

--開啟數據庫的ADR功能
ALTER DATABASE [YourDatabaseName]
SET ACCELERATED_DATABASE_RECOVERY = ON;
GO
--檢查 ADR 狀態
SELECT name, is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = 'YourDatabaseName';
--關閉 ADR
ALTER DATABASE [YourDatabaseName]
SET ACCELERATED_DATABASE_RECOVERY = OFF;
GO

這里有如下幾個優化思路：
1、多文件備份：將默認的單文件備份改為多文件備份，可以提升備份效率，縮短備份時間。（之前介紹過SQL Server的一個不顯眼的功能備份文件分割）
2、升級SQL Server版本：升級到SQL Server 2019或SQL Server 2022，啟用ADR（加速數據庫恢復）功能，通過SLOG實現日志及時截斷。
3、業務拆分：盡管不現實，但從業務上進行拆分也可以減少單一數據庫的事務量。

總結
在SQL Server 2019中，一個48TB數據庫因備份耗時過長，導致日志文件無法截斷并達到上限，阻礙事務寫入。
本文介紹了多種解決方案包括多文件分割備份、啟用數據庫ADR功能。
最終啟用數據庫ADR功能解決了問題。這里要注意的是，一定要盡量使用最新的數據庫版本，例如SQL Server 2019或者SQL Server 2022，
保證能使用到最新功能，可以擺脫很多麻煩。

補充：數據庫完整備份原理
在完整備份過程中，即使數據庫處于簡單恢復模式，備份依然會拷貝未提交事務的日志。對于長時間運行的事務，備份會包含足夠的日志信息來撤銷這些未提交的事務。
因此，即使數據庫的MDF文件較小，日志文件（LDF）也可能會導致備份文件非常大。
這與MySQL的Xtrabackup的原理幾乎是一樣的，備份開始時生成一個開始LSN，結束時生成一個結束LSN，如果有未提交事務，MySQL8.0的undoxx文件會非常大

轉自https://www.cnblogs.com/lyhabc/p/18402109/