您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 交易記錄壓縮與收縮完整預存程序：DUMP TRAN、SHRINKDATABASE、AUTO_SHRINK、離線刪除 LDF 重建

編輯：關於SqlServer

預存程序 p_compdb 完整碼

CREATE PROC p_compdb
    @dbname SYSNAME,                    -- 要壓縮的資料庫名
    @bkdatabase BIT = 1,                -- 是否先備份資料庫 (離線刪除 LDF 有風險)
    @bkfname NVARCHAR(260) = ''         -- 備份檔名，空則自動產生 (資料庫名_yyyymmddhhmmss)
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 清空交易記錄 (舊版語法，SQL 2005+ 建議用 BACKUP LOG ... WITH NO_LOG 已移除，改用簡單模式切換)
    EXEC('DUMP TRANSACTION [' + @dbname + '] WITH NO_LOG');
    
    -- 2. 截斷交易記錄
    EXEC('BACKUP LOG [' + @dbname + '] WITH NO_LOG');
    
    -- 3. 收縮資料庫檔案 (資料檔與記錄檔)
    EXEC('DBCC SHRINKDATABASE([' + @dbname + '])');
    
    -- 4. 設定自動收縮 (生產環境不建議開啟，易造成碎片與效能抖動)
    EXEC('EXEC sp_dboption ''' + @dbname + ''', ''autoshrink'', ''TRUE''');

    -- 5. 選擇性：離線備份、分離、刪除 LDF、重新掛載單一 MDF (高風險，需完整備份為前提)
    IF @bkdatabase = 1
    BEGIN
        IF ISNULL(@bkfname, '') = ''
            SET @bkfname = @dbname + '_' + CONVERT(VARCHAR, GETDATE(), 112) 
                           + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '');
        PRINT '備份資料庫至 SQL 預設備份目錄，檔案名：' + @bkfname;
        EXEC('BACKUP DATABASE [' + @dbname + '] TO DISK = ''' + @bkfname + '''');
    END

    -- 分離資料庫
    CREATE TABLE #files (fname NVARCHAR(260), ftype INT);
    EXEC('INSERT INTO #files SELECT filename, type = status & 0x40 FROM [' + @dbname + ']..sysfiles');
    EXEC('sp_detach_db ''' + @dbname + '''');

    -- 刪除記錄檔 (ftype=64 為 log)
    DECLARE @fname NVARCHAR(260), @cmd VARCHAR(8000);
    DECLARE cur CURSOR LOCAL FOR SELECT fname FROM #files WHERE ftype = 64;
    OPEN cur;
    FETCH NEXT FROM cur INTO @fname;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @cmd = 'DEL "' + RTRIM(@fname) + '"';
        EXEC master..xp_cmdshell @cmd, NO_OUTPUT;
        FETCH NEXT FROM cur INTO @fname;
    END
    CLOSE cur; DEALLOCATE cur;

    -- 重新掛載單一資料檔 (自動建立新 LDF)
    SET @cmd = '';
    DECLARE cur2 CURSOR LOCAL FOR SELECT fname FROM #files WHERE ftype = 0;
    OPEN cur2;
    FETCH NEXT FROM cur2 INTO @fname;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @cmd = @cmd + ',''' + RTRIM(@fname) + '''';
        FETCH NEXT FROM cur2 INTO @fname;
    END
    CLOSE cur2; DEALLOCATE cur2;
    EXEC('sp_attach_single_file_db ''' + @dbname + '''' + @cmd);
    
    DROP TABLE #files;
END
GO

⚠️ 高風險操作警示

DUMP TRAN ... WITH NO_LOG / BACKUP LOG ... WITH NO_LOG：SQL Server 2008 已移除，破壞交易記錄鏈，導致無法進行時間點還原。現代版本請改用：
① 切換至 SIMPLE 復原模式 → DBCC SHRINKFILE (N'LogFile', 目標大小) → 切回 FULL 並立即完整備份。
② 或直接 DBCC SHRINKFILE (N'LogFile', 目標大小) 在 FULL 模式下 (需先備份記錄)。
AUTO_SHRINK = TRUE：極不推薦，背景執行緒頻繁收縮造成索引碎片、CPU 飆升、I/O 暴增。生產環境應關閉，改由維護計畫定期手動收縮 (如有必要)。
離線分離、刪除 LDF、重新掛載：極高風險！若 MDF 損壞、備份失效、權限不足、磁碟空間不足，資料庫將永久損壞。僅限測試環境或極度緊急且有完整驗證可用備份時使用。
xp_cmdshell 執行 DEL：需啟用 Ole Automation 與 xp_cmdshell，安全性風險極高，生產環境禁用。

現代最佳實務：安全收縮記錄檔

-- 1. 確認復原模式
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'YourDB';

-- 2. 完整備份 (FULL 模式下必須先備份記錄才能收縮)
BACKUP DATABASE YourDB TO DISK = '...';
BACKUP LOG YourDB TO DISK = '...';

-- 3. 收縮記錄檔至合理大小 (如 500MB)
DBCC SHRINKFILE (N'YourDB_Log', 500);

-- 4. 如需長期控制大小，設定記錄檔最大值與成長增量 (避免頻繁自動成長)
ALTER DATABASE YourDB MODIFY FILE (NAME = N'YourDB_Log', MAXSIZE = 2048MB, FILEGROWTH = 128MB);