收缩SQL Server的数据库日志文件

1. 收缩单个数据库的日志文件

USE [master]
GO
ALTER DATABASE [数据库名] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [数据库名] SET RECOVERY SIMPLE --简单模式
GO
USE [数据库名]
GO
DBCC SHRINKFILE (N'数据库名_Log' , 1, TRUNCATEONLY) --1表示1024kb
GO

USE [master]
GO
ALTER DATABASE [数据库名] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [数据库名] SET RECOVERY FULL --还原为完全模式
GO

2. 批量收缩数据库的日志文件

这个脚本会遍历 SQL Server 实例中的所有用户数据库（排除系统库），并按以下逻辑收缩日志文件：

• 将数据库恢复模式设为 SIMPLE（截断不活动的日志）
• 收缩日志文件到目标大小（可自定义）
• 恢复原有的恢复模式（避免破坏业务备份策略）

-- 声明变量
DECLARE 
    @DBName NVARCHAR(128),          -- 数据库名称
    @TargetSizeMB INT = 1,        -- 日志文件目标大小（MB），可根据需要修改
    @RecoveryModel NVARCHAR(60),    -- 保存原恢复模式
    @SQL NVARCHAR(MAX);            -- 动态SQL语句

-- 游标遍历所有用户数据库（排除系统数据库）
DECLARE db_cursor CURSOR FOR
SELECT name 
FROM sys.databases 
WHERE 
    [name] NOT IN('master','model','msdb','tempdb')  -- 排除master/model/msdb/tempdb
    AND database_id > 4                -- 排除master/model/msdb/tempdb
    AND state = 0                  -- 数据库处于在线状态
    AND is_read_only = 0;          -- 非只读数据库

-- 打开游标
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

-- 循环处理每个数据库
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        -- 1. 获取当前数据库的恢复模式
        SELECT @RecoveryModel = recovery_model_desc
        FROM sys.databases WHERE name = @DBName;

        PRINT N'=============================================';
        PRINT N'正在处理数据库：' + @DBName;
        PRINT N'原恢复模式：' + @RecoveryModel;

        -- 2. 切换到SIMPLE模式（截断日志）
        SET @SQL = 'ALTER DATABASE [' + @DBName + '] SET RECOVERY SIMPLE WITH NO_WAIT';
        EXEC sp_executesql @SQL;

        -- 3. 收缩日志文件到目标大小
		--
		--  脚本中用 2 代表日志文件（默认情况下，数据文件是 1，日志文件是 2）
		--
        SET @SQL = 'USE [' + @DBName + ']; 
                    DBCC SHRINKFILE (2, ' + CAST(@TargetSizeMB AS NVARCHAR) + ');';
        EXEC sp_executesql @SQL;

        -- 4. 恢复原恢复模式
        SET @SQL = 'ALTER DATABASE [' + @DBName + '] SET RECOVERY ' + @RecoveryModel + ' WITH NO_WAIT';
        EXEC sp_executesql @SQL;

        PRINT N'数据库 ' + @DBName + ' 日志文件已收缩至 ' + CAST(@TargetSizeMB AS NVARCHAR) + ' MB';
    END TRY
    BEGIN CATCH
        -- 捕获异常并打印错误信息
        PRINT N'处理数据库 ' + @DBName + ' 失败：' + ERROR_MESSAGE();
    END CATCH

    -- 获取下一个数据库
    FETCH NEXT FROM db_cursor INTO @DBName;
END

-- 关闭并释放游标
CLOSE db_cursor;
DEALLOCATE db_cursor;

PRINT N'=============================================';
PRINT N'批量收缩日志文件完成！';

3. 关键说明

3.1 目标大小调整
修改 @TargetSizeMB 的值（默认 1MB），设置你想要的日志文件大小。

3.2 排除特定数据库
如果不想收缩某些数据库，可在游标查询中添加排除条件，例如：

AND [name] NOT IN ('DB1', 'DB2', 'DB3')  -- 排除不需要收缩的数据库

3.3 日志文件编号

脚本中用 2 代表日志文件（默认情况下，数据文件是 1，日志文件是 2）。
如果你的数据库日志文件编号不是 2，可先执行以下语句确认：

-- 查看指定数据库的文件信息
USE 你的数据库名;
SELECT file_id, name, type_desc FROM sys.database_files;

3.4 权限要求
执行脚本的账号需要具备 ALTER ANY DATABASE 和 VIEW SERVER STATE 权限，建议用 sa 或系统管理员账号执行。

4. 注意事项

生产环境谨慎操作： 建议在业务低峰期执行，收缩过程会消耗 IO 资源，可能影响数据库性能。