👋 我是三笠，一名专注数据库运维与架构的 DBA，平时分享数据库实战经验与技术笔记，个人网站：ora100.com

前言

最近处理了一套 SQL Server 测试数据库，Windows 系统盘突然告警，C 盘只剩下不到 200MB。

检查后发现：数据库的数据文件只有 30GB 左右，事务日志文件却增长到了 348GB。

继续排查，发现数据库一直使用 FULL 恢复模式，但已经很长时间没有执行事务日志备份。

最后通过调整恢复模式、截断日志和收缩文件，成功释放了 300 多 GB 磁盘空间，顺便还做了一个自动维护任务。

本文记录一下整个处理过程。

C 盘只剩 199MB，空间被谁占用了？

接到告警后，先登录 Windows 服务器查看磁盘空间。

查看磁盘使用情况：

可以发现 C 盘基本已经满了。

使用 TreeSize 扫描磁盘，发现 SQL Server 安装目录占用了大量空间。

继续往下看，其中一个数据库的 LDF 文件达到了 348.6GB，而对应的 MDF 数据文件只有 30.4GB，一个事务日志文件，竟然比数据文件大了十多倍。

这显然不太正常。

日志文件为什么会增长到 348GB？

先连接 SQL Server，检查所有数据库的事务日志使用情况：

DBCC SQLPERF(LOGSPACE);

重点关注异常数据库：

日志文件已经达到约 348GB，内部使用率接近 100%。

说明当前不仅 LDF 文件很大，内部也几乎没有可复用的空间。

接着检查数据库的恢复模式和日志重用等待原因：

SELECT
    name,
    recovery_model_desc,
    log_reuse_wait_desc,
    state_desc
FROM sys.databases
WHERE name = 'APPDB';

结果：

name    recovery_model_desc  log_reuse_wait_desc  state_desc
APPDB   FULL                 LOG_BACKUP            ONLINE

这里重点看两个字段：

• recovery_model_desc = FULL：说明数据库使用完整恢复模式。
• log_reuse_wait_desc = LOG_BACKUP：说明当前需要事务日志备份才能推进日志截断。

看到这里，基本有方向了。

再查一下最近一次事务日志备份是什么时候：

SELECT
    database_name,
    MAX(backup_finish_date) AS LastLogBackup
FROM msdb.dbo.backupset
WHERE database_name = 'APPDB'
  AND type = 'L'
GROUP BY database_name;

结果发现，上一次记录的事务日志备份已经是两年多以前。

也就是说，这套数据库长期处于 FULL 恢复模式，却没有正常执行事务日志备份。

这就能解释为什么日志一直增长。

在 FULL 恢复模式下，事务日志通常需要通过日志备份推进截断。如果长期不备份，已提交事务产生的日志记录也可能一直无法重用。

随着业务不断写入，LDF 文件就会持续增长。

当然，FULL 模式本身并不会导致日志无限增长，真正的问题是恢复策略与备份策略没有配套。

测试库不需要时间点恢复，怎么处理？

确认这是一套测试数据库后，处理思路就比较明确了。

这套数据库没有时间点恢复要求，也没有依赖原有日志备份链的高可用或日志传送配置。因此，决定将数据库从 FULL 恢复模式调整为 SIMPLE。

如果是生产数据库，这一步就不能这么随意。

生产库通常应该先确认日志备份链、恢复目标和日志重用阻塞原因，再决定处理方式。

首先修改恢复模式：

USE master;
GO

ALTER DATABASE [APPDB]
SET RECOVERY SIMPLE;
GO

执行成功后，切换到目标数据库执行 CHECKPOINT：

USE [APPDB];
GO

CHECKPOINT;
GO

这里有一点需要注意：

日志截断不等于日志文件收缩。

日志截断只是让不再需要的日志记录所占用的空间可以重新利用，并不会自动将 LDF 文件缩小。

如果希望把空间还给操作系统，还需要执行 SHRINKFILE。

348GB 的 LDF，直接收缩到 10GB

先确认日志文件的逻辑名称：

USE [APPDB];
GO

SELECT
    name,
    type_desc,
    physical_name,
    size * 8.0 / 1024 AS SizeMB
FROM sys.database_files;

查询结果：

确认之后，执行收缩：

USE [APPDB];
GO

DBCC SHRINKFILE
(
    N'APPDB_Log',
    10240
);
GO

这里的 10240 单位是 MB，也就是将日志文件的目标大小设置为 10GB。

执行完成后，再次检查日志空间：

DBCC SQLPERF(LOGSPACE);

结果：

可以看到，日志文件从约 348GB 缩小到了 10GB，日志使用率也从 99.98% 降到了 0.06%。

这次收缩比较顺利，没有遇到日志末尾 VLF 无法释放的问题。

