一、為什么需要收縮日志文件？

在 SQL Server 中，事務(wù)日志文件（.ldf）會記錄所有數(shù)據(jù)庫事務(wù)操作（如增刪改、事務(wù)提交 / 回滾），用于故障恢復(fù)和數(shù)據(jù)一致性保障。但在以下場景中，日志文件可能會異常膨脹：

1. FULL 恢復(fù)模式下未定期備份日志：日志會持續(xù)累積事務(wù)記錄，無法自動釋放空間；
2. 長事務(wù)未提交：如長時間運行的 UPDATE/DELETE 語句，會鎖定日志片段，導(dǎo)致無法截斷；
3. 數(shù)據(jù)庫鏡像 / 復(fù)制配置異常：日志記錄因同步延遲被占用，無法正?；厥铡?/li>

日志文件過度膨脹會占用大量磁盤空間，甚至導(dǎo)致磁盤滿額、數(shù)據(jù)庫性能下降。此時需通過 “收縮操作” 釋放未使用的空間，但需注意：收縮僅適用于 “臨時清理空間”，需先排查膨脹根源（如完善備份計劃），避免頻繁操作導(dǎo)致文件碎片化。

二、可視化操作（SSMS 界面）

適用于Windows系統(tǒng)、新手或單數(shù)據(jù)庫少量操作，以 SQL Server 2012（版本 11.0）、數(shù)據(jù)庫 Db1 為例，核心步驟分三步：切換恢復(fù)模式→收縮日志→恢復(fù)原模式。

1. 將恢復(fù)模式調(diào)整為 “簡單”

簡單恢復(fù)模式（SIMPLE）的核心特點是 “事務(wù)日志自動截斷”—— 檢查點（Checkpoint）后，會自動釋放已提交事務(wù)的日志空間，無需手動備份日志，這是后續(xù)收縮日志的前提（FULL 模式下日志無法直接截斷）。

1. 打開 SQL Server Management Studio（SSMS），在 “對象資源管理器” 中找到目標(biāo)數(shù)據(jù)庫 Db1，右鍵點擊，選擇 “屬性（R）”；
2. 在 “數(shù)據(jù)庫屬性 - Db1” 窗口的左側(cè) “選擇頁” 中，點擊 “選項”；
3. 在右側(cè) “恢復(fù)模式（M）” 下拉框中，將默認(rèn)的 “完整” 改為 “簡單”；
4. 點擊 “確定” 保存設(shè)置，此時數(shù)據(jù)庫會立即切換到簡單恢復(fù)模式。

2. 收縮數(shù)據(jù)庫日志文件

切換到簡單模式后，日志中未使用的空間已標(biāo)記為 “可回收”，需通過 “收縮文件” 操作釋放磁盤空間。

操作步驟：

1. 右鍵點擊 Db1 數(shù)據(jù)庫，選擇 “任務(wù)（T）”→“收縮（S）”→“文件（F）”；
2. 在 “收縮文件 - Db1” 窗口中，進(jìn)行以下配置：
   • 文件類型（T）：下拉選擇 “日志”（默認(rèn)是 “數(shù)據(jù)”，需手動切換，避免收縮 .mdf 數(shù)據(jù)文件）；
   • 文件名（F）：自動顯示當(dāng)前數(shù)據(jù)庫的日志文件（如 Db1_log），無需修改；
   • 收縮操作：選擇 “釋放未使用的空間（R）”（僅釋放未使用的尾部空間，不移動日志數(shù)據(jù)，對性能影響最?。?；
     • 不建議選擇 “將文件收縮到（K）”：該選項會強(qiáng)制將日志壓縮到指定大?。ㄈ?3MB），可能導(dǎo)致日志數(shù)據(jù)頁重組，產(chǎn)生大量碎片化，影響后續(xù)事務(wù)性能；
     • 不建議選擇 “通過將數(shù)據(jù)遷移到同一文件組中的其他文件來清空文件（E）”：僅適用于刪除日志文件的場景，常規(guī)收縮無需使用；
3. 點擊 “確定”，SSMS 會執(zhí)行收縮操作，此時日志文件中未使用的空間會被釋放。

3.將恢復(fù)模式調(diào)整回“完整”。

簡單恢復(fù)模式雖便于收縮日志，但僅支持 “恢復(fù)到最近完整備份”，無法實現(xiàn) “時間點恢復(fù)”（如恢復(fù)到故障前 10 分鐘的數(shù)據(jù)），不符合生產(chǎn)環(huán)境對數(shù)據(jù)安全性的要求。因此收縮完成后，需立即切回完整恢復(fù)模式。

操作步驟：

1. 重復(fù)上述“1. 將恢復(fù)模式調(diào)整為 “簡單””的 1-2 步，打開 “數(shù)據(jù)庫屬性 - Db1” 的 “選項” 頁；
2. 將 “恢復(fù)模式” 從 “簡單” 改回 “完整”，點擊 “確定”；
3. 關(guān)鍵補(bǔ)充：切換回完整模式后，需立即執(zhí)行一次 “完整備份”（右鍵 Db1→“任務(wù)”→“備份”，選擇 “完整” 備份類型），否則后續(xù)的日志備份會失敗 —— 因為簡單模式會斷裂 “日志鏈”，完整備份是重建日志鏈、保障時間點恢復(fù)能力的前提。

