SQL Server日志被填满的可能原因

 

转载自http://support.microsoft.com/kb/110139/zh-cn

 

SQL Server 事务日志可能会处于填满状态，这将阻止在数据库中继续进行 UPDATE、DELETE 或 INSERT 活动，包括 CHECKPOINT。通常为错误 1105：

Can't allocate space for object syslogs in database dbname because the logsegment is full. If you ran out of space in syslogs, dump the transaction log. Otherwise use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

这可能会发生在任何数据库（包括 master 或 tempdb）上。本文讨论导致错误 1105 的那些问题的可能原因和解决方案。如果您的事务日志已满，并且当前收到错误 1105，则需要使用 DUMP TRANSACTION 语句清空该日志。有关使用 DUMP TRANSACTION 的更多信息，请参见 SQL Server 文档。

 

 

真正的关系数据库（如 Microsoft SQL Server）的基本特征在于事务完整性。任何事务都必须完全原子化（即功能上不可见），所有更改必须全部应用或全不...

真正的关系数据库（如 Microsoft SQL Server）的基本特征在于事务完整性。任何事务都必须完全原子化（即功能上不可见），所有更改必须全部应用或全不应用，即使出现系统故障时也应如此。在用户定义的事务中，以 BEGIN TRANSACTION 和 COMMIT TRANSACTION 语句开头和结尾的所有语句必须是全部应用或全不应用。在隐式事务中，每个单一 SQL 语句都被视为一个原子单位。

在生产环境中出现电源故障、操作系统崩溃等情况时，利用此功能，SQL Server 数据库可以在系统重新启动后自动恢复到先前的状态，而无需人为干预。这一点与非关系数据库系统形成对比，在出现系统故障后，非关系数据库系统通常需要长时间的手动过程来检查数据库的一致性问题。

事务日志正是提供此功能的机制。由于事务完整性被视为 SQL Server 的基本本质特征，所以不能禁用日志记录功能。某些实用工具或维护操作（如快速 BCP 和 SELECT INTO）的日志记录量最小，但这些日志也能进行一定程度的定位，使回滚成为可能。

进行日志记录所需的空间可能非常大。例如，大多数情况下，必须记录每个更新数据行的更新前映像和更新后映像，以及所有受影响的索引行的更新前后映像。由于必须为每个日志记录行记录固定数量的事务记录开销，所以已更新数据与日志空间使用量的比率会随着行宽度的不同而变化。对于窄行，特定 UPDATE、DELETE 或 INSERT 所使用的日志空间量可能相当于所使用的数据空间的十倍。对于宽行，使用的日志空间量成比例减小。提供事务完整性不可避免的结果就是要使用日志空间。数据库管理员必须为其特定安装提供足够的日志空间。

所需的日志空间量取决于多种因素，很难事先准确地预测。不过，通常会使用经验数据（如数据库大小的 15% 至 30%）作为设定日志大小的起点，实际情况中，这个数字的变动范围非常大。成功安装 SQL Server 的过程中通常会做一些简单的经验测试，以便大致评估特定数据和应用程序的日志空间要求，然后基于此值设定日志的大小。试图仅基于计算而并不进行测试来设定日志的大小非常困难，而且通常不准确。

有几个很难预测的因素导致日志空间使用量的变化很大。其中一个因素是查询优化器。对于给定的 SQL 数据修改语句，访问计划会随着时间而变化，具体取决于数据的统计分布。不同的访问计划会消耗不同的日志空间量。另一个因素是很难避免的内部数据库碎片，可能会影响页拆分的执行次数。但又无法执行任何操作来检查或影响此进程，因为 SQL Server 会自动为用户管理数据。

例如，可运行 DBCC CHECKTABLE(syslogs) 进行简单测试，从而返回执行数据修改查询代表性示例之前和之后日志中字节数为 2048 的数据页的数量。这可使您大体了解上述类型的查询所需的日志空间量。在为关系数据库（如 SQL Server）提供日志或数据磁盘空间时，通常最好多提供一些空间。

对于 SQL Server 7.0 和 2000 类服务器，事务日志具有按需扩展的功能。增加的数量可以由用户控制，也可以被允许为利用所有可用磁盘容量。日志文件由许多虚拟日志文件组成。这些虚拟日志文件的数量和大小是由 SQL Server 决定的，不能进行配置。当初次创建数据库时，每个物理日志文件最少具有 2 个虚拟日志文件。有时，数据库管理员会启用数据库的“在检查点截断日志”选项，以避免用尽日志空间。此选项的目的在于提供一种自动截断日志的方法，主要用于那些不依赖日志转储进行备份的开发数据库或测试数据库。此选项不禁用日志记录或事务完整性功能。它只会导致检查点处理程序大约每 60 秒钟尝试进行一次截断日志操作。请注意，在启用“在检查点截断日志”选项的情况下，在数据库中发出手动检查点命令时，日志不会被截断。对于 tempdb 数据库，此选项是始终启用的，即使在 sp_help 存储过程输出的状态栏中未指示为要启用时也是如此。

