SQL Server 2000 完美壓縮 MDF 資料檔：重建資料表 + DBCC SHRINKFILE 實戰步驟

問題

企業管理器「收縮資料庫」僅能釋放自動成長空間，無法回收刪除資料後的碎片空間，MDF 長期佔用大量磁碟。

前置準備

務必先完整備份資料庫 (完整備份 + 記錄備份)。

步驟

1. 找出最大/最碎片化的資料表

   DBCC SHOWCONTIG  -- 列出所有資料表掃描密度
   -- 或針對疑似大表
   EXEC sp_spaceused 'LargeTable'  -- 比較 reserved vs data 差異
   

2. 將資料複製到新資料表

   SELECT * INTO NewTable FROM OldTable
   

   注意：SELECT INTO 不會複製主鍵、索引、預設值、Identity 屬性、檢查條件約束，需手動補建。

3. 更新空間使用資訊

   DBCC UPDATEUSAGE (0)  -- 0 代表目前資料庫
   

4. 刪除舊表、重新命名新表

   企業管理器：刪除 OldTable → 將 NewTable 改名為 OldTable → 補建主鍵、索引、Identity、預設值等。

5. 收縮 MDF (關鍵步驟)

   -- 收縮整個資料庫資料檔
   DBCC SHRINKDATABASE('DatabaseName')
   
   -- 或精確收縮資料檔 (file_id = 1 通常是 MDF)
   DBCC SHRINKFILE(1, 0)  -- 0 = 收縮至初始大小
   
   -- 再次更新使用資訊
   DBCC UPDATEUSAGE (0)
   

實測成果

作者實測：6 GB MDF 壓縮至約 1 GB，查詢速度明顯提升。

原理解析

• SELECT INTO 重新寫入資料頁，自然消除分頁分割與已刪除資料的空洞
• DBCC SHRINKFILE 釋放檔案尾端未使用空間回作業系統
• 重建索引進一步優化頁面填充率

自動化建議

將上述步驟封裝為 Agent 作業，每月/每季執行一次 (視資料變動頻率)，並監控磁碟剩餘空間。