三、代碼操作（T-SQL）

適用于批量操作（如多數(shù)據(jù)庫同時收縮）或自動化腳本（如通過作業(yè)定期執(zhí)行），相比可視化操作更高效、可復(fù)用。代碼分為 “單數(shù)據(jù)庫” 和 “多數(shù)據(jù)庫” 兩種場景，核心邏輯與可視化操作一致：查日志名→切簡單模式→收縮日志→切完整模式。

1. 單數(shù)據(jù)庫收縮（以 Db1 為例）

0. 前置步驟：查詢?nèi)罩疚募壿嬅Q

收縮日志前，需先確認(rèn)目標(biāo)數(shù)據(jù)庫的日志文件邏輯名稱，避免因名稱錯誤導(dǎo)致收縮失敗。

-- 0. 查詢數(shù)據(jù)庫 Db1 的日志文件邏輯名稱
SELECT 
    name AS 日志文件邏輯名稱,  -- 邏輯名稱（收縮時需用此名稱）
    physical_name AS 日志文件物理路徑,  -- 物理文件路徑（可確認(rèn)文件位置）
    size/128.0 AS 當(dāng)前大小_MB,  -- 轉(zhuǎn)換為 MB（SQL Server 中 size 單位是 8KB 頁）
    FILEPROPERTY(name, 'SpaceUsed')/128.0 AS 已使用大小_MB  -- 計算實際使用空間
FROM 
    sys.database_files  -- 系統(tǒng)視圖，存儲數(shù)據(jù)庫文件信息
WHERE 
    type = 1;  -- type=1 表示日志文件，type=0 表示數(shù)據(jù)文件

1. 切換到簡單恢復(fù)模式

    
-- 1. 將數(shù)據(jù)庫 Db1 的恢復(fù)模式設(shè)置為“簡單”
ALTER DATABASE Db1 
SET RECOVERY SIMPLE;  -- 未加 WITH NO_WAIT，默認(rèn)會等待數(shù)據(jù)庫鎖釋放（適合單庫操作，避免直接報錯）

2. 收縮日志文件

-- 2. 收縮 Db1 的日志文件（需替換為步驟 0 查詢到的日志文件邏輯名稱）
DBCC SHRINKFILE (
    N'Db1_log',  -- 第一個參數(shù)：日志文件邏輯名稱（N 表示 Unicode 字符串，避免中文/特殊字符問題）
    TRUNCATEONLY  -- 第二個參數(shù)：僅截斷未使用的尾部空間，不移動日志數(shù)據(jù)
);

代碼解釋：

• DBCC SHRINKFILE：SQL Server 內(nèi)置命令，用于收縮單個數(shù)據(jù)庫文件（數(shù)據(jù)或日志），相比 DBCC SHRINKDATABASE（收縮整個數(shù)據(jù)庫）更精準(zhǔn)；
• TRUNCATEONLY：核心參數(shù)，僅釋放 “已標(biāo)記為可回收” 的未使用空間，不會修改日志數(shù)據(jù)的存儲結(jié)構(gòu)，性能損耗極低；若省略此參數(shù)，默認(rèn)會先移動數(shù)據(jù)頁再截斷空間，可能導(dǎo)致碎片化。

3. 切換回完整恢復(fù)模式

-- 3. 將數(shù)據(jù)庫 Db1 的恢復(fù)模式設(shè)置為“完整”，并添加 WITH NO_WAIT 選項
ALTER DATABASE Db1 
SET RECOVERY FULL 
WITH NO_WAIT;  -- 若數(shù)據(jù)庫被其他進(jìn)程鎖定（如查詢/備份），不等待直接報錯（適合腳本自動化，避免無限等待）

補(bǔ)充說明：

• WITH NO_WAIT：若當(dāng)前數(shù)據(jù)庫有長事務(wù)或備份操作，會立即返回錯誤（如 “無法對數(shù)據(jù)庫 'Db1' 放置鎖”），需先終止占用進(jìn)程再執(zhí)行；
• 若希望 “低優(yōu)先級等待”，可替換為 WITH WAIT_AT_LOW_PRIORITY (WAIT_DURATION_SECONDS = 10)：表示等待 10 秒，若仍無法獲取鎖則報錯，兼顧效率與容錯。

2. 多數(shù)據(jù)庫批量收縮

當(dāng)需要同時收縮多個數(shù)據(jù)庫時，用 “游標(biāo) + 動態(tài) SQL” 實現(xiàn)循環(huán)處理，同時添加錯誤捕獲（可根據(jù)需要將執(zhí)行記錄保存到日志表中），避免單個數(shù)據(jù)庫失敗導(dǎo)致整個腳本中斷。

