事务日志在运行 SQL Server 的计算机上意外增大或充满

文章翻译 文章翻译
文章编号: 317375 - 查看本文应用于的产品
展开全部 | 关闭全部

本文内容

概要

在 SQL Server 7.0、SQL Server 2000 和 SQL Server 2005 中,使用自动增长设置,事务日志文件可自动扩展。

通常,如果事务日志文件还能容纳得下两次事务日志截断之间发生的最大数量的事务的话,则它的大小还是稳定的。事务日志截断由检查点或事务日志备份触发。

然而,在某些情况下,事务日志可能会变得非常大,以致用尽空间或变满。通常,在事务日志文件占尽可用磁盘空间且不能再扩展时,您将收到以下错误消息:
错误:9002, 严重性:17, 状态:2
数据库 '%.*ls' 的日志文件已满。
如果您使用的是 SQL Server 2005,您将收到与以下内容类似的错误消息:
错误:9002, 严重性:17, 状态:2
数据库 '%.*ls' 的事务日志已满。若要查明无法重用日志中的空间的原因,请参阅 sys.databases 中的 log_reuse_wait_desc 列。
除了出现此错误消息以外,SQL Server 还可能因缺少事务日志扩展而将数据库标记为“置疑”。有关如何从此情形中恢复的其他信息,请参见 SQL Server 联机丛书中的“Insufficient Disk Space”主题。

另外,事务日志扩展可能导致下列情形:
  • 非常大的事务日志文件。
  • 事务可能会失败并可能开始回滚。
  • 事务可能会用很长时间才能完成。
  • 可能发生性能问题。
  • 可能发生阻塞现象。

原因

事务日志扩展可能由于以下原因或情形而发生:

未提交的事务

如果不发出显式 COMMIT 或 ROLLBACK 命令,显式事务将不提交。这种情况最常发生在应用程序发出了 CANCEL 或 Transact SQL KILL 命令但未发出对应的 ROLLBACK 命令时。这时会发生事务取消,但不回退;这样,SQL Server 将不能截断此后发生的每一个事务,因为中止的事务仍处于打开状态。您可以使用 DBCC OPENTRAN Transact-SQL 参考来检查在某一特定时间数据库中是否有一个活动的事务。 有关此特定情形的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
295108 不完整事务可能控制了大量锁并导致阻塞
171224 INF:了解 Transact-SQL KILL 命令的工作原理
另外,请参见 SQL Server 联机丛书中的“DBCC OPENTRAN”主题。

可能导致未提交事务的情形
  • 假定所有错误都会导致回滚的应用程序设计。
  • 未充分考虑 SQL Server 在回滚到命名事务或特别嵌套的命名事务时的行为的应用程序设计。如果尝试回滚到内部命名的事务,您将收到以下错误消息:
    服务器:Msg 6401, Level 16, State 1, Line 13 无法回滚 InnerTran。没有找到任何该名称的事务或保存点。
    在 SQL Server 生成此错误消息之后,它将继续执行下一个语句。这是设计使然。有关更多信息,请参见 SQL Server 联机丛书中的“Nested Transactions”或“Inside SQL Server”主题。

    在设计应用程序时,Microsoft 为您提出以下建议:
    • 只打开一个事务单元(考虑到另一个进程调用您的进程的可能性)。
    • 在发出 COMMIT、ROLLBACK、RETURN 或者类似命令或语句之前检查 @@TRANCOUNT。
    • 编写代码时要假定有另一个 @@TRANCOUNT 可能“嵌套”您的 @@TRANCOUNT,并要计划发生错误时外层 @@TRANCOUNT 的回滚。
    • 检查事务的保存点和标记选项。(它们不能释放锁!)
    • 执行完整的测试。
  • 允许用户在事务内交互的应用程序。这会导致事务长时间保持打开,从而造成阻塞和事务日志增长,因为打开的事务不能被截断,而且又有新的事务添加到日志中打开的事务之后。
  • 不检查 @@TRANCOUNT 以验证是否没有打开的事务的应用程序。
  • 关闭客户端应用程序到 SQL Server 的连接而不通知 SQL Server 的网络错误或其他错误。
  • 连接池。创建工作线程后,如果这些线程不为连接提供服务,SQL Server 就会重用它们。如果一个用户连接启动了一个事务并在提交或回滚事务前断开,并且随后的一个连接重用了此同一线程,则前一事务仍保持打开状态。此情形将导致前一事务的锁保持打开状态,并且使日志中提交的事务无法被截断,从而导致较大的日志文件。有关连接池的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
    164221 INFO:如何在 ODBC 应用程序中启用连接池

