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

08/05/2016

本文内容

适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)

本主题讨论对已满事务日志可以采取的几种应对措施，并就以后如何避免出现已满事务日志给出建议。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. 有关延迟日志截断的因素的说明，请参阅事务日志 (SQL Server)。For descriptions of factors that can delay log truncation, see The Transaction Log (SQL Server).

重要

如果数据库在恢复过程中出现 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

释放磁盘空间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.

重要

日志文件决不要放在压缩文件系统中。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 值。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 ADD LOG FILE，向具有足够空间的其他磁盘上的数据库中添加新日志文件。Add a new log file to the database on a different disk that has sufficient space by using ALTER DATABASE 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:

此动态管理视图返回有关数据库级事务的信息。This dynamic management view returns information about transactions at the database level. For a long-running transaction, columns of particular interest include the time of the first log record (database_transaction_begin_time), the current state of the transaction (database_transaction_state), and the log sequence number (LSN) of the begin record in the transaction log (database_transaction_begin_lsn).

通过此语句，您可以标识该事务所有者的用户 ID，因此可以隐性地跟踪该事务的源以得到更加有序的终止(将其提交而非回滚)。This statement lets you identify the user ID of the owner of the transaction, so you can potentially track down the source of the transaction for a more orderly termination (committing it rather than rolling it back).

终止一个事务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. For more information, see KILL (Transact-SQL)

另请参阅See also