SQL Server 備份檔案異常增長的排查指南

在管理 SQL Server 資料庫時，有時會遇到備份檔案大小突然增加的情況。即使資料表中的資料量沒有明顯增長，備份檔案卻莫名變大了好幾 GB，這時該如何進行排查呢？本文將介紹幾個常見的原因和解決方案。

常見原因分析

1. 交易記錄檔（Transaction Log）過大

交易記錄檔是記錄資料庫所有異動的檔案，如果沒有定期做記錄檔備份或截斷，可能會導致檔案持續成長。

診斷方式：

1	DBCC SQLPERF(LOGSPACE);

這個指令可以顯示所有資料庫的記錄檔使用狀況。

2. 索引碎片化

頻繁的資料異動可能造成索引碎片化，進而影響備份檔案大小。

診斷方式：

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
       ind.name AS IndexName, 
       indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind 
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC

此查詢可以找出碎片率超過 30% 的索引。

3. 快照隔離層級的版本存儲

如果啟用了 RCSI（Read Committed Snapshot Isolation）或快照隔離，系統會在 tempdb 中保留資料版本，可能導致備份檔案變大。

4. 備份壓縮設定

檢查備份是否有啟用壓縮功能。未壓縮的備份檔案會比壓縮後的檔案大上許多。

5. 大型物件（LOB）資料異動

異動大型物件資料（如文字、圖片等）時，即使只修改一小部分，在備份時可能也會包含整個物件。

診斷與解決步驟

Step 1: 檢查資料庫完整性

1	DBCC CHECKDB('您的資料庫名稱') WITH NO_INFOMSGS;

Step 2: 檢查資料庫實際使用空間

1	EXEC sp_spaceused;

Step 3: 重建索引並收縮資料庫

-- 重建所有索引
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO

-- 收縮資料庫
DBCC SHRINKDATABASE (N'您的資料庫名稱');

Step 4: 檢查各資料表的大小與記錄數

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceKB DESC, t.Name