非常大的事务

事务日志文件中的日志记录的截断是逐个事务进行的。如果事务的范围较大,此事务和任何在其后开始的事务只有在此事务完成后才能从事务日志中被删除。这可能会导致较大的日志文件。如果事务大到一定程度,日志文件就可能会用尽可用磁盘空间并导致“transaction log full”之类的错误消息(如 Error 9002)。本文的“更多信息”一节介绍了在收到此类错误消息时应如何处理的其他信息。另外,回滚较大的事务将占用大量的时间和 SQL Server 开销。

操作:DBCC DBREINDEX 和 CREATE INDEX

由于 SQL Server 2000 中的恢复模型发生了变化,在您使用“完整”恢复模式并运行 DBCC DBREINDEX 时,与在 SQL Server 7.0 中的对等恢复模式中使用 SELECT INTO 或 BULK COPY 并关闭“Trunc.Log on chkpt.”相比,事务日志的扩展速度明显快了很多。

尽管执行 DBREINDEX 操作之后事务日志的大小可能是一个问题,但此方法提供了较好的日志还原性能。

在从事务日志备份还原时

下面的 Microsoft 知识库文章对此作了介绍:
232196 INF:从备份还原后使用的日志空间似乎增大

如果将 SQL Server 2000 设置为使用“大容量日志”模式,并发出一个 BULK COPY 或 SELECT INTO 语句,则每一个更改的扩展盘区都将被标记并在备份事务日志时得到备份。尽管这样允许您备份事务日志,并且让您甚至在执行批量操作之后也能从故障中恢复,但这增加了事务日志的大小。SQL Server 7.0 中不包括此功能。SQL Server 7.0 只记录哪些扩展盘区发生了更改,但不记录实际的扩展盘区。因此,在“大容量日志”模式下,SQL Server 2000 中的日志记录比 SQL Server 7.0 中占用的空间要多出许多,但在“完整”模式下不会多这么多。

客户端应用程序不处理所有结果

如果您向 SQL Server 发出一个查询,但没有立即处理查询结果,则您可能会控制着一些锁并因而降低服务器的并发处理能力。

例如,假设您发出了一个查询,它需要两个页中的行来填充您的结果集。SQL Server 将分析、编译并运行此查询。这意味着共享锁放置在这两个包含必须用来满足查询的行的页上。另外,假设并不是所有的行都能放入一个 SQL Server TDS 包(服务器用此包与客户端通讯)中。TDS 包被填充后发送到客户端。如果第一页中的所有行都能放入 TDS 包中,SQL Server 将释放此页上的共享锁,但在第二页上仍保留一个共享锁。然后 SQL Server 等待客户端请求更多数据(例如,可以使用 DBNEXTROW/DBRESULTS、SQLNextRow/SQLResults 或 FetchLast/FetchFirst 进行请求)。

这意味着共享锁将继续保持,直到客户端请求了其余的数据为止。从第二页请求数据的其他进程可能会被阻塞。

查询在事务日志完成扩展之前超时,您收到假的“Log Full”错误消息

在这种情况下,尽管有足够的磁盘空间,您仍会收到“out of space”错误消息。

这一情形在 SQL Server 7.0 和 SQL Server 2000 中会有所不同。

在事务日志快要满时,一个查询可能会导致事务日志自动扩展。这可能需要额外的时间,而且查询可能会因此而停止或超时。SQL Server 7.0 在此情形下将返回错误 9002。SQL Server 2000 中不存在此问题。

在 SQL Server 2000 中,如果为一个数据库启用了自动收缩选项,那么在某个瞬间事务日志曾尝试自动扩展但却扩展不了,因为自动收缩功能同时也在运行。这也可能会导致出现假的错误 9002 实例。

一般情况下,事务日志文件的自动扩展很快即可完成。但是,在下列情况下,它占用的时间将比平时长:
  • 增长的增量太小。
  • 由于各种原因服务器太慢。
  • 磁盘驱动器不够快。

未复制的事务

如果您正在使用复制,publisher 数据库的事务日志的大小就会增大。影响被复制对象的事务标记为“For Replication”。这些事务(如未提交事务)在检查点之后或在备份事务日志之后将不被删除,直到日志读取器任务将事务复制到分发数据库并取消其标记时为止。如果日志读取器任务因出现问题而无法读取 publisher 数据库中的这些事务,则随着未复制事务数量的增加,事务日志的大小可能继续增大。您可以使用 DBCC OPENTRAN Transact-SQL 参考来确定最旧的未复制事务。

