SQL Server 事務日誌清理兩大實戰法與「不產生日誌」誤區

方法一：備份截斷 + 收縮（標準流程）

-- 1. 截斷日誌（二選一）
BACKUP LOG database_name WITH NO_LOG
-- 或
BACKUP LOG database_name WITH TRUNCATE_ONLY

-- 2. 收縮文件
-- 收縮整個數據庫（數據+日誌）
DBCC SHRINKDATABASE (database_name [, target_percent])

-- 或針對單一文件收縮（需 file_id）
DBCC SHRINKFILE (file_id [, target_size_MB])
-- 獲取 file_id: SELECT FILE_ID('logical_log_name') 或查 sysfiles

參數 NOTRUNCATE（只移動頁不釋放空間）與 TRUNCATEONLY（釋放尾部空間不移動頁）可組合使用，詳見幫助。

方法二：分離刪除日誌文件再附加（極簡暴力，適用 SQL 2000+）

-- 1. 完整備份防萬一
-- 2. 分離數據庫（刪除註冊信息）
EXEC sp_detach_db 'yourDBName', 'true'

-- 3. 物理刪除或移動 .ldf 日誌文件
-- 4. 以單文件模式附加（自動創建 500KB 新日誌）
EXEC sp_attach_single_file_db 'yourDBName', 'd:\mssql7\data\yourDBName_data.mdf'

為什麼無法「完全不產生日誌」？

事務日誌是 ACID 基石，任何數據修改（INSERT/UPDATE/DELETE）必須記錄日誌以保證可恢復性。僅有極少操作（如 TRUNCATE TABLE、DROP INDEX、SELECT INTO、BCP 等）為最小日誌記錄，無法完全關閉。

實戰案例：DELETE 產生日誌遠超 SELECT INTO

SELECT * INTO test_2 FROM b_bgxx  -- 4.5萬行，產生日誌 ~10MB
DELETE FROM test_2                -- 同表刪除，產生日誌 ~80MB！

原因：SELECT INTO 為最小日誌（僅記錄頁分配）；DELETE 逐行記錄、產生大量邏輯日誌。
對策：大量刪除改用 TRUNCATE TABLE（無 WHERE 條件）或分批 DELETE TOP (N) 並頻繁 BACKUP LOG。