使用SSMS收缩SQL Server日志文件失败，求可用解决代码

1. 先排查日志无法收缩的原因 #

执行以下查询，确认数据库恢复模式、日志阻塞原因及日志文件的空间使用情况：

-- 查看数据库恢复模式与日志重用等待原因
SELECT 
    name AS DatabaseName,
    recovery_model_desc AS RecoveryModel,
    log_reuse_wait_desc AS LogReuseWaitReason
FROM sys.databases 
WHERE name = '你的数据库名'; -- 替换为实际数据库名

-- 查看日志文件的大小、已用空间和可用空间
SELECT 
    name AS LogFileName,
    size/128.0 AS CurrentSizeMB,
    FILEPROPERTY(name, 'SpaceUsed')/128.0 AS UsedSpaceMB,
    (size - FILEPROPERTY(name, 'SpaceUsed'))/128.0 AS FreeSpaceMB
FROM sys.database_files 
WHERE type_desc = 'LOG';

2. 针对不同场景的解决代码 #

场景1：日志未备份（LogReuseWaitReason 显示 LOG_BACKUP） #

如果数据库处于完整/大容量日志恢复模式，未备份的日志无法被截断，需先备份再收缩：

-- 1. 备份事务日志（替换为实际备份路径）
BACKUP LOG 你的数据库名 TO DISK = 'D:\Backups\你的数据库名_LogBackup.bak';

-- 2. 收缩日志到目标大小（例如收缩到100MB）
DBCC SHRINKFILE(你的日志文件名, 100);

场景2：允许临时切换恢复模式（非核心业务或可接受事务丢失） #

临时切换到简单恢复模式可快速释放日志空间，之后可恢复原模式：

-- 1. 切换到简单恢复模式
ALTER DATABASE 你的数据库名 SET RECOVERY SIMPLE;

-- 2. 收缩日志到目标大小
DBCC SHRINKFILE(你的日志文件名, 100); -- 替换为目标大小

-- 3. 恢复原恢复模式（若需要）
ALTER DATABASE 你的数据库名 SET RECOVERY FULL; -- 或原有的BULK_LOGGED模式

场景3：存在未完成的事务（LogReuseWaitReason 显示 ACTIVE_TRANSACTION） #

先排查并终止未完成的事务（谨慎操作，可能导致数据丢失）：

-- 查询未完成的事务及关联会话
SELECT 
    tat.transaction_id,
    tat.name AS TransactionName,
    tat.begin_time,
    tds.session_id
FROM sys.dm_tran_active_transactions tat
JOIN sys.dm_tran_session_transactions tds ON tat.transaction_id = tds.transaction_id;

-- 终止无用的会话（替换为查询到的session_id）
KILL 123;

-- 事务结束后执行收缩
DBCC SHRINKFILE(你的日志文件名, 100);

注意事项 #

• 请将代码中的你的数据库名和你的日志文件名替换为实际名称（可通过第一步的查询结果获取）
• 生产环境中切换恢复模式前，务必评估业务影响；完整恢复模式下，必须先备份日志再操作
• 频繁收缩日志会导致日志文件碎片化，建议合理配置日志文件的自动增长参数，从根源减少收缩需求

内容的提问来源于stack exchange，提问作者rcpkhy