tl;博士

我認為我的冗長掩蓋了我在這裡提出的真正問題，所以我為此道歉。我的主要問題是 checkpoint 命令似乎在循環的至少 800 次迭代中工作。在此期間，日誌大小保持不變，約為 1GB。然後，某天一夜之間，檢查點顯然未能釋放日誌文件中的空間，並消耗了驅動器。沒有計劃的數據庫備份。我不知道是什麼導致了這種行為。

我可以做些什麼來確保在 FULL 和 SIMPLE 恢復模式下完成每個循環迭代時釋放日誌空間？

---

我正在嘗試在一個非常大的表上更改架構（並進行一些數據更改），而不會對需要此表的應用程序和服務造成任何中斷，並將事務日誌保持在可管理的大小。

所以我決定在一個循環中處理表數據，要麼進行日誌備份，要麼在checkpoint每次迭代結束時根據恢復模型發出一條語句。每個循環將一些數據遷移到一個新表中（舊表和新表都在視圖後面進行 UNION ALLd，以確保數據僅部分遷移時的可用性）。

然而，在 SIMPLE 恢復模式下，事務日誌在開發伺服器上爆炸，但以一種非常奇怪的方式。在回家之前，我監視了循環的前約 800 次迭代（總共約 3000 次）的日誌，並且日誌的大小與每次迭代處理的記錄數一致，因此該方法似乎是在職的。然而，第二天早上到達時，我注意到日誌已經爆炸並消耗了整個磁碟，只需要 400 次迭代。

我不明白為什麼這個過程最初似乎可以工作，然後在某個看似任意的點上失敗了。如果我的邏輯錯誤，我會期望日誌穩定增長，但這並沒有發生。

我截斷了日誌，所以它從 ~500KB 開始。在第一次循環之後它增長到大約 1.2GB，並且在最初的幾百次迭代中保持在大約這個大小。基於此，方法似乎是合理的。

我的腳本總結為以下虛擬碼：

select distinct [fk] into #tmp from [huge_table];

while exists (select 1 from #tmp) begin
   select top (1) @id = [fk] from #tmp;

   -- Actual query is more complicated...
   delete [huge_table]
   output deleted.* into [new_table]
     from [huge_table] a
    where a.[fk] = @id;

   -- Keep log down
   if @recovery = N'SIMPLE'
       checkpoint;
   else begin
       set @logpath = '\\path\to\backups\log_' + cast(@c as varchar) + '.trn';
       backup log 'database_name' to disk = @logpath;
       set @c = @c + 1;
   end

   delete top (1) #tmp;
end

在這一點上，我的選擇似乎是 a) 以某種方式解決問題，或者 b) 假設在生產中的完全恢復模式下不會發生相同的退化行為。我很自然地被選項 B 排斥，但不知道如何去做選項 A。

我怎樣才能確保事務日誌的大小能夠正常執行。有什麼我想念的嗎？

我目前唯一的想法是附加到循環的末尾，例如：

if @logsize > 10GB (or some other arbitrary number)
   dbcc shrinkfile (N'database_log_file', 0, truncateonly);

但是，除了本身很糟糕之外，我想不出任何方法可以將這種方法轉換為完全恢復模式下的數據庫，所以在這一點上它真的不是我的選擇。

**編輯：**另外，我log_reuse_wait_desc在失敗後檢查了列，但有問題的數據庫的結果是NOTHING，因此沒有提供任何啟示。dbcc shrinkfile事後我能夠進行日誌截斷，因此日誌沒有處於任何阻止截斷的狀態。

即使使用 SIMPLE 恢復模型，如果有一個長時間執行的事務正在進行，trans log 仍然會爆炸。我想知道您的開發伺服器上的某些東西是否在您的腳本執行它的操作時正在執行，並導致 trans 日誌無法截斷，因為 MinLSN 來自某個長時間執行的事務。

不過，要回答這個問題，除非您限制對數據庫的訪問（例如使用 ALTER DATABASE SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE 或類似的類似 SINGLE_USER 執行腳本），否則您無法強制日誌文件釋放空間，因為其他事務可能是執行並且trans日誌必須為這些事務保持一致。

檢查TechNet（或 BOL：檢查點和日誌的活動部分）以獲取更多資訊。

該表是否用於其他用途？鏡像、複製等？這些將導致事務日誌爆炸。

您不想在循環中縮小日誌文件。這將導致事務日誌執行不佳，並且您的 DBA 想要限制您。您需要在程序執行時對其進行監視，以查看導致日誌增長的原因。事後查看系統將毫無用處，因為日誌不再增長。在程序正在執行並且日誌正在積極增長時查看日誌重用列。其他任何事情都幾乎是浪費時間。

您的程式碼看起來很好。

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