這似乎是大多數論壇和整個網路中的一個常見問題，這里以多種格式提出，通常聽起來像這樣：

在 SQL Server 中 -

• 事務日誌增長如此之大的一些原因是什麼？
• 為什麼我的日誌文件這麼大？
• 有什麼方法可以防止這個問題的發生？
• 當我找到根本原因並希望將我的事務日誌文件設置為健康的大小時，我該怎麼辦？

一個簡短的答案：

您可能正在執行一個長時間執行的事務（索引維護？大批量刪除或更新？）或者您處於“預設”（更多關於預設含義的內容）的恢復模式Full並且沒有進行日誌備份（或沒有足夠頻繁地服用它們）。

如果是恢復模式問題，Simple如果您不需要時間點恢復和定期日誌備份，簡單的答案可能是切換到恢復模式。但是，許多人在不了解恢復模型的情況下就給出了答案。繼續閱讀以了解它為什麼重要，然後決定你做什麼。您也可以開始進行日誌備份並保持Full恢復狀態。

可能還有其他原因，但這些是最常見的。這個答案開始深入探討最常見的兩個原因，並為您提供有關原因和原因背後的一些背景資訊，並探討其他一些原因。

---

更長的答案： 哪些情況會導致日誌不斷增長？原因有很多，但通常這些原因有以下兩種模式： 對恢復模型存在誤解或存在長時間執行的事務。請繼續閱讀以了解詳細資訊。

首要原因 1/2：不了解恢復模型

（處於完全恢復模式並且不進行日誌備份- 這是最常見的原因 - 絕大多數遇到此問題的人。）

雖然這個答案不是對 SQL Server 恢復模型的深入探討，但恢復模型的主題對於這個問題至關重要。

在 SQL Server 中，有三種恢復模式：

• Full,
• Bulk-Logged和
• Simple.

我們現在將忽略Bulk-Logged我們會說它是一個混合模型，並且大多數處於此模型中的人都是有原因的並且了解恢復模型。

我們關心的兩個和他們的困惑是大多數人遇到這個問題的原因是Simple和Full。

中場休息：總體恢復

在我們談論恢復模型之前：讓我們來談談一般的恢復。如果您想更深入地了解這個主題，只需閱讀Paul Randal 的部落格以及您想要的盡可能多的文章。不過，對於這個問題：

1. 崩潰/重新啟動恢復

事務日誌文件的用途之一是用於崩潰/重新啟動恢復。對於崩潰或重啟前已完成的工作（前滾/重做）的前滾和回滾，以及崩潰或重啟後已開始但未完成的工作（回滾/撤消）。事務日誌的工作是查看事務開始但從未完成（回滾或崩潰/重新啟動發生在事務送出之前）。在那種情況下，日誌的工作是在恢復期間說*“嘿..這從未真正完成，讓我們回滾吧”。日誌的工作也是查看您確實完成了某些事情並且您的客戶端應用程序被告知它已完成（即使它尚未硬化到您的數據文件）並說“嘿..這真的發生了，讓我們繼續前進，讓它像應用程序認為的那樣”*重啟後。現在還有更多，但這是主要目的。 2. 時間點恢復

事務日誌文件的另一個目的是能夠讓我們能夠恢復到由於數據庫中的“oops”而導致的時間點，或者在硬體故障的情況下保證恢復點涉及數據庫的數據和/或日誌文件。如果此事務日誌包含已啟動和已完成以進行恢復的事務記錄，SQL Server 可以並且確實使用此資訊將數據庫獲取到問題發生之前的位置。但這對我們來說並不總是一個可用的選擇。為此，我們必須讓我們的數據庫處於正確的恢復模式，並且我們必須進行日誌備份。

恢復模式

恢復模型：

• 簡單恢復模型

有了上面的介紹，最簡單的還是先說Simple Recovery模型。在這個模型中，你告訴 SQL Server：“我很樂意使用你的事務日誌文件來進行崩潰和重新啟動恢復……”（你真的別無選擇。查找ACID 屬性，這應該很快就有意義了。）“……但是一旦您不再需要它來進行崩潰/重啟恢復，請繼續並重用日誌文件。”

