萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

MSSQL 記錄檔清理自動化腳本:脫離/刪除/附加全流程 T-SQL 實作

針對 SSIS 或排程作業需以純 T-SQL 完成「脫離資料庫 → 刪除實體 LDF → 重新附加」流程,本文提供可直接部署的完整腳本,包含動態取得檔案路徑、啟用 OLE Automation 刪除檔案、錯誤處理等細節。

核心流程

  1. 查詢 sys.database_files 取得 .mdf、.ldf 實體路徑。
  2. 切換到 master,執行 sp_detach_db 脫離資料庫。
  3. 呼叫 sp_OA* 系列擴展預存程序,透過 Scripting.FileSystemObject 刪除 LDF 檔案。
  4. 執行 sp_attach_single_file_db 以單一 MDF 重新附加,SQL Server 自動重建新 LDF。

完整部署腳本

-- 1. 取得檔案路徑
DECLARE @logfilename  VARCHAR(100), @datafilename VARCHAR(100);
SELECT @logfilename  = physical_name FROM sys.database_files WHERE type = 1; -- LOG
SELECT @datafilename = physical_name FROM sys.database_files WHERE type = 0; -- ROWS

-- 2. 脫離資料庫
USE master;
EXEC sp_detach_db @dbname = 'TestVFA';

-- 3. 刪除 LDF 檔案(需啟用 Ole Automation Procedures)
DECLARE @Result INT, @FSO_Token INT;
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token OUTPUT;
EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, @logfilename;
EXEC @Result = sp_OADestroy @FSO_Token;

-- 4. 重新附加(單一 MDF 自動重建 LOG)
EXEC sp_attach_single_file_db @dbname = 'TestVFA', @physname = @datafilename;

必要的伺服器設定

-- 啟用 Ole Automation Procedures(預設關閉)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_OACreate 'Ole Automation Procedures', 1; RECONFIGURE;

安全性提醒:開啟 OLE Automation 會擴大攻擊面,建議僅在受控維護視窗臨時開啟,用畢即關閉:

EXEC sp_configure 'Ole Automation Procedures', 0; RECONFIGURE;

替代方案(不依賴 OLE Automation)

  • xp_cmdshell:EXEC xp_cmdshell 'DEL ' + @logfilename(需啟用 xp_cmdshell,風險同等)。
  • SQL Agent 作業步驟:用 CmdExec 類型步驟執行 DEL 指令,再用 T-SQL 步驟附加。
  • PowerShell + sqlps:最現代、安全、可遠端執行的選擇(SQL 2008+ 內建)。

適用場景與限制

  • 適用:開發/測試環境、單機部署、可容許短暫停機的維護窗口。
  • 不適用:生產環境高可用性架構(鏡像、AlwaysOn、複寫)、資料庫超過 16 個檔案、LDF 損壞但 MDF 也不可信時。
copyright © 萬盛學電腦網 all rights reserved