解决事务日志已满的问题(SQL Server 错误 9002)Troubleshoot a Full Transaction Log (SQL Server Error 9002)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题讨论对已满事务日志可以采取的几种应对措施,并就以后如何避免出现已满事务日志给出建议。This topic discusses possible responses to a full transaction log and suggests how to avoid it in the future.

如果事务日志已满,则 SQL Server 数据库引擎SQL Server Database Engine 会发出 9002 错误When the transaction log becomes full, SQL Server 数据库引擎SQL Server Database Engine issues a 9002 error. 当数据库联机或恢复时,日志可能会满。The log can fill when the database is online, or in recovery. 如果数据库联机时日志已满,则数据库保持联机状态,但是只能进行读取而不能更新。If the log fills while the database is online, the database remains online but can only be read, not updated. 如果恢复过程中日志已满,则 数据库引擎Database Engine 将数据库标记为 RESOURCE PENDING。If the log fills during recovery, the 数据库引擎Database Engine marks the database as RESOURCE PENDING. 不管哪种情况,都需要用户执行操作才能使日志空间可用。In either case, user action is required to make log space available.

对已满事务日志的响应Responding to a full transaction log

正确响应已满事务日志在某种程度上取决于导致日志已满的情况。The appropriate response to a full transaction log depends partly on what condition or conditions caused the log to fill.

若要在给定情况下查找阻止日志截断的原因,请使用 sys.database 目录视图的 log_reuse_wait 列和 log_reuse_wait_desc 列。To discover what is preventing log truncation in a given case, use the log_reuse_wait and log_reuse_wait_desc columns of the sys.database catalog view. 有关详细信息,请参阅 sys.databases (Transact-SQL)For more information, see sys.databases (Transact-SQL). 有关延迟日志截断的因素的说明,请参阅事务日志 (SQL Server)For descriptions of factors that can delay log truncation, see The Transaction Log (SQL Server).

重要说明!!IMPORTANT!!
如果数据库在恢复过程中出现 9002 错误,则在解决此问题后,可使用 ALTER DATABASE database_name SET ONLINE 恢复数据库。If the database was in recovery when the 9002 error occurred, after resolving the problem, recover the database by using ALTER DATABASE database_name SET ONLINE.

响应已满事务日志的备选方法包括:Alternatives for responding to a full transaction log include:

  • 备份日志。Backing up the log.

  • 释放磁盘空间以便日志可以自动增长。Freeing disk space so that the log can automatically grow.

  • 将日志文件移到具有足够空间的磁盘驱动器。Moving the log file to a disk drive with sufficient space.

  • 增加日志文件的大小。Increasing the size of a log file.

  • 在其他磁盘上添加日志文件。Adding a log file on a different disk.

  • 完成或取消长时间运行的事务。Completing or killing a long-running transaction.

下列部分介绍了这些备选方法。These alternatives are discussed in the following sections. 请选择最适用于您情况的响应。Choose a response that fits your situation best.

备份日志Back up the log

在完整恢复模式或大容量日志恢复模式下,如果最近尚未备份事务日志,则请立即进行备份以免发生日志截断。Under the full recovery model or bulk-logged recovery model, if the transaction log has not been backed up recently, backup might be what is preventing log truncation. 如果从未备份日志,则 必须创建两个日志备份 ,以允许 数据库引擎Database Engine 将日志截断到上次的备份点。If the log has never been backed up, you must create two log backups to permit the 数据库引擎Database Engine to truncate the log to the point of the last backup. 截断日志可释放空间以供新的日志记录使用。Truncating the log frees space for new log records. 若要防止日志再次填满,请经常执行日志备份。To keep the log from filling up again, take log backups frequently.

创建事务日志备份To create a transaction log backup

重要事项IMPORTANT
如果数据库已损坏,请参阅结尾日志备份 (SQL Server)If the database is damaged, see Tail-Log Backups (SQL Server).

释放磁盘空间Freeing disk space

您可以通过删除或移动其他文件的方法来释放包含数据库事务日志文件的磁盘驱动器上的磁盘空间。You might be able to free disk space on the disk drive that contains the transaction log file for the database by deleting or moving other files. 释放磁盘空间后,恢复系统将自动扩大日志文件。The freed disk space allows the recovery system to enlarge the log file automatically.

将日志文件移至其他磁盘Move the log file to a different disk

如果在当前包含日志文件的驱动器上无法释放足够的磁盘空间,请考虑将该文件移至空间充足的其他驱动器上。If you cannot free enough disk space on the drive that currently contains the log file, consider moving the file to another drive with sufficient space.

重要说明!!IMPORTANT!! 日志文件决不要放在压缩文件系统中。Log files should never be placed on compressed file systems.

移动日志文件Move a log file

增加日志文件大小。Increase log file size

如果日志磁盘上具有可用空间,则可以增加日志文件的大小。If space is available on the log disk, you can increase the size of the log file. 日志文件的最大大小是每个日志文件 2 TB。The maximum size for log files is two terabytes (TB) per log file.

增加文件大小Increase the file size

如果禁用自动增长,数据库处于联机状态,并且磁盘上有足够的可用空间,则可采用以下方法之一:If autogrow is disabled, the database is online, and sufficient space is available on the disk, either:

  • 手动增加文件大小以生成单个增量。Manually increase the file size to produce a single growth increment.

  • 使用 ALTER DATABASE 语句启用自动增长以针对 FILEGROWTH 选项设置非零增量。Turn on autogrow by using the ALTER DATABASE statement to set a non-zero growth increment for the FILEGROWTH option.

请注意 不管哪种情况,如果已达到当前大小限制,则应增加 MAXSIZE 值。NOTE In either case, if the current size limit has been reached, increase the MAXSIZE value.

在其他磁盘上添加日志文件Add a log file on a different disk

使用 ALTER DATABASE <database_name> ADD LOG FILE,向具有足够空间的其他磁盘上的数据库中添加新日志文件。Add a new log file to the database on a different disk that has sufficient space by using ALTER DATABASE <database_name> ADD LOG FILE.

添加日志文件Add a log file

完成或终止长时间运行的事务Complete or kill a long-running transaction

发现长时间运行的事务Discovering long-running transactions

很长时间运行的事务会导致事务日志填满。A very long-running transaction can cause the transaction log to fill. 若要查看长时间运行的事务,请使用下列方法之一:To look for long-running transactions, use one of the following:

终止一个事务Kill a transaction

有时只是需要结束进程;此时可能需要使用 KILL 语句。Sometimes you just have to end the process; you may have to use the KILL statement. 请谨慎使用此语句,特别是在运行不想终止的重要进程时。Please use this statement very carefully, especially when critical processes are running that you don't want to kill. 有关详细信息,请参阅 KILL (Transact-SQL)For more information, see KILL (Transact-SQL)

另请参阅See also

KB 支持文章 - A transaction log grows unexpectedly or becomes full in SQL Server(SQL Server 中的事务日志意外增大或已满)ALTER DATABASE (Transact-SQL) KB support article - A transaction log grows unexpectedly or becomes full in SQL Server ALTER DATABASE (Transact-SQL)
管理事务日志文件的大小 Manage the Size of the Transaction Log File
事务日志备份 (SQL Server) Transaction Log Backups (SQL Server)
sp_add_log_file_recover_suspect_db (Transact-SQL)sp_add_log_file_recover_suspect_db (Transact-SQL)