萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 日誌與資料庫檔案壓縮完整操作指南

SQL Server 縮減資料庫與日誌檔案大小的標準流程

資料庫日誌(LDF)與資料檔(MDF)過大會佔用磁碟空間並影響備份速度。本文提供從安全到激進的分級操作方案,強烈建議操作前完成完整備份。

⚠ 風險提示: 步驟 4 與 6 存在資料損風險,非必要勿用。生產環境請優先考慮步驟 1-3、5。

階段一:標準清理流程(安全)

1. 清空交易日誌(舊版語法)

DUMP TRANSACTION 資料庫名 WITH NO_LOG

2. 截斷交易日誌(標準語法)

BACKUP LOG 資料庫名 WITH NO_LOG

3. 收縮資料庫檔案

圖形介面操作: 企業管理器 → 右鍵資料庫 → 所有工作 → 收縮資料庫 → 收縮檔案 → 選擇日誌/資料檔 → 設定目標大小(介面會顯示最小可縮減值)。

T-SQL 指令:

-- 收縮整個資料庫
DBCC SHRINKDATABASE(N'客戶資料')

-- 收縮指定檔案(1 為檔案編號,可透過 sysfiles 查詢)
DBCC SHRINKFILE(1)

階段二:進階處理(謹慎使用)

4. 分離資料庫刪除日誌重建(高風險)

  1. 企業管理器 → 右鍵資料庫 → 所有工作 → 分離資料庫。
  2. 在檔案系統中刪除 .ldf 日誌檔。
  3. 企業管理器 → 右鍵資料庫 → 所有工作 → 附加資料庫,僅選取 .mdf 檔,系統將自動建立最小日誌(約 500KB)。

T-SQL 等效操作:

EXEC sp_detach_db @dbname = 'pubs';
-- 手動刪除 pubs_log.ldf
EXEC sp_attach_single_file_db @dbname = 'pubs', 
    @physname = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf';

階段三:長期優化設定

5. 啟用自動收縮(建議配合維護計畫)

-- T-SQL 開啟
EXEC sp_dboption '資料庫名', 'autoshrink', 'TRUE';

或企業管理器 → 資料庫屬性 → 選項 → 勾選「自動收縮」。

6. 限制日誌檔案最大增長(防止失控)

ALTER DATABASE 資料庫名 
MODIFY FILE (NAME = 邏輯檔名, MAXSIZE = 2048MB); -- 設定上限 2GB

企業管理器 → 資料庫屬性 → 交易日誌 → 設定「檔案增長限制」。

最佳實踐建議

  • 定期完整備份 + 交易日誌備份 是控制日誌增長的根本方案。
  • 簡單復原模式下日誌會自動截斷,全量復原模式需定期備份日誌。
  • 監控 DBCC SQLPERF(LOGSPACE) 追蹤日誌使用率。
  • 避免長時間未提交的大型交易導致日誌膨脹。
copyright © 萬盛學電腦網 all rights reserved