DECLARE @DBs TABLE (DBName NVARCHAR(128));
INSERT INTO @DBs (DBName)
VALUES 
    ('Db1'),   
    ('Db2');  
--Tip：再次維護(hù)需要收縮的數(shù)據(jù)庫名稱

DECLARE @CurrentDB NVARCHAR(128);
DECLARE @LogFileName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);

--使用游標(biāo)循環(huán)處理各個數(shù)據(jù)庫@DBs

DECLARE DB_Cursor CURSOR FOR 
SELECT DBName FROM @DBs;

OPEN DB_Cursor;
FETCH NEXT FROM DB_Cursor INTO @CurrentDB;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '------------------------------------------------';
    PRINT '開始處理數(shù)據(jù)庫：' + @CurrentDB;

    BEGIN TRY
        --1.切換數(shù)據(jù)庫為簡單恢復(fù)模式
        SET @SQL = N'ALTER DATABASE ' + QUOTENAME(@CurrentDB) + N' SET RECOVERY SIMPLE WITH NO_WAIT;';
        EXEC sp_executesql @SQL;
        PRINT @CurrentDB + ' 已切換為簡單恢復(fù)模式';

        --2.查詢?nèi)罩疚募壿嬅Q
        SET @SQL = N'
            USE ' + QUOTENAME(@CurrentDB) + N';
            SELECT TOP 1 @LogNameOUT = name 
            FROM sys.database_files 
            WHERE type = 1;  -- type=1 表示日志文件
        ';
        EXEC sp_executesql @SQL, 
            N'@LogNameOUT NVARCHAR(128) OUTPUT', 
            @LogNameOUT = @LogFileName OUTPUT;

        --3.收縮日志文件（釋放未使用空間）
        IF @LogFileName IS NOT NULL
        BEGIN
            SET @SQL = N'
                USE ' + QUOTENAME(@CurrentDB) + N';
                DBCC SHRINKFILE (N''' + @LogFileName + N''', TRUNCATEONLY);
            ';
            EXEC sp_executesql @SQL;
            PRINT @CurrentDB + ' 的日志文件 "' + @LogFileName + '" 收縮完成';
        END
        ELSE
        BEGIN
            PRINT @CurrentDB + ' 未找到日志文件，跳過收縮';
        END

        --4.切換回完整恢復(fù)模式
        SET @SQL = N'ALTER DATABASE ' + QUOTENAME(@CurrentDB) + N' SET RECOVERY FULL WITH NO_WAIT;';
        EXEC sp_executesql @SQL;
        PRINT @CurrentDB + ' 已切換回完整恢復(fù)模式';

    END TRY

    --報錯處理方式
    BEGIN CATCH
        
        PRINT @CurrentDB + ' 處理失?。?;
        PRINT '錯誤消息：' + ERROR_MESSAGE();
    END CATCH

    FETCH NEXT FROM DB_Cursor INTO @CurrentDB;
END

CLOSE DB_Cursor;
DEALLOCATE DB_Cursor;

PRINT '------------------------------------------------';
PRINT '所有數(shù)據(jù)庫處理完畢';

四、知識延伸

1. 為什么收縮前必須切換恢復(fù)模式？

• FULL 模式：日志會完整記錄所有事務(wù)，即使事務(wù)提交，未備份的日志也會保留（用于時間點恢復(fù)），無法截斷未使用空間，此時 DBCC SHRINKFILE 無效；
• SIMPLE 模式：事務(wù)提交后，日志僅保留 “崩潰恢復(fù)必需的信息”，檢查點會自動標(biāo)記未使用日志為 “可回收”，此時收縮才能釋放空間。

2. 生產(chǎn)環(huán)境收縮日志的注意事項

1. 避免業(yè)務(wù)高峰期執(zhí)行：收縮操作會產(chǎn)生 IO 開銷，若在高峰期執(zhí)行，可能導(dǎo)致數(shù)據(jù)庫響應(yīng)延遲；建議在凌晨或低峰期執(zhí)行；
2. 收縮后必做完整備份：切換回 FULL 模式后，日志鏈已斷裂，需立即執(zhí)行完整備份，否則后續(xù)日志備份會失敗，無法實現(xiàn)時間點恢復(fù)；
3. 不建議定期收縮：頻繁收縮會導(dǎo)致日志文件碎片化（日志數(shù)據(jù)分散在多個磁盤塊中），后續(xù)事務(wù)寫入時需頻繁尋址，降低性能；正確做法是 “排查日志膨脹根源”（如完善日志備份計劃，設(shè)置每 15-30 分鐘備份一次日志）；
4. 監(jiān)控日志文件大小：通過 SSMS 的 “數(shù)據(jù)庫→屬性→文件”，設(shè)置日志文件的 “自動增長”（如每次增長 100MB，而非 “按百分比增長”），避免頻繁小幅度增長導(dǎo)致碎片化。

3. 常見錯誤與解決方案

總結(jié) 

到此這篇關(guān)于SQL SERVER數(shù)據(jù)庫日志文件收縮的文章就介紹到這了,更多相關(guān)sql server日志文件收縮內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論