如何确定SQL事务日志文件的良好初始大小？

Question

我们使用SQL Server 2008 R2。数据库大小在20-500 gb之间，并且全部处于完整恢复模式下。

我发现很难确定事务日志文件的良好初始大小，以避免自动增长。这取决于多个因素，但有没有人可以给出一个关于日志的初始大小的规则？我能看到日志在过去xx时间内增长的速度吗？

对此主题的任何建议都非常欢迎...谢谢！

- Erik Dekker

---

你有什么信息？数据库已经投入生产运行一段时间了吗？现在的日志配置是什么？ - usr

更大的数据库已经在生产中运行了几年。每个数据库的日志配置都不同，这就是为什么我想让它更加一致，并且我正在寻找良好的初始大小。（一个日志文件为200GB，另一个为25GB，目前没有好的线索）。 - Erik Dekker

Answer 1

您需要在一段时间内收集元数据，以了解日志文件的增长模式。可以将以下查询设置为作业，以获取并填充包含元数据的表格，然后您将能够了解您的初始大小需要是多少。

SELECT GETDATE() AS DateRun 
     ,(size * 8.0)/1024.0 AS size_in_mb
     , CASE
  WHEN max_size                                 = -1 
  THEN 9999999                  -- Unlimited growth, so handle this how you want
  ELSE (max_size * 8.0)/1024.0                  END AS max_size_in_mb
  FROM YOURDBNAMEHERE.sys.database_files
 WHERE data_space_id                            = 0 

- Raj

Answer 2

你需要避免自动增长，因为它会引入延迟和负载峰值。小的自动增长增量也会导致虚拟日志文件碎片化。但是，自动增长应该作为安全保障而启用。

目标是开启自动增长，但实际上不要让它发生。第二个目标是不浪费空间并且具有快速恢复时间。日志无法立即初始化。

当我调整日志大小时，我会将其大小调整到正常操作期间自动增长事件的几率很低。如果必须量化，我会说每年10％的几率是相当安全的标准。我将增长增量设置为较低的值，以避免在需要自动增长时出现延迟峰值（甚至超时）。

确定在两个日志备份之间最多生成多少日志空间。可能，您希望在高峰负载时测量每小时的增长率并添加安全裕度。您可以通过记录日志文件中分配的空间的注意事项来粗略地测量增长率。

一些维护操作也可以非常快速地生成大量日志。您也需要测量这一点。

关键是根据备份计划测量两个日志备份之间的峰值日志生成。

如果你想要一个更快、不那么科学的解决方案：从一个小日志开始，让它自动增长一个相对较小的增量（256MB？），看看一周后它变得有多大。然后“重新格式化”日志以消除VLF碎片（收缩，然后设置最终大小加上安全余量）。

- usr