為什麼事務日誌在帶有夜間備份的簡單恢復模式下繼續增長

在立即標記為重複之前，我已經閱讀了 Mike Walsh 的為什麼事務日誌保持增長或空間不足？，但我認為它沒有回答我的情況。我瀏覽了十幾個類似的問題，但相關的大多只是說“重複”並指向邁克的問題。

詳細資訊：我在 SQL Server 2008 R2 上有一堆約 500MB 的數據庫，全部處於簡單恢復模式（不是我的選擇），每晚完整備份，具有約 200MB 數據文件和約 300MB 日誌文件。日誌不會立即增長到 300MB，而是在幾個月的過程中緩慢增長。至少根據 sp_who2 和活動監視器，其中任何一個都沒有打開的事務。如果我右鍵點擊數據庫並選擇屬性，它會告訴我有大約 50MB 可用空間。特別是在備份之後，整個日誌不應該是空閒的嗎？在簡單模式下，只要沒有打開的事務，日誌不應該是免費的嗎？

log_reuse_wait_descfromsys.databases說“NOTHING”，基於上面引用的問題和答案，它不應該等待任何東西來重用空間。

如果我執行“DBCC SHRINKFILE”，日誌文件會縮小到 1MB，因此它願意回收空間。我可以設置一些東西來每週縮小日誌並防止事情失控，但我很困惑為什麼 SQL Server 會讓我這樣做。

我可以理解是否有一些瘋狂的事務需要 300MB 來記錄它，但我們沒有做任何極端的事情，只是基本的 OLTP。從邁克的問題/答案：

簡單恢復模型——所以有了上面的介紹，最簡單的就是先說簡單恢復模型。在此模型中，您是在告訴 SQL Server - 我可以使用您的事務日誌文件進行崩潰和重新啟動恢復（您確實別無選擇。查找 ACID 屬性，這應該很快就有意義），但是一旦您沒有不再需要它用於崩潰/重新啟動恢復目的，繼續並重用日誌文件。

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

它一直說應該重用日誌空間，但是隨著幾個月的緩慢增長，它似乎並非如此。

我錯過了什麼？是否有什麼東西阻止 SQL Server 將數據辨識為“硬化”並釋放日誌？

（編輯） 行動後報告 - 又名一點知識是危險的

在發現這是一個“熱門問題”之後，我覺得我應該解釋一下 7 個月前發生的事情以及我學到的東西，希望能幫助其他人免去一些悲傷。

首先，當您查看數據庫屬性時，您在 SSMS 中看到的可用空間是數據文件中的可用空間。您可以通過在數據庫上執行以下命令來查看這一點，您會發現 SSMS 報告的可用空間是 FileSizeMB 和 UsedSpaceMB 之間的差異：

SELECT
   DB.name,
   MF.physical_name,
   MF.type_desc AS FileType,
   MF.size * 8 / 1024 AS FileSizeMB,
   fileproperty(MF.name, 'SpaceUsed') * 8/ 1024 AS UsedSpaceMB,
   mf.name LogicalName
FROM
   sys.master_files MF
   JOIN sys.databases DB ON DB.database_id = MF.database_id
WHERE   DB.name = 'yourdatabasename'

這確實證實了在正常情況下我們使用的日誌空間非常少（20MB 或更少），但這導致了第二項……

其次，我對原木增長的看法是隨著時間的推移而緩慢增長。然而，實際上，在負責為這個第 3 方應用程序應用更新檔的人正在應用更新檔的晚上，日誌正在迅速增長。更新檔是作為單個事務完成的，因此根據更新檔的不同，200MB 的數據需要 300MB 的日誌。追踪這一點的關鍵是 Aaron Bertrand 在https://sqlblog.org/2007/01/11/reviewing-autogrow-events-from-the-default-trace的查詢

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
  DatabaseName,
  [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass 
      WHEN 92 THEN 'Data'
      WHEN 93 THEN 'Log'
   END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE
   EventClass IN (92,93)
ORDER BY
   StartTime DESC;

這表明在某些晚上，當客戶不使用數據庫時，日誌會增長。這導致了與應用更新檔的人的對話以及謎團的答案。

再次感謝那些提供幫助讓我得到答案的人。

我們不可能猜測是什麼原因造成的，但是 SQL Server 不只是將日誌文件增長到 300 MB，它會增長到 300 MB，因為在您上次收縮操作後的某個時間點，它需要大量的日誌空間（無論是由於一些大的單個事務還是許多較小的並發事務）。您必須跟踪日誌文件增長事件（我在此處和此處討論過）以嘗試縮小發生這種情況的時間或原因（如果您的日誌文件增長設置為 300 MB 或其他內容，那麼它將增長 300 MB只要它需要超過 1 MB 的空間來容納活動事務）。

無論如何，你為什麼認為一旦日誌文件達到 300 MB 就需要縮小它？你真的仔細閱讀了關於邁克問題的所有答案嗎？日誌文件不會自行縮小，因為將日誌文件縮小到 1MB - 只是為了在您最大的事務期間它可以再次增長 - 完全是浪費時間。在此期間，您將如何處理所有這些可用空間？

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