SQL Server事务日志增长监视和管理

In the previous articles of this series on the SQL Server Transaction Log, we discussed the importance of the SQL Server Transaction Log and the role that it plays in maintaining the database consistency, by ensuring that the committing transactions data will be preserved and the failed transaction will be rolled back. It also helps to recover the database to a specific point in time in case of system or hardware failure. This is achieved by writing a log record to the SQL transaction log file before writing the data pages to the physical data file, using Write-ahead Logging process.

在本系列有关SQL Server事务日志的前几篇文章中，我们讨论了SQL Server事务日志的重要性及其在通过确保将保留提交的事务数据和失败的事务而保持数据库一致性方面所起的作用。将回滚。 如果系统或硬件出现故障，它还有助于将数据库恢复到特定的时间点。 这是通过使用预写日志记录过程在将数据页写入物理数据文件之前将日志记录写入SQL事务日志文件来实现的。

In addition, we discussed deeply the three Full, Simple and Bulk-Logged recovery models of the database, that specifies how these transactions will be logged and saved to the SQL Server transaction log file. In this article, we will see how to monitor the SQL Server Transaction Log growth and how to manage that growth.

此外，我们深入讨论了数据库的三种完整，简单和批量记录的恢复模型 ，这些模型指定了如何记录这些事务并将其保存到SQL Server事务日志文件中。 在本文中，我们将看到如何监视SQL Server事务日志的增长以及如何管理该增长。

SQL事务日志增长 (SQL Transaction Log Growth)

The SQL Server Database Engine writes a log record for every single operation in the database, that includes starting or ending a SQL transaction when a data modification process is performed, when a database table or index is created or dropped, and after each page allocation or deallocation operation. In the heavily transactional systems with excessive logging operations, the SQL Server Transaction Log file will grow rapidly, until it reaches its maximum size, generating error number 9002. And if the auto-growth option is enabled, the underlying disk drive will run out of free space.

SQL Server数据库引擎为数据库中的每个操作写入一条日志记录，包括在执行数据修改过程时，在创建或删除数据库表或索引时以及在每次页面分配或执行之后开始或结束SQL事务。释放操作。 在具有过多日志记录操作的高事务性系统中，SQL Server事务日志文件将快速增长，直到达到最大大小，并生成错误号9002 。 并且，如果启用了自动增长选项，则基础磁盘驱动器将耗尽可用空间。

Before going through the mechanisms that can be used to monitor and manage SQL Server Transaction Log growth, let us discuss the excessive logging operations that may lead the SQL Transaction Log file to grow rapidly and the operations that lock the transaction and prevent it from being reused by other transactions.

在探讨可用于监视和管理SQL Server事务日志增长的机制之前，让我们讨论可能导致SQL事务日志文件快速增长的过多日志记录操作，以及锁定事务并防止其被重用的操作。通过其他交易。

索引重建和重组 (Index Rebuild and Reorganize )

SQL Server index rebuild operation is a fully logged operations that use the Transaction Log excessively, especially when the recovery model of the database is FULL. It runs by default in a single long-running transaction, that prevents the Transaction Log space reuse while rebuilding a large index. If the recovery model of the database is changed to Simple or Bulk-Logged, the index rebuild operation will be a minimally logging operation.

SQL Server索引重建操作是完全记录的操作，过度使用了事务日志，尤其是在数据库的恢复模型为FULL时。 默认情况下，它在单个长时间运行的事务中运行，从而防止了在重建大型索引时重复使用事务日志空间。 如果将数据库的恢复模型更改为“简单”或“批量记录”，则索引重建操作将是最少记录操作。

The case in the index reorganizes operation is not the same. The index reorganizes operation is always fully logged, regardless of the recovery model setting of the database. You can imagine the SQL Transaction Log space that is required to reorganize a large index that is heavily fragmented. In all cases, reorganizing an index with a specific size requires less log space than rebuilding the same index.

索引重组操作的情况是不同的。 无论数据库的恢复模型设置如何，索引重组操作始终被完全记录。 您可以想象重新组织散乱的大索引所需SQL事务日志空间。 在所有情况下，与重建相同索引相比，重组具有特定大小的索引所需的日志空间更少。

SQL事务日志备份 (SQL Transaction Log Backup)

When the recovery model of the database is FULL or Bulk-Logged, which is the default recovery model of all newly created databases, unless you change the recovery model setting in the