您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

SQL Server 交易日誌滿 (Log Full) 完整解決方案：簡單模式收縮、分離刪除、自動收縮陷阱

編輯：MySQL 資料庫相關知識

錯誤 The transaction log for database 'xxx' is full 導致寫入阻塞。根因通常是 **完整復原模式下未做日誌備份**、長時間大事務、max_log_size 限制。本文提供三級解決方案 (簡單收縮、分離重建、緊急繞過) 並分析自動收縮與檔案增長限制的副作用。

現象確認

-- 查看日誌空間使用
DBCC SQLPERF(LOGSPACE);
-- Database Name | Log Size (MB) | Log Space Used (%) | Status
-- mydb         | 10240         | 99.8               | 0 (正常但滿)

-- 查看為什麼無法截斷
SELECT name, log_reuse_wait_desc
FROM sys.databases
WHERE name = 'mydb';
-- 常見輸出：LOG_BACKUP (需日誌備份)、ACTIVE_TRANSACTION (長事務)、REPLICATION (複寫延遲)

方案一：標準收縮流程 (推薦，資料安全)

-- 1. 切換至簡單復原模式 (立即截斷非活動部分)
ALTER DATABASE mydb SET RECOVERY SIMPLE;

-- 2. 收縮日誌檔案 (指定目標大小，單位 MB)
DBCC SHRINKFILE (mydb_log, 500);  -- 目標 500MB，視業務調整

-- 3. 切回完整復原模式 (生產環境必須)
ALTER DATABASE mydb SET RECOVERY FULL;

-- 4. 立即執行完整備份 + 日誌備份，啟動備份鏈
BACKUP DATABASE mydb TO DISK = 'D:\Backup\mydb_full.bak' WITH INIT, COMPRESSION;
BACKUP LOG mydb TO DISK = 'D:\Backup\mydb_log.trn' WITH INIT, COMPRESSION;

原理：SIMPLE 模式下 Checkpoint 會截斷非活動 VLF，SHRINKFILE 釋放檔案末端未使用空間回作業系統。

方案二：分離資料庫刪除日誌檔 (風險較高，極端情況)

-- 1. 離線資料庫 (斷開所有連線)
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db 'mydb';

-- 2. 檔案系統層面刪除/重命名 .ldf 檔案
-- del D:\Data\mydb_log.ldf
-- 或 rename mydb_log.ldf mydb_log.ldf.bak

-- 3. 重新附加 (僅指定 .mdf，自動重建 1MB 空日誌)
EXEC sp_attach_single_file_db @dbname = 'mydb',
    @physname = 'D:\Data\mydb.mdf';

-- 4. 立即設定復原模式與備份
ALTER DATABASE mydb SET RECOVERY FULL;
BACKUP DATABASE mydb TO DISK = '...';

風險：未提交交易丟失、資料庫一致性風險、**極不建議生產環境使用**除非日誌檔案損壞無法讀取。

方案三：緊急繞過 (日誌檔案損壞、無法啟動)

-- 1. 進入緊急模式 (唯讀、僅 sysadmin)
ALTER DATABASE mydb SET EMERGENCY;
ALTER DATABASE mydb SET SINGLE_USER;

-- 2. 執行修復 (可能資料遺失)
DBCC CHECKDB (mydb, REPAIR_ALLOW_DATA_LOSS);

-- 3. 重建日誌 (同方案二)
-- 4. 設定復原模式、備份、驗證資料完整性

長效預防配置

設定	指令/操作	說明
日誌檔案初始大小	`ALTER DATABASE mydb MODIFY FILE (NAME=mydb_log, SIZE=1024MB);`	避免頻繁自動增長碎片
檔案增長固定 MB (非百分比)	`ALTER DATABASE mydb MODIFY FILE (NAME=mydb_log, FILEGROWTH=256MB);`	百分比增長不可控、越來越大
設定最大大小 (防止佔滿磁碟)	`ALTER DATABASE mydb MODIFY FILE (NAME=mydb_log, MAXSIZE=20480MB);`	達上限後寫入失敗，需人工擴容
啟用日誌備份作業	維護計畫 / Agent Job / Ola Hallengren Scripts	每 15-60 分鐘備份一次，截斷 VLF
監控告警	`log_reuse_wait_desc`、`DBCC SQLPERF(LOGSPACE)` 定時採集	使用率 > 80% 告警

自動收縮與增長限制的陷阱

AUTO_SHRINK = ON：背景執行緒定期收縮，**嚴重碎片化索引**、產生大量 I/O、觸發統計更新、導致計畫失效。**嚴禁開啟**。
FILEGROWTH 百分比：10% → 100MB → 110MB → 121MB... 指數增長，不可預測。**必須改固定 MB**。
MAXSIZE 限制：達上限後寫入失敗 (錯誤 9002)，需人工擴容。**建議設定極大值 (如 100GB) + 磁碟空間監控告警**，而非依賴 MAXSIZE 保護。

快速檢查腳本 (巡檢用)

SELECT
    DB_NAME(database_id) AS DBName,
    (size * 8 / 1024) AS LogSize_MB,
    (FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024) AS LogUsed_MB,
    CAST(FILEPROPERTY(name, 'SpaceUsed') * 1.0 / size * 100 AS DECIMAL(5,2)) AS Used_Pct,
    growth * 8 / 1024 AS Growth_MB,
    CASE max_size WHEN -1 THEN 'Unlimited' ELSE CAST(max_size * 8 / 1024 AS VARCHAR) + ' MB' END AS MaxSize,
    type_desc
FROM sys.master_files
WHERE type = 1  -- LOG
ORDER BY Used_Pct DESC;