不过需要说明，SHRINKFILE 的目标大小并不保证一定能够达到。如果仍有活动日志或文件末尾存在无法释放的 VLF，就可能无法继续收缩。

另一套数据库，也存在同样的问题

处理完 APPDB 后，又检查了一下其他数据库。

发现还有一套 TESTDB，日志文件也增长到了约 36.8GB。

查询结果显示：

Recovery Model : FULL
Log Reuse Wait : LOG_BACKUP
Log Used       : 99.90%

和 APPDB 的情况基本一致。

确认同样属于不需要时间点恢复的测试数据库后，采用相同方式处理：

USE master;
GO

ALTER DATABASE [TESTDB]
SET RECOVERY SIMPLE;
GO

USE [TESTDB];
GO

CHECKPOINT;
GO

DBCC SHRINKFILE
(
    N'TESTDB_Log',
    10240
);
GO

执行完成后，TESTDB 的日志也缩小到了约 10GB。

至此，两套数据库的日志空间都处理完成。

日志收缩完成，还要调整自动增长

空间释放出来后，还需要检查日志文件的自动增长配置：

SELECT
    name,
    size * 8.0 / 1024 AS SizeMB,
    growth * 8.0 / 1024 AS GrowthMB,
    is_percent_growth
FROM sys.master_files
WHERE database_id = DB_ID('APPDB')
  AND type_desc = 'LOG';

检查发现，原来的日志自动增长大小为 64MB。

对于这套测试环境，决定将其调整为固定 512MB：

ALTER DATABASE [APPDB]
MODIFY FILE
(
    NAME = N'APPDB_Log',
    FILEGROWTH = 512MB
);
GO

另一套 TESTDB 也做了相同调整，这里没有采用百分比增长，而是固定增长大小。

不过，512MB 并不是所有 SQL Server 数据库的标准配置，实际还需要结合日志生成量、业务高峰和磁盘容量确定。

每次手动处理太麻烦，顺手做个自动维护任务

这次虽然把空间释放出来了，但考虑到是测试环境，后面还可能有批量导入、数据清理或者其他测试操作。

如果日志文件再次增长到几十 GB，总不能每次都登录服务器手动处理。

于是利用 SQL Server Agent 做了一个简单的定时维护任务。

我没有选择每天强制收缩，而是增加了一些判断条件：

只有满足条件，才执行收缩，不满足条件就直接跳过。

这样可以避免每周无意义地收缩日志文件，也减少日志文件反复收缩、增长带来的开销。

将维护逻辑封装成存储过程后，由 SQL Server Agent 调用：

EXEC master.dbo.usp_DBA_LogMaintenance;
GO

然后创建定时任务：

Job Name:
DBA_Weekly_Log_Maintenance

Schedule:
Every Sunday 03:00

Target:
APPDB / TESTDB

创建完成后，手动测试任务。

第一次执行时，SQL Server 返回：

Msg 22022

SQLServerAgent is not currently running
so it cannot be notified of this action.

原来 SQL Server Agent 服务没有启动。

通过 Windows 服务管理器启动 SQL Server Agent，并将启动类型调整为 Automatic。

再次执行：

USE msdb;
GO

EXEC dbo.sp_start_job
    @job_name = N'DBA_Weekly_Log_Maintenance';
GO

这次返回：

Job started successfully.

最后检查任务执行历史：

SELECT TOP 20
    j.name AS JobName,
    h.step_id,
    h.run_status,
    h.message
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j
    ON h.job_id = j.job_id
WHERE j.name = 'DBA_Weekly_Log_Maintenance'
ORDER BY h.instance_id DESC;

Job 和 Step 均显示 SUCCEEDED，说明 SQL Server Agent 已经能够正常调度维护过程。

需要注意，任务成功不代表每次都实际发生了收缩。本次测试时日志文件已经低于 20GB，因此维护过程会按条件跳过。

最后，磁盘空间恢复了多少？

整个处理完成后，再次查看 Windows 磁盘空间：

整个处理过程没有停止 SQL Server 服务，也没有直接删除 LDF 文件。

C 盘从几乎没有剩余空间，恢复到了数百 GB 可用空间。

最后再说两句。

这次问题的关键，其实不是 SHRINKFILE，而是前面查到的 LOG_BACKUP。

LDF 文件很大，只是现象；日志为什么不能重用，才是需要先搞清楚的问题。

另外，定时收缩只是这套测试环境的兜底方案，并不代表 SQL Server 应该定期收缩日志。正常情况下，还是应该根据恢复要求配置备份策略，并合理规划日志容量。

写文章之外，我也在持续完善自己的数据库学习与运维平台 ORA100。

平时整理的数据库实战案例、常用 SQL、运维脚本和实用工具，都可以在这里找到。

网站地址：ora100.com

感兴趣的话，可以来看看。