SQL 问与答： 日志的座右铭
事务日志是任何 SQL Server 实例的基本组件。 适当且有效地管理它们同样很重要。
Paul S. Randal
快速增长
**问：**我一直成功使用联机索引重建因为 SQL Server 2005 中推出。 我一直还使用大容量日志记录恢复模型以减少碎片索引时的事务日志增长量。 我们最近升级到 SQL Server 2008年和事务日志现在比以往更增长。 您可以解释发生了什么情况？
**答：**索引重建操作之前始终 SQL Server 2005 的重建操作期间获取阻塞锁。 重建聚集的索引意味着独占表锁 （无并发读取器或编写器）。 重建非聚集索引意味着共享的表锁 （没有写入并发程序表）。
随着企业版的 SQL Server 2005 中的联机索引操作，锁定出于已更改了的在线操作。 阻塞锁了仅举行的短时间的开始和结束的操作 （请参阅此博客张贴内容说明）。
在 SQL Server 2005，所有索引重建操作都使用最少的日志记录。 这意味着仅页的分配会记录，而不是所有的行插入到新的索引。 这将大大减少生成的索引重建操作的事务日志记录的数量。 这还意味着事务日志文件本身不一定是最大的 （因为它不必去适应完整记录的索引重建操作）。 使用大容量日志记录或简单恢复模型时，才会出现此行为。 完全恢复模型中完全记录所有操作 — 因此名称。
2008 SQL Server 年起，从联机索引操作都已被完全记录在所有恢复模型中更改。 这可确保涵盖了联机索引操作其事务日志中的数据库还原操作将不会遇到任何问题。 （有关此详细信息，请参阅 Microsoft 知识文库文章 2407439。）
如果限制事务日志增长比索引重建期间允许并发表访问更重要，遗憾的是您需要恢复到脱机索引重建得到的最小日志记录行为。 要考虑的另一件事使用改变索引 …若要删除碎片重组。 这始终运行而不会导致阻塞，可以减少事务日志量。 （有是重建和重新组织，在以下答案之一进行比较）。
您的备份进行大容量
**问：**我们已经一直在努力设计一个与我们每夜数据导入过程一致，所以我们完全备份包含导入过程中的所有数据的备份战略。 我们还没有已经能够计算出可靠的方法，要这样做 — — 有时数据都是存在，有时它都有。 您可以帮助吗？
**答：**您应声明的"有时数据是所有存在，并且有时它都有。这样，我认为您做作为一个大型事务您导入过程。
如果这是您正在执行的操作，则可以考虑将其拆分为较小的事务。 这将使 SQL 更多潜在的清除之间导入交易记录的事务日志和较长期阻塞。 一个长时间运行、 单交易记录的过程可能会导致锁升级为独占表锁。 您可能无法更改导入过程中，但应这样做如果您知道如何操作。
完整备份分两个阶段： 读取数据和读取事务日志。 当它已完全读取数据时，它将然后读取事务日志从回尽最大的点是必要 （请参阅我 2009 年 7 月的文章，"了解 SQL Server 备份，"有关更多详细信息）。
当您还原完整备份，以点还原数据库是从该处完成备份的数据读取部分的时间。 事务日志实质上是包含在备份中还原的数据库上运行故障恢复。 这使得事务一致性。
您必须回滚任何事务传输的数据在那时，就像普通的故障恢复。 如果在导入过程时仍在运行时完成的数据读取部分，它将被回滚还原操作过程中。
若要保证特定事务的唯一方法是完全备份中包含是序列化操作，以便在问题中的事务完成之前备份开始。 否则，没有办法知道在事务完成之前数据读取已完成。
当然没有序列化操作的替代方法。 在导入过程完成后，请执行事务日志备份。 然后恢复完整备份再加上的事务日志备份。 这将包含整个导入过程事务。
重新生成或重新组织
**问：**我正在设计索引维护策略。 我不明白为什么有两种方法可以删除索引碎片的原因。 如何确定是否要重新建立我们索引或重新组织它们？ 找不到列表中的两种方法之间找到平衡。
**答：**已删除碎片，因为我写了 DBCC INDEXDEFRAG SQL Server 2000 的两种方法。 这两种方法是必需的因为它们具有完全不同的特征。 遗憾的是，没有充分描述了 SQL Server 2005 中的三种方法开始之间的区别没有白皮书： 更改索引 …重新组织 （新 DBCC INDEXDEFRAG），改变索引 …重建 （新 DBCC DBREINDEX） 和在线版本更改索引的 …重新生成。
这是更改索引选项重建和重新组织快速比较：
- 重建需要除去旧之前建立新的索引。 这意味着必须有足够的可用空间以容纳新的索引 ； 在数据库中 否则，数据库将不断增长，以提供所需的可用空间。 这可能会出现问题，对于大型的索引。 重新组织只需要 8 KB 的数据库中的额外空间。
- 重建可以使用多个 Cpu，因此该操作运行速度更快。 重新组织始终以单线程。
- 重建可能需要的表，可以限制并发操作的长期锁定。 重新组织不会持有阻塞锁 （它始终是一项在线操作）。
- 重建可以使用最小日志记录来减少事务日志增长。 重新组织将始终完整地记录，但不会妨碍清除事务日志。
- 重建将自动重建所有索引列统计信息，而重新组织根本不会更新统计信息。
因此，有几种折衷磁盘空间要求，锁定，日志记录和并行度的角度。 要考虑的最重要的事情是两个操作的算法区别。
索引重建始终将重建整个索引，而无需考虑的碎片的程度。 这意味着轻轻碎片索引中的索引重建实际上是一种浪费。 现有的碎片将只负责索引重新组织。 这使得从一个轻轻零碎的索引，删除碎片更好的选择，但从碎片索引删除碎片糟糕的选择。 在这种情况会更好一些，只需重建索引。
这将导致我们的重建和重新组织之间进行选择的各种阈值。 有基于 sys.dm_db_index_physical_stats 的输出中的 avg_fragmentation_in_percent 列的广泛使用的碎片阈值：
- 0%到 10%： 不采取任何操作
- 10%到 30%： 使用更改索引 …重新组织
- 30%或更高版本： 使用更改索引 …重建
这些是一般指导原则，您可能会发现不同的值在您的环境中更好地工作。 您还需要考虑您是否在使用数据库镜像，这要求完全恢复模型。 这意味着将完全记录索引重建操作。 许多人发现这会产生太多事务日志镜像的主体和镜像之间高效地发送。 在这种情况下，它可以更好地重新组织索引，以最小化事务日志。
您还可以考虑使用其他人的索引维护脚本来节省时间。 Ola Hallengren 有一些全面、 最广泛使用的脚本。
日志大小的 Ups and Downs
**问：**我刚刚已经成为 DBA。 我遇到麻烦我们的开发人员为某些新数据库的设置。 我有此问题找出事务日志应该多大。 我选择，无论大小它变大并保持这种方式。 如果缩小它，它仍然增长到相同大小再次。 有一种创建数据库时正确设置大小吗？
**答：**首先，不要经常会收缩事务日志。 当事务日志需要增长时，事务日志分配的新空间必须初始化为零。 这意味着等待生成日志记录的事务可能需要等待此初始化发生时。
如果您在 shrink-grow 中，收缩-增长周期正在使用事务日志中，从而导致不必要的性能下降 SQL Server。 最好将事务日志保留在其所需的大小。 收缩应该是数据或事务日志的极少数运算。
您可以估计基于数据库操作的事务日志的大小。 阅读的有关的在我 2011 年 1 月的专栏中。 如果每个收缩后，事务日志增长到稳定的大小，这很可能是它应具有的大小。 停用该这样的大小，除非它是真正太大。 如果太大，确定什么导致增长，因此事务日志都有机会清除是否可以将拆分为多个部分的操作。
.jpg)
Paul S. Randal为管理主任 SQLskills.com，Microsoft 区域主管和 SQL Server MVP。 从 1999 年到 2007 年，他一直在 Microsoft 的 SQL Server 存储引擎团队工作。 他曾编写过 DBCC CHECKDB/repair for SQL Server 2005，并在 SQL Server 2008 的开发过程中负责核心存储引擎部分的工作。 Randal 是灾难恢复、高可用性和数据库维护方面的专家，经常在全球出席一些会议。 您可以访问他的博客 SQLskills.com/blogs/paul，也可以通过 Twitter Twitter.com/PaulRandal 了解他。