SQL Server 疑慮模式資料庫修復完整指南：從錯誤 1105 到日誌縮減

疑慮模式成因與錯誤 1105

當 SQL Server 因磁碟空間不足無法完成資料庫復原，會回傳錯誤 1105 並將 sysdatabases.status 設為「疑慮」。錯誤訊息典型如下：

Can't allocate space for object syslogs in database dbname because the logsegment is full. If you ran out of space in syslogs, dump the transaction log. Otherwise use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

常見觸發情境

• 大量批次更新/插入/刪除交易
• 長時間未認可的開放式交易
• 檢查點截斷日誌所需頻寬過大
• 複寫標記交易未被日誌讀取器清除
• 上述條件疊加

此問題可能發生於任何資料庫，包含 master、tempdb 等系統庫。

分階段修復操作手冊

階段 1：單一用戶模式啟動 SQL Server

sqlservr -f -m

• -m：單一用戶模式，僅允許一個連線
• -f：最小配置啟動
• 注意：確認無其他服務/用戶端搶佔唯一連線

階段 2：重設資料庫狀態旗標

EXEC sp_resetstatus 'database_name';

回傳：Database 'database_name' status reset! WARNING: You must reboot SQL Server prior to Accessing this database!

階段 3：擴充資料/日誌檔案空間

USE master;
GO

-- 新增資料檔
ALTER DATABASE db_name
ADD FILE (
    NAME = dbname_dat2,
    FILENAME = 'F:\MSSQL\DATA\dbname_dat2.ndf',
    SIZE = 2000MB,
    FILEGROWTH = 50MB
);
GO

-- 新增日誌檔
ALTER DATABASE db_name
ADD LOG FILE (
    NAME = db_name_log2,
    FILENAME = 'F:\MSSQL\Data\db_name_log2.ldf',
    SIZE = 1000MB,
    FILEGROWTH = 20MB
);
GO

新增檔案提供額外空間，讓 SQL Server 能完成復原流程。

階段 4：重啟 SQL Server 服務

正常啟動服務，資料庫應自動完成復原並上線。

階段 5：釋放磁碟空間並縮減交易日誌

sp_resetstatus 僅關閉疑慮旗標，不釋放空間。後續須執行日誌備份與 DBCC SHRINKFILE 縮減實體檔案。

根本預防：復原模式與維護策略

簡單復原模式

USE MASTER;
GO
ALTER DATABASE db_name SET RECOVERY SIMPLE;
GO

• INSERT/UPDATE/DELETE 僅記錄最小資訊，日誌自動截斷
• 不支援時間點還原，適合非關鍵/可重建資料

完整復原模式 + 定期日誌備份

-- 完整備份
BACKUP DATABASE db_name TO DISK = 'path\full.bak';

-- 排程每 15-60 分鐘日誌備份
BACKUP LOG db_name TO DISK = 'path\log.trn';

• 支援任意時間點還原
• 必須配合日誌備份工作，否則日誌持續增長

自動增長與最大值設定

ALTER DATABASE db_name
MODIFY FILE (NAME = db_name_log, MAXSIZE = 10GB, FILEGROWTH = 512MB);

• 避免 FILEGROWTH 過小導致頻繁擴展碎片
• 設定 MAXSIZE 防止佔滿磁碟

緊急狀態下的替代方案（僅限極端情況）

1. 啟用系統表直接修改：sp_configure 'allow updates', 1; RECONFIGURE WITH OVERRIDE;
2. 設定緊急模式：UPDATE sysdatabases SET status = -32768 WHERE dbid = DB_ID('db_name');
3. 重建日誌：DBCC REBUILD_LOG('db_name', 'path\new_log.ldf');
4. 驗證一致性：DBCC CHECKDB('db_name');
5. 還原正常模式並關閉系統表修改

警告：緊急模式操作可能導致交易一致性遺失，僅作為最後手段，且必須在完整備份後執行。