事务日志填满的可能原因

即使启用“在检查点截断日志”选项，仍然有许多因素可能会导致用尽日志空间。下面列出了这些可能的因素：

1. 大型原子事务（特别是批量 UPDATE、INSERT 或 DELETE）：每个单一 SQL 语句被视为一个原子单位，不论应用与否都必须作为一个整体。因此，必须记录所有行变动，并且在事务期间不能将其截断。例如，如果发出一个命令执行大批量 INSERT 操作，该操作需用时五分钟，则在操作期间此事务所用的日志不能被截断。数据库管理员必须为预期的最大批量操作提供足够的日志空间，或者必须在较小的组中执行批量操作。
2. 未提交的事务：只能在最早的未提交事务之前截断日志。有几种可能的原因会导致未提交的事务，其中大部分是应用程序错误。原因包括：

a.   大容量事务：由于上述的原因，在大容量事务期间，不能截断所生成的日志记录。不过，此类事务也会阻止同一期间提交的其他较短事务的日志被截断。

例如，假设数据库管理员设定的日志大小足以支持能想象到的最大的大容量事务。然而在此事务运行时，其他较短的数据修改语句也会使用日志空间。此日志空间不能被截断，因为大容量事务最先开始，因此成为最早的未提交事务。管理员必须意识到这种并发性和大容量事务的日志影响，从而恰当地设定日志的大小。

b.   设计糟糕的应用程序，这些应用程序允许在用户定义的事务内进行用户输入或其他比较耗时的活动：例如，在发出 BEGIN TRANSACTION 后，应用程序可能提示用户进行一个用时较长的输入（具体输入时间取决于用户）。在用户进行响应以及应用程序发出 COMMIT 命令前，不可能进行日志截断。

c.   未提交的事务中的应用程序错误：此情况的常见原因是在用户定义的事务内未正确处理 DB-Library 调用 dbcancel()。当使用 dbcancel() 取消查询时，当前执行的 SQL 语句将中止并回滚，但外部事务不会。应用程序必须意识到这一点，并发出必要的 ROLLBACK TRANSACTION 或 COMMIT TRANSACTION 语句以关闭该事务。如果未能执行此操作，通常会导致错误 3902：

The commit transaction has no corresponding BEGIN TRANSACTION.

对于要发送 SELECT @@TRANCOUNT 来确定事务嵌套级别的应用程序，此操作可能会有用。不过，应用程序不应该盲目地执行此操作，然后发出 COMMIT/ROLLBACK 以实现 @@TRANCOUNT=0。这是因为，如果 @@TRANCOUNT 始终与应用程序所预期的不同，则表示应用程序已经失去对事务嵌套级别的跟踪（这是应用程序设计错误）。此时发出 COMMIT/ROLLBACK 可能会导致错误地应用或中止事务，因为应用程序不知道哪个事务会导致不希望的事务级别。相反，程序员应该调试该应用程序和所有相关的存储过程，以确定出现不希望的事务级别的原因。

d.   未通知 SQL Server 出现了网络连接中断的网络错误：如果客户端工作站在用户定义的事务内挂起、重新启动或关闭，则网络层应该通知 SQL Server 所发生的情况。如果网络未能正确地完成此操作，则从 SQL Server 的角度来讲，客户端将被认为正确联机，来自该客户端的打开事务将保持。这是一个网络问题，必须从这方面着手处理。作为一种替代方法，管理员可以使用 sp_who、sp_lock 或网络实用工具来确定仍存在的客户端会话，并手动将其终止。

e.   由于受到阻塞，事务未提交：在多用户环境中，打开的事务可能被另一进程持有的锁阻塞。在这种情况下，事务仍将保持打开状态，阻止日志截断。要发现此情况，程序员或数据库管理员需要使用 sp_who、sp_lock 或其他工具对并发环境进行分析。大多数情况下，可以通过适当的查询、索引和数据库设计来减少或消除阻塞问题。

