// SQL Server 错误参考

SQL Server 错误 9002:数据库的事务日志已满

数据库的日志文件已满且无法增长,写入因此失败。消息中的原因(LOG_BACKUP、ACTIVE_TRANSACTION 等)说明了是什么在阻止日志重用。

错误编号
9002
严重性
17
类别
内存、日志与存储
错误信息
The transaction log for database '%ls' is full due to '%ls' and the holdup lsn is %S_LSN.

它的意思

SQL Server 在更改数据之前会将每个更改写入事务日志。日志空间只有在不再需要时才会被重用:在完整恢复模式下,这意味着日志备份之后,而在所有模式中,最旧的未完成事务、复制或可用性组副本都可能占用它。如果日志无法重用且无法增长(已达到最大大小或磁盘已满),每次写入都会失败并显示 9002。

消息会指出原因,这个值与 log_reuse_wait_desc in sys.databases.

常见原因

  • LOG_BACKUP: 数据库处于完整恢复模式或大容量日志恢复模式,并且没有在进行日志备份。
  • ACTIVE_TRANSACTION: 一个长时间运行或被遗忘的打开事务拖住了日志。
  • REPLICATION 或者 AVAILABILITY_REPLICA: 日志读取器或辅助副本已落后。
  • 日志文件已达到固定最大大小,或存放日志的磁盘已满。
  • 一个超大事务,例如用一条语句删除数百万行。

如何修复它

  1. 查看日志无法重用的原因。

    SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = N'SalesDb';
  2. 对于 LOG_BACKUP,请先备份一次日志(并安排定期执行)。

    BACKUP LOG SalesDb TO DISK = N'D:\Backup\SalesDb_log.trn';
  3. 对于 ACTIVE_TRANSACTION,请找到最早的未完成事务并将其提交或回滚。

    DBCC OPENTRAN (N'SalesDb');
  4. 给日志留出空间:让它增长,或在另一块磁盘上添加第二个日志文件。

    ALTER DATABASE SalesDb MODIFY FILE (NAME = SalesDb_log, MAXSIZE = UNLIMITED, FILEGROWTH = 512MB);
  5. 把大量数据修改拆分成批次,让每个事务都保持很小。

    WHILE 1 = 1
    BEGIN
        DELETE TOP (10000) FROM dbo.EventLog WHERE CreatedAt < '2024-01-01';
        IF @@ROWCOUNT = 0 BREAK;
    END;
  6. 如果你不需要对该数据库进行时间点恢复,简单恢复模式会在每个检查点后重用日志空间。

    ALTER DATABASE SalesDb SET RECOVERY SIMPLE;

问题

修复 9002 错误后,我应该收缩日志文件吗?

仅当日志因一次性事件增长到远超正常大小时才这么做。平时就需要这么大空间的日志,收缩只会让它再次长大,既费时间又让写入干等。

相关错误

更多内存、日志和存储错误

代码和消息文本来自 Microsoft 数据库引擎事件和错误参考。浏览完整的 SQL Server 错误列表 或者 搜索每个数据库错误.

// 错误更少,修复更快

在 SQLly 中使用 SQL Server

适用于 macOS、Windows 和 Linux 的原生 SQL 客户端,智能感知能理解你的数据库模式,高危语句有防护,错误信息清晰,支持 SQL Server 以及你使用的其他数据库。