我要评论一、为什么需要收缩日志文件？
在 sql server 中，事务日志文件（.ldf）会记录所有数据库事务操作（如增删改、事务提交 / 回滚），用于故障恢复和数据一致性保障。但在以下场景中，日志文件可能会异常膨胀：
- full 恢复模式下未定期备份日志：日志会持续累积事务记录，无法自动释放空间；
- 长事务未提交：如长时间运行的 update/delete 语句，会锁定日志片段，导致无法截断；
- 数据库镜像 / 复制配置异常：日志记录因同步延迟被占用，无法正常回收。
日志文件过度膨胀会占用大量磁盘空间，甚至导致磁盘满额、数据库性能下降。此时需通过 “收缩操作” 释放未使用的空间，但需注意：收缩仅适用于 “临时清理空间”，需先排查膨胀根源（如完善备份计划），避免频繁操作导致文件碎片化。
二、可视化操作（ssms 界面）
适用于windows系统、新手或单数据库少量操作，以 sql server 2012（版本 11.0）、数据库 db1 为例，核心步骤分三步：切换恢复模式→收缩日志→恢复原模式。
1. 将恢复模式调整为 “简单”
简单恢复模式（simple）的核心特点是 “事务日志自动截断”—— 检查点（checkpoint）后，会自动释放已提交事务的日志空间，无需手动备份日志，这是后续收缩日志的前提（full 模式下日志无法直接截断）。
- 打开 sql server management studio（ssms），在 “对象资源管理器” 中找到目标数据库
db1，右键点击，选择 “属性（r）”； - 在 “数据库属性 - db1” 窗口的左侧 “选择页” 中，点击 “选项”；
- 在右侧 “恢复模式（m）” 下拉框中，将默认的 “完整” 改为 “简单”；
- 点击 “确定” 保存设置，此时数据库会立即切换到简单恢复模式。
2. 收缩数据库日志文件
切换到简单模式后，日志中未使用的空间已标记为 “可回收”，需通过 “收缩文件” 操作释放磁盘空间。
操作步骤：
- 右键点击
db1数据库，选择 “任务（t）”→“收缩（s）”→“文件（f）”； - 在 “收缩文件 - db1” 窗口中，进行以下配置：
- 文件类型（t）：下拉选择 “日志”（默认是 “数据”，需手动切换，避免收缩 .mdf 数据文件）；
- 文件名（f）：自动显示当前数据库的日志文件（如
db1_log），无需修改； - 收缩操作：选择 “释放未使用的空间（r）”（仅释放未使用的尾部空间，不移动日志数据，对性能影响最小）；
- 不建议选择 “将文件收缩到（k）”：该选项会强制将日志压缩到指定大小（如 3mb），可能导致日志数据页重组，产生大量碎片化，影响后续事务性能；
- 不建议选择 “通过将数据迁移到同一文件组中的其他文件来清空文件（e）”：仅适用于删除日志文件的场景，常规收缩无需使用；
- 点击 “确定”，ssms 会执行收缩操作，此时日志文件中未使用的空间会被释放。
3.将恢复模式调整回“完整”。
简单恢复模式虽便于收缩日志，但仅支持 “恢复到最近完整备份”，无法实现 “时间点恢复”（如恢复到故障前 10 分钟的数据），不符合生产环境对数据安全性的要求。因此收缩完成后，需立即切回完整恢复模式。
操作步骤：
- 重复上述“1. 将恢复模式调整为 “简单””的 1-2 步，打开 “数据库属性 - db1” 的 “选项” 页；
- 将 “恢复模式” 从 “简单” 改回 “完整”，点击 “确定”；
- 关键补充：切换回完整模式后，需立即执行一次 “完整备份”（右键
db1→“任务”→“备份”，选择 “完整” 备份类型），否则后续的日志备份会失败 —— 因为简单模式会断裂 “日志链”，完整备份是重建日志链、保障时间点恢复能力的前提。
三、代码操作（t-sql）
适用于批量操作（如多数据库同时收缩）或自动化脚本（如通过作业定期执行），相比可视化操作更高效、可复用。代码分为 “单数据库” 和 “多数据库” 两种场景，核心逻辑与可视化操作一致：查日志名→切简单模式→收缩日志→切完整模式。
1. 单数据库收缩（以 db1 为例）
0. 前置步骤：查询日志文件逻辑名称
收缩日志前，需先确认目标数据库的日志文件逻辑名称，避免因名称错误导致收缩失败。
-- 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. 切换到简单恢复模式
-- 1. 将数据库 db1 的恢复模式设置为“简单” alter database db1 set recovery simple; -- 未加 with no_wait，默认会等待数据库锁释放（适合单库操作，避免直接报错）
2. 收缩日志文件
-- 2. 收缩 db1 的日志文件（需替换为步骤 0 查询到的日志文件逻辑名称）
dbcc shrinkfile (
n'db1_log', -- 第一个参数：日志文件逻辑名称（n 表示 unicode 字符串，避免中文/特殊字符问题）
truncateonly -- 第二个参数：仅截断未使用的尾部空间，不移动日志数据
);代码解释：
dbcc shrinkfile：sql server 内置命令，用于收缩单个数据库文件（数据或日志），相比dbcc shrinkdatabase（收缩整个数据库）更精准；truncateonly：核心参数，仅释放 “已标记为可回收” 的未使用空间，不会修改日志数据的存储结构，性能损耗极低；若省略此参数，默认会先移动数据页再截断空间，可能导致碎片化。
3. 切换回完整恢复模式
-- 3. 将数据库 db1 的恢复模式设置为“完整”，并添加 with no_wait 选项 alter database db1 set recovery full with no_wait; -- 若数据库被其他进程锁定（如查询/备份），不等待直接报错（适合脚本自动化，避免无限等待）
补充说明：
with no_wait：若当前数据库有长事务或备份操作，会立即返回错误（如 “无法对数据库 'db1' 放置锁”），需先终止占用进程再执行；- 若希望 “低优先级等待”，可替换为
with wait_at_low_priority (wait_duration_seconds = 10)：表示等待 10 秒，若仍无法获取锁则报错，兼顾效率与容错。
2. 多数据库批量收缩
当需要同时收缩多个数据库时，用 “游标 + 动态 sql” 实现循环处理，同时添加错误捕获（可根据需要将执行记录保存到日志表中），避免单个数据库失败导致整个脚本中断。
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 '所有数据库处理完毕';四、知识延伸
1. 为什么收缩前必须切换恢复模式？
- full 模式：日志会完整记录所有事务，即使事务提交，未备份的日志也会保留（用于时间点恢复），无法截断未使用空间，此时
dbcc shrinkfile无效； - simple 模式：事务提交后，日志仅保留 “崩溃恢复必需的信息”，检查点会自动标记未使用日志为 “可回收”，此时收缩才能释放空间。
2. 生产环境收缩日志的注意事项
- 避免业务高峰期执行：收缩操作会产生 io 开销，若在高峰期执行，可能导致数据库响应延迟；建议在凌晨或低峰期执行；
- 收缩后必做完整备份：切换回 full 模式后，日志链已断裂，需立即执行完整备份，否则后续日志备份会失败，无法实现时间点恢复；
- 不建议定期收缩：频繁收缩会导致日志文件碎片化（日志数据分散在多个磁盘块中），后续事务写入时需频繁寻址，降低性能；正确做法是 “排查日志膨胀根源”（如完善日志备份计划，设置每 15-30 分钟备份一次日志）；
- 监控日志文件大小：通过 ssms 的 “数据库→属性→文件”，设置日志文件的 “自动增长”（如每次增长 100mb，而非 “按百分比增长”），避免频繁小幅度增长导致碎片化。
3. 常见错误与解决方案
| 错误现象 | 原因 | 解决方案 |
|---|---|---|
执行 alter database 时提示 “无法对数据库放置锁” | 数据库被其他进程占用（如长事务、备份、查询） | 1. 用 sp_who2 查询占用进程的 session_id；2. 若为无关查询，用 kill session_id 终止；3. 若为备份，等待备份完成后再执行 |
dbcc shrinkfile 执行后日志大小无变化 | 1. 日志中仍有活动事务；2. 未切换到简单模式 | 1. 执行 dbcc opentran(@currentdb) 查看未提交事务，终止后重试；2. 确认恢复模式已切换为 “简单” |
| 切换回 full 模式后日志备份失败 | 未执行完整备份，日志链断裂 | 立即执行一次 “完整备份”，再执行日志备份 |
总结
到此这篇关于sql server数据库日志文件收缩的文章就介绍到这了,更多相关sql server日志文件收缩内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论