有关对未复制事务进行故障排除的更多信息,请参见 SQL Server 联机丛书中的“sp_replcounters”和“sp_repldone”主题。

有关其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
306769 FIX:不能截断包含快照发布的数据库的事务日志
240039 FIX:DBCC OPENTRAN 不报告复制信息
198514 FIX:恢复到新服务器导致事务保留在日志中

更多信息

任何数据库的事务日志都作为一组虚拟日志文件 (VLF) 进行管理,虚拟日志文件的大小由 SQL Server 根据日志文件的总大小和在日志扩展时使用的增量内部确定。日志总是以整个 VLF 为单位进行扩展,而且它只能压缩到一个 VLF 边界。VLF 有以下三种存在状态:ACTIVE(活动)、RECOVERABLE(可恢复)和 REUSABLE(可重用)。
  • ACTIVE:日志的活动部分从代表活动(未提交)事务的最小日志序列号 (LSN) 开始。日志的活动部分结束于最后写入的 LSN。包含活动日志的任何部分的任何 VLF 都被认为是活动 VLF。(物理日志中未使用的空间不是任何 VLF 的一部分。)
  • RECOVERABLE:日志中位于最旧的活动事务之前的部分只是用来为进行恢复而维护一个日志备份序列。
  • RESUABLE:如果您没有维护事务日志备份,或者您已经备份了日志,SQL Server 将重用最旧的活动事务之前的 VLF。
当 SQL Server 到达物理日志文件的末尾时,它将通过向文件的开头发出一个 CIRCLING BACK 操作而开始重用物理文件中的这一空间。事实上,SQL Server 回收了日志文件中对于恢复或备份不再必要的那部分空间。如果维护着一个日志备份序列,则日志中位于最小 LSN 前面的部分在您备份或截断这些日志记录之前不能被覆盖。在您执行日志备份之后,SQL Server 可以回到文件的开头重新开始记录。在 SQL Server 返回来在日志文件中较靠前的部分开始写日志记录后,日志的可重用部分将位于逻辑日志的末尾和日志的活动部分之间。

有关其他信息,请参见 SQL Server 联机丛书中“Transaction Log Physical Architecture”这一主题。另外,您还可以在“Inside SQL Server 7.0”(Soukup, Ron.Inside Microsoft SQL Server 7.0, Microsoft Press, 1999) 的第 190 页和“Inside SQL Server 2000”(Delaney, Kalen.Inside Microsoft SQL Server 2000, Microsoft Press, 2000) 的第 182-186 页中看到有关此内容的精美图表和详细论述。 SQL Server 7.0 和 SQL Server 2000 数据库具有自动增长和自动收缩的选项。您可以使用这些选项来帮助您压缩或扩展事务日志。

有关这些选项如何影响您的服务器的其他信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:
315512 INF:SQL Server 中的自动增长和自动收缩配置注意事项
事务日志文件的截断和压缩是不同的。当 SQL Server 截断一个事务日志文件时,意味着此文件中的内容(如已提交事务)将被删除。但是,当您从磁盘空间的角度(例如,在 Windows Explorer 中或使用 dir 命令)来查看此文件的大小时,此文件的大小保持不变。但是,.ldf 文件中的空间现在可以由新事务重用了。只有在 SQL Server 收缩事务日志文件的大小时,您才能实际看到日志文件的物理大小发生改变。

有关如何收缩事务日志的其他信息,请单击下面的文章编号,查看 Microsoft 知识库中相应的文章:
256650 INF:如何收缩 SQL Server 7.0 事务日志
272318 INF:使用 DBCC SHRINKFILE 在 SQL Server 2000 中收缩事务日志
有关 SQL Server 6.5 事务日志的使用率的其他信息,请单击下面的文章编号,查看 Microsoft 知识库中相应的文章:
110139 INF:SQL 事务日志填满原因

属性

文章编号: 317375 - 最后修改: 2013年7月16日 - 修订: 6.2
这篇文章中的信息适用于:
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL 2005 Server Enterprise
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL 2005 Server Workgroup
  • Microsoft SQL Server 2000 标准版
  • Microsoft SQL Server 7.0 标准版
关键字:?
kbsqldeveloper kbinfo KB317375
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性,不作任何声明。 所有该等文件及有关图形均"依样"提供,而不带任何性质的保证。Microsoft和/或其各供应商特此声明,对所有与该等信息有关的保证和条件不负任何责任,该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下,在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中,Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润不负任何责任。

提供反馈

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com