SQL SERVER数据库日志文件收缩图文详解

-- 0. 查询数据库 db1 的日志文件逻辑名称
select 
    name as 日志文件逻辑名称,  -- 逻辑名称（收缩时需用此名称）
    physical_name as 日志文件物理路径,  -- 物理文件路径（可确认文件位置）
    size/128.0 as 当前大小_mb,  -- 转换为 mb（sql server 中 size 单位是 8kb 页）
    fileproperty(name, 'spaceused')/128.0 as 已使用大小_mb  -- 计算实际使用空间
from 
    sys.database_files  -- 系统视图，存储数据库文件信息
where 
    type = 1;  -- type=1 表示日志文件，type=0 表示数据文件

    
-- 1. 将数据库 db1 的恢复模式设置为“简单”
alter database db1 
set recovery simple;  -- 未加 with no_wait，默认会等待数据库锁释放（适合单库操作，避免直接报错）

-- 2. 收缩 db1 的日志文件（需替换为步骤 0 查询到的日志文件逻辑名称）
dbcc shrinkfile (
    n'db1_log',  -- 第一个参数：日志文件逻辑名称（n 表示 unicode 字符串，避免中文/特殊字符问题）
    truncateonly  -- 第二个参数：仅截断未使用的尾部空间，不移动日志数据
);

-- 3. 将数据库 db1 的恢复模式设置为“完整”，并添加 with no_wait 选项
alter database db1 
set recovery full 
with no_wait;  -- 若数据库被其他进程锁定（如查询/备份），不等待直接报错（适合脚本自动化，避免无限等待）

declare @dbs table (dbname nvarchar(128));
insert into @dbs (dbname)
values 
    ('db1'),   
    ('db2');  
--tip：再次维护需要收缩的数据库名称

declare @currentdb nvarchar(128);
declare @logfilename nvarchar(128);
declare @sql nvarchar(max);

--使用游标循环处理各个数据库@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 '开始处理数据库：' + @currentdb;

    begin try
        --1.切换数据库为简单恢复模式
        set @sql = n'alter database ' + quotename(@currentdb) + n' set recovery simple with no_wait;';
        exec sp_executesql @sql;
        print @currentdb + ' 已切换为简单恢复模式';

        --2.查询日志文件逻辑名称
        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.切换回完整恢复模式
        set @sql = n'alter database ' + quotename(@currentdb) + n' set recovery full with no_wait;';
        exec sp_executesql @sql;
        print @currentdb + ' 已切换回完整恢复模式';

    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 '所有数据库处理完毕';