f.    尝试取消数据修改查询失败：如果应用程序发出 dbcancel()，但由于网络或 SQL 问题未能取消该查询，则该查询将继续运行，事务仍将处于打开状态。如果您怀疑有问题，请使用 sp_who 来查看是否取消了该查询。如果试图从 TCP/IP 套接字客户端取消查询，请从命名管道客户端进行该测试，或使用本地管道在服务器计算机上运行客户端应用程序。这将有助于查明是网络问题还是 SQL 问题阻止了取消。

          超出了检查点处理程序截断带宽：虽然每 60 秒钟截断一次日志，但截断速率是有限的。这种情况并不常见，应该先考虑日志溢出的其他可能原因并予以解决，如果不是再检查此种可能性。不过，如果许多客户端同时发出大量更新命令，则可能会超出最大截断速率。这种情况与只能以某一固定速率排出液体的漏斗相似，即使一直排放，漏斗还是会溢出液体。如果是这种情况，可以重构应用程序以减少要更新的行数，对于任何关系数据库，这始终应是主要的设计目标。

如果不可行，则可以重新配置系统，以通过带区、附加控制器等方式增加磁盘 I/O 带宽。在这种情况下，通常会看到检查点处理程序进程在 DUMP TRANSACTION 语句上花费的时间不断增加，与它试图与日志截断保持同步的情况类似。一旦超出截断阈值（参见下文），您可能看不到检查点处理程序在该数据库中曾尝试截断，除非清除日志。

          超出截断阈值：检查点处理程序本质上来说是执行 DUMP TRANSACTION WITH TRUNCATE_ONLY。就好像此语句是手动发出的一样，如果日志已经填满到某个点，则此语句不会总是成功执行。例如，在检查点处理程序访问期间，突然发生的更新活动可能会填充日志的 95%。当检查点处理程序尝试截断时，虽然日志未完全填满，但是对于要允许截断来说，仍然过于满。这是因为必须记录日志截断本身。在这种情况下，唯一的解决方案就是使用 DUMP TRANSACTION WITH NO_LOG 手动截断日志。除非绝对有必要，否则不建议使用 NO_LOG 选项，因为这是一个非日志记录操作（在此期间系统故障可能会引发数据库错误）。

          上述任何原因相互作用：例如，在正常情况下经常更新的环境中，检查点处理程序截断速率可以避免日志填满。如果上述任意条件（如锁争用）所引发的临时打开事务导致日志填充了 50%（假设），这样就会有较少的空余空间用于处理其他更新情况，从而更可能达到截断阈值（在这种情况下不可能进行自动截断）。对 tempdb 中的事务进行日志记录与任何其他数据库类似。由于 TRUNCATE LOG ON CHECKPOINT 在 tempdb 中执行，大多数情况下，日志将被截断并且不会溢出。不过，上述任意环境均可能导致 tempdb 日志填满。Tempdb 通常是针对混合日志和数据配置的 (sysusages.segmap=7)，所以数据和日志操作将争夺同一可用的空间。GROUP BY 和 ORDER BY DESC 等某些 Transact-SQL 构造将自动为 tempdb 请求工作空间。这还会在作为工作空间的 tempdb 中产生隐式 BEGIN TRANSACTION 记录。在用户数据库的事务期间，此 tempdb 事务将继续，这可能会推迟此期间的 tempdb 日志截断。如果用户数据库中的事务因某种原因（包括阻塞锁）停止，或者应用程序未能完成对 dbnextrow() 的处理，则 tempdb 中的事务也可能保持打开状态，从而阻止截断 tempdb 日志。程序员必须对该应用程序进行调试和/或解决导致此情况的并发问题。

          SQL Server 7.0 和 2000 类服务器中事务日志的截断是通过截断虚拟日志文件来实现的。如果活动日志的任意部分驻留在给定的 VLF 中，则虚拟日志文件不能被截断。如果活动日志驻留在所有虚拟日志文件中，则不能截断该日志。如果启用了自动增长，并且在事务日志驻留的卷上有空间，且未达到最大文件大小，则事务日志将按日志文件属性中指定的数量增长。

 

 

下面讨论 SQL 启动时根据是否设置了 TRUNCATE LOG ON CHECKPOINT 来进行日志截断的行为。

• 如果设置了 TRUNCATE LOG ON CHECKPOINT 且在启动时发现日志是填满的，将使用 no_log 自动转储日志。
• TRUNCATE LOG ON CHECKPOINT 是 master 中的默认值，因为其日志不能放置于单独设备中，所以永远不能加载。唯一可行的选项就是在日志填满时丢弃该日志。
• 如果未设置 TRUNCATE LOG ON CHECKPOINT，且在启动时发现日志是填满的，恢复可以完成，但不会写入最终检查点。管理员可以进入数据库，并使用 no_truncate 转储日志以保存数据，然后使用 no_log 进行转储以清除日志（或仅仅将其清除）。