SQL Server 2005/2008 事務日志壓縮與清理實戰指南

SQL Server 2005 適用方案

舊版本採用「備份截斷 + 收縮」兩步走：

USE YourDB;
GO
-- 1. 截斷日志（不記錄備份）
BACKUP LOG YourDB WITH NO_LOG;
GO
-- 或 DUMP TRANSACTION YourDB WITH NO_LOG; -- 同義詞

-- 2. 收縮文件
-- 方式 A：收縮整個數據庫
DBCC SHRINKDATABASE(YourDB);
GO
-- 方式 B：精準收縮日志文件（推薦）
-- 先查詢邏輯文件名：SELECT * FROM sysfiles;
DBCC SHRINKFILE (YourDB_Log);
GO

極端收縮（分離-刪除日志-重新附加）

EXEC sp_detach_db 'YourDB';
GO
-- 手動刪除 .ldf 文件
EXEC sp_attach_single_file_db @dbname='YourDB',
    @physname='C:\Path\YourDB.mdf';
GO

長期策略

• 啟用自動收縮：EXEC sp_dboption 'YourDB', 'autoshrink', 'TRUE';
• 限制文件增長：ALTER DATABASE YourDB MODIFY FILE (NAME=LogLogicalName, MAXSIZE=20MB);

SQL Server 2008+ 現代方案（推薦）

利用恢復模式切換實現無損截斷：

USE [master];
GO
ALTER DATABASE YourDB SET RECOVERY SIMPLE WITH NO_WAIT;
GO
ALTER DATABASE YourDB SET RECOVERY SIMPLE;
GO

USE YourDB;
GO
-- TRUNCATEONLY 僅釋放未使用空間，不移動數據頁，極快
DBCC SHRINKFILE (N'YourDB_Log', 11, TRUNCATEONLY);
GO

USE [master];
GO
ALTER DATABASE YourDB SET RECOVERY FULL WITH NO_WAIT;
GO
ALTER DATABASE YourDB SET RECOVERY FULL;
GO

核心差異對比

最佳實踐建議

1. 生產環境堅持 FULL 恢復模式 + 定期日志備份，從根源控制日志增長。
2. 臨時清理時優先用 2008+ 方案，避免 WITH NO_LOG 破壞備份鏈。
3. 收縮後重建索引消除碎片。
4. 監控 log_reuse_wait_desc 找出日志無法複用的真因。