SQL Server 在 Simple Recovery 中偵聽此請求，它只保留執行崩潰/重新啟動恢復所需的資訊。一旦 SQL Server 確定它可以恢復，因為數據被強化到數據文件（或多或少），已經強化的數據在日誌中不再需要並且被標記為截斷 - 這意味著它可以被重新使用。

• 完全恢復模型

使用Full Recovery，您告訴 SQL Server 您希望能夠恢復到特定時間點，只要您的日誌文件可用或恢復到日誌備份涵蓋的特定時間點。在這種情況下，當 SQL Server 在簡單恢復模型中達到可以安全截斷日誌文件的程度時，它不會這樣做。相反**，它允許日誌文件繼續增長**，並允許它繼續增長，直到您在正常情況下進行日誌備份（或日誌文件驅動器上的空間不足）。

從簡單切換到完整有一個陷阱。

這裡有規則和例外。我們將在下面深入討論長期執行的事務。

但是要記住完全恢復模式的一個警告是：如果您只是切換到Full Recovery模式，但從未進行初始完全備份，SQL Server 將不會滿足您的模型請求Full Recovery。您的事務日誌將繼續按原樣執行，Simple直到您切換到完全恢復模式並獲得第一個Full Backup.

沒有日誌備份的完整恢復模式很糟糕。

那麼，不受控制的日誌增長最常見的原因是什麼？答：處於完全恢復模式而沒有任何日誌備份。

這一直發生在人們身上。

為什麼這是一個如此常見的錯誤？

為什麼它總是發生？因為每個新數據庫都通過查看模型數據庫來獲取其初始恢復模型設置。

模型的初始恢復模型設置始終是Full Recovery Model- 直到並且除非有人改變它。所以你可以說“預設恢復模式”是Full. 許多人沒有意識到這一點，並且他們的數據庫在Full Recovery Model沒有日誌備份的情況下執行，因此事務日誌文件比必要的大得多。這就是為什麼當預設值不適合您的組織及其需求時更改預設值很重要的原因）

日誌備份太少的完整恢復模式很糟糕。

如果沒有足夠頻繁地進行日誌備份，您也可能在這裡遇到麻煩。

每天進行一次日誌備份聽起來不錯，它使還原需要更少的還原命令，但請記住上面的討論，該日誌文件將繼續增長，直到您進行日誌備份。

如何找出我需要的日誌備份頻率？

您需要考慮兩件事來考慮您的日誌備份頻率：

1. 恢復需求- 這應該是第一個。如果存放事務日誌的驅動器壞了，或者出現嚴重損壞影響日誌備份的情況，可能會失去多少數據？如果該數字不超過 10-15 分鐘，那麼您需要每 10-15 分鐘進行一次日誌備份，討論結束。
2. 日誌增長- 如果您的組織可以輕鬆地重新創建當天的數據而失去更多數據，那麼您的日誌備份頻率可能遠低於 15 分鐘。也許您的組織每 4 小時就可以了。但是你必須看看你在 4 小時內產生了多少交易。讓日誌在這四個小時內保持增長會導致日誌文件過大嗎？這是否意味著您的日誌備份需要太長時間？

---

首要原因 2/2：長時間執行的事務

（“我的恢復模式很好！日誌還在增長！）

這也可能是導致不受控制和不受限制的日誌增長的原因。無論恢復模式如何，但它經常出現*“但我處於簡單恢復模式 - 為什麼我的日誌仍在增長？！”*

這裡的原因很簡單：如果 SQL 使用此事務日誌進行恢復，如上所述，那麼它必須回溯到事務的開始。

如果您有一個需要很長時間或進行大量更改的事務，則日誌無法在檢查點截斷仍在打開的事務中或自該事務開始以來已開始的任何更改。

這意味著一個大刪除，在一個刪除語句中刪除數百萬行是一個事務，並且在整個刪除完成之前，日誌不能進行任何截斷。在Full Recovery Model中，此刪除被記錄，這可能是很多日誌記錄。維護視窗期間的索引優化工作也是如此。這也意味著糟糕的事務管理以及不注意和關閉打開的事務真的會傷害您和您的日誌文件。

我可以對這些長期執行的交易做些什麼？

您可以通過以下方式在這裡拯救自己：

