SQL Server 錯誤記錄清理與無記錄檔資料庫修復：sp_cycle_errorlog、DBCC REBUILD_LOG 實戰

一、錯誤記錄暴增問題：Query Notification Dialog 狂刷日誌

現象：錯誤記錄檔極速增大（數 GB），內容多為 query notification dialog 訊息，源自 Service Broker 訊息機制。

暫時抑制：追蹤旗標 4133

DBCC TRACEON(4133, -1);  -- 全域啟用，抑制該類訊息寫入錯誤記錄

⚠️ 重啟服務後失效，需加入啟動參數 `-T4133` 永久生效。

根治：清空現有錯誤記錄檔 —— sp_cycle_errorlog

EXEC sp_cycle_errorlog;

• 每執行一次，**關閉目前錯誤記錄檔、建立新空白記錄檔**；舊檔遞增歸檔 (ERRORLOG.1, .2...)。
• 預設保留 7 個歸檔，執行 7 次即可清空所有歷史內容。
• **極速完成**：40 GB 記錄檔亦能秒級歸檔，不阻塞服務，適合緊急清理。

對比：手動刪除檔案需停止服務、風險高；sp_cycle_errorlog 線上安全執行。

固定單一記錄檔大小？

註冊表 ErrorLogSizeInKb 僅 **SQL Server 2012+** 支援，舊版設定無效。建議配合 Agent 作業定期執行 sp_cycle_errorlog 控制大小。

二、無記錄檔 (LDF 遺失/刪除) 資料庫修復實戰

情境：SQL Server 2000，LDF 達 200 GB 導致磁碟滿，使用者分離資料庫並**刪除 LDF**，導致資料庫無法附加，呈現「無記錄檔」狀態。

修復腳本 (適用 SQL 2000/2005/2008，需 sysadmin)

-- 0. 確保企業管理器/SSMS 無開啟該資料庫連線
USE master;
GO

-- 1. 允許直接修改系統目錄 (SQL 2000/2005 需要)
EXEC sp_configure 'allow updates', 1;
RECONFIGURE WITH OVERRIDE;
GO

-- 2. 設為緊急模式 (狀態 -32768)
UPDATE sysdatabases SET status = -32768 WHERE dbid = DB_ID('Procurement');
GO

-- 3. 重建記錄檔 (指定新 LDF 完整路徑)
DBCC REBUILD_LOG('Procurement', 'D:\Procurement_log.ldf');
GO

-- 4. 驗證一致性 (建議執行)
DBCC CHECKDB('Procurement');
GO

-- 5. 恢復正常模式
EXEC sp_dboption 'Procurement', 'dbo use only', 'false';
GO

-- 6. 還原系統設定
EXEC sp_configure 'allow updates', 0;
RECONFIGURE WITH OVERRIDE;
GO

關鍵點提醒

• sp_configure 'allow updates' 僅 SQL 2000/2005 需要；2008+ 系統表不可直接更新，改用 ALTER DATABASE ... SET EMERGENCY。
• 重建記錄檔會導致交易一致性遺失，僅適用於**無備份、無 LDF、資料表多為可重算/統計表**的救援情境。
• 執行後務必檢查核心業務表資料完整性，若有損壞需尋求專業資料救援。

三、現代版本 (SQL 2008+) 無 LDF 修復標準流程

-- 1. 設為緊急模式
ALTER DATABASE Procurement SET EMERGENCY;
GO
ALTER DATABASE Procurement SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- 2. 重建記錄檔
ALTER DATABASE Procurement REBUILD LOG ON (NAME = Procurement, FILENAME = 'D:\Procurement_log.ldf');
GO

-- 3. 恢復多使用者
ALTER DATABASE Procurement SET MULTI_USER;
GO

-- 4. 一致性檢查
DBCC CHECKDB('Procurement');
GO

結語

錯誤記錄暴增 用 sp_cycle_errorlog 秒級歸檔；無 LDF 修復 用 DBCC REBUILD_LOG (舊版) 或 ALTER DATABASE REBUILD LOG (新版) 重建記錄檔。**兩者皆屬救援手段，平時請落實備份策略、監控記錄檔大小、設定自動清理作業**，才能從容應對災難。