SQL Server 交易記錄清理與縮減完整操作指南

方法一：備份截斷 + DBCC SHRINK (標準線上作法)

-- 1. 截斷記錄 (簡易復原模式下等效)
BACKUP LOG Database_Name WITH NO_LOG;
-- 或
BACKUP LOG Database_Name WITH TRUNCATE_ONLY;

-- 2. 縮減資料庫 (所有檔案)
DBCC SHRINKDATABASE (Database_Name [, target_percent]);

-- 3. 或縮減單一記錄檔
DBCC SHRINKFILE (File_ID [, target_size_MB]);
-- 取得 File_ID: SELECT FILE_ID('Log_Logical_Name');

參數 NOTRUNCATE 僅移動頁面不釋放空間；TRUNCATEONLY 僅釋放檔案末端未使用空間。

方法二：離線分離刪除記錄檔再附加 (適用 SQL 2000、需停機)

1. 完整備份資料庫以防萬一。
2. EXEC sp_detach_db 'YourDB', 'true'; (跳過更新統計資料)
3. 在檔案系統刪除或移動 .ldf 記錄檔。
4. EXEC sp_attach_single_file_db 'YourDB', 'D:\Data\YourDB_Data.mdf';
   SQL Server 自動建立 500 KB 新記錄檔。

風險：未提交交易遺失、資料庫損壞無法附加、複寫/鏡像/記錄傳送中斷。嚴禁用於生產環境核心資料庫。

能否完全不產生記錄？

無法。SQL Server 採用預寫式記錄 (WAL) 保證 ACID，所有資料修改必須先寫入記錄。唯有大量載入 (BULK INSERT、SELECT INTO、BCP、INSERT...SELECT 搭配 TABLOCK、最小記錄模式) 可最小化記錄量。

案例分析：每日 4-5 GB 記錄暴增

客戶每日 DELETE 全表再匯入，DELETE 逐列記錄產生大量記錄。
改善：改用 TRUNCATE TABLE (最小記錄、釋放頁面) 或 DROP/CREATE 資料表，搭配簡易復原模式，記錄量驟降 90% 以上。

最佳實務建議

• 依據 RPO/RTO 選擇復原模式：完整模式 + 定期記錄備份 (生產)；簡易模式 + 定期完整備份 (可容忍資料遺失的測試/數倉)。
• 設定 Auto Shrink = OFF，改用維護計畫定期 DBCC SHRINKFILE 至合理大小。
• 監控記錄空間使用率：DBCC SQLPERF(LOGSPACE)，設定警示。
• 大量異動操作前切換至大量記錄模式，操作後切回。