• 適當調整日誌文件的大小以應對最壞的情況——比如您的維護或已知的大型操作。當您增加日誌文件時，您應該查看 Kimberly Tripp 的此指南（以及她發送給您的兩個連結）。正確的尺寸在這裡非常重要。
• 觀察您對交易的使用情況。不要在您的應用程序伺服器中啟動事務並開始與 SQL Server 進行長時間的對話，否則可能會導致打開時間過長。
• 觀察 DML 語句中的隱含事務。例如：UPDATE TableName Set Col1 = 'New Value'是一筆交易。我沒有放在BEGIN TRAN那裡，我也不必，它仍然是一個在完成後自動送出的事務。因此，如果對大量行進行操作，請考慮將這些操作分批成更易於管理的塊，並給日誌時間恢復。或者考慮合適的尺寸來解決這個問題。或者也許考慮在批量載入視窗期間更改恢復模型。

---

這兩個原因是否也適用於 Log Shipping？

簡短的回答：是的。下面更長的答案。

問題：“我正在使用日誌傳送，所以我的日誌備份是自動的……為什麼我仍然看到事務日誌增長？”

答案：繼續閱讀。

什麼是日誌傳送？

日誌傳送就是它聽起來的樣子 - 您將事務日誌備份傳送到另一台伺服器以用於 DR 目的。有一些初始化，但之後的過程相當簡單：

• 在一台伺服器上備份日誌的工作，
• 複製該日誌備份的作業和
• 在目標伺服器上恢復它而不恢復（NORECOVERY或）的作業。STANDBY

如果事情沒有按照您的計劃進行，還有一些工作需要監控和提醒。

在某些情況下，您可能只想每天或每三天或每週一次進行日誌傳送恢復。沒事兒。但是，如果您對所有作業（包括日誌備份和復製作業）進行此更改，則意味著您一直在等待進行日誌備份。這意味著您將有大量的日誌增長——因為您處於沒有日誌備份的完全恢復模式——而且這可能還意味著要複製一個大的日誌文件。您應該只修改還原作業的計劃，並讓日誌備份和副本更頻繁地發生，否則您將遇到此答案中描述的第一個問題。

---

通過狀態碼進行一般故障排除

除了這兩個之外還有其他原因，但這些是最常見的。不管是什麼原因：有一種方法可以分析這種無法解釋的日誌增長/缺少截斷的原因，並查看它們是什麼。

通過查詢sys.databases目錄視圖，您可以看到描述日誌文件可能正在等待截斷/重用的原因的資訊。

有一列呼叫log_reuse_wait原因程式碼的查找 ID 和一log_reuse_wait_desc列描述等待原因。參考書籍線上文章中的大部分原因（您可能會看到的原因以及我們可以解釋原因的原因。缺少的原因要麼已停用，要麼供內部使用），並附有一些關於等待的說明斜體：

• 0 = 沒有

聽起來像什麼……不應該等待

• 1 = 檢查點

等待檢查點發生。這應該會發生，你應該沒問題 - 但有些情況需要在這裡尋找以後的答案或編輯。

• 2 = 日誌備份

您正在等待日誌備份發生。要麼你已經安排好它們並且很快就會發生，要麼你有這裡描述的第一個問題，你現在知道如何解決它

• 3 = 活動備份或恢復

正在數據庫上執行備份或恢復操作

• 4 = 活動事務在可以備份日誌之前，

有一個活動事務需要完成（無論哪種方式 -ROLLBACK或）。COMMIT這是此答案中描述的第二個原因。

• 5 = 數據庫鏡像

在高性能鏡像情況下，鏡像落後或處於某些延遲狀態，或者鏡像由於某種原因暫停

• 6 = 複製 複製

可能會出現問題 - 例如日誌讀取器代理未執行，數據庫認為它已標記為不再是複制以及各種其他原因。您也可以看到這個原因，這是完全正常的，因為您正在查看正確的時間，就像日誌閱讀器正在使用事務一樣

• 7 = 數據庫快照創建

您正在創建數據庫快照，如果您在創建快照時查看正確的時刻，您會看到這一點

• 8 = 日誌掃描

我還沒有遇到這個永遠執行的問題。如果您看的時間足夠長並且足夠頻繁，您可以看到這種情況發生，但這不應該是事務日誌過度增長的原因，我已經看到了。

• 9 = AlwaysOn 可用性組輔助副本正在將此數據庫的事務日誌記錄應用到相應的輔助數據庫。 關於最清晰的描述..

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