什么是SQL Server事务日志中的虚拟日志文件？

什么是SQL Server事务日志文件？ (What is a SQL Server transaction log file?)

SQL Server事务日志文件是每个SQL Server数据库的组成部分。 带有扩展名MDF的数据文件和带有扩展名LDF的事务日志文件一起构成了SQL Server数据库。

A SQL Server database can have more than one data file. These additional data files have an NDF extension. But there can be only one active LDF. Physically, there can be additional LDF files but this is only recommended in specific situations. In either case, SQL Server can only use one LDF file at the time.

一个SQL Server数据库可以有多个数据文件。 这些其他数据文件具有NDF扩展名。 但是只能有一个活动的LDF。 从物理上讲，可以有其他LDF文件，但是仅在特定情况下才建议这样做。 无论哪种情况，SQL Server一次只能使用一个LDF文件。

SQL Server事务日志有什么作用？ (What does a SQL Server transaction log do?)

A SQL Server transaction log keeps all transactions that occur in a SQL Server database. With every database transaction, a log record is written into the transaction log sequentially. The purpose of the SQL transaction log is to bring back old values if ever needed, which can be useful in disaster recovery situations. Depending on the recovery model, the transaction log file is maintained differently.

SQL Server事务日志保留SQL Server数据库中发生的所有事务。 对于每个数据库事务，都将日志记录顺序写入事务日志中。 SQL事务日志的目的是在需要时恢复旧值，这在灾难恢复情况下很有用。 根据恢复模型，事务日志文件的维护方式有所不同。

SQL Server中有哪些可用的恢复模型？ (What are the available recovery models in SQL Server?)

There are three recovery models available in SQL Server: Simple, Full, and Bulk logged.

SQL Server中提供了三种恢复模型：简单，完整和批量记录。

The Full recovery model is the most suitable for production systems where data loss is unacceptable. The old transactions are marked for reuse after a transaction log backup. The SQL transaction log can grow unless it is maintained properly, i.e. backed up regularly.

完全恢复模型最适用于数据丢失不可接受的生产系统。 备份事务日志后，将旧事务标记为可重用。 除非对其进行适当的维护（即定期备份），否则SQL事务日志可能会增长。

The Bulk logged recovery model is used when there is a need for large bulk operations such as bulk import or index recreation. It is designed to minimally log most bulk operations and save the amount of space needed for the SQL transaction log file. This recovery model can be used with the Full model when bulk operations are planned. Switching between these two recovery models doesn’t break the transaction log backup chain.

当需要大量批量操作（例如批量导入或索引重新创建）时，可以使用批量日志记录恢复模型。 它旨在最小程度地记录大多数批量操作，并节省SQL事务日志文件所需的空间量。 当计划批量操作时，此恢复模型可以与完整模型一起使用。 在这两种恢复模型之间切换不会中断事务日志备份链 。

The Simple recovery model if often used in testing and development environments, where data loss is not an issue. The older transactions are marked for reuse and will be overwritten with new ones automatically. In this recovery model, there is a little chance for transaction log file growth because the file is self-maintained. If a disaster occurs, there is a chance of data loss. Also, this recovery model is suitable for data warehouses with read-only data.

如果通常在测试和开发环境中使用简单恢复模型，那么数据丢失就不会成为问题。 较旧的事务被标记为可重复使用，并将自动被新事务覆盖。 在此恢复模型中，事务日志文件增长的可能性很小，因为该文件是自维护的。 如果发生灾难，则可能会丢失数据。 而且，此恢复模型适用于具有只读数据的数据仓库。

如何编写SQL Server事务日志？ (How is a SQL Server transaction log written?)

When using the Full recovery model, if the transactions fill up the transaction log file, SQL Server continues to write the new transactions from the beginning of the transaction log file, but only if the transaction log backup is taken and the old transactions are marked for deletion. If the transaction log backup hasn’t been taken, the transaction log file is forced to expand and grow enough to accommodate newly executed transactions. This operation is performance intensive.

使用完全恢复模型时，如果事务填满了事务日志文件，则SQL Server会继续从事务日志文件的开头写入新事务，但前提是必须进行事务日志备份并且将旧事务标记为删除。 如果尚未进行事务日志备份，则将强制事务日志文件扩展和增长到足以容纳新执行的事务的程度。 此操作是性能密集型的。

If the SQL Server transaction log file can’t grow due to a limited free space on the hard drive, SQL Server will report the following error: Error: 9002, Severity: 17, State: 2.

如果由于硬盘驱动器上的可用空间有限而导致SQL Server事务日志文件无法增长，则SQL Server将报告以下错误：错误：9002，严重性：17，状态：2。

If there is more than one transaction log file, SQL Server will move through all the files first, before it starts to write new transactions to the first file. Maximum size for a transaction log file is 2 terabytes.

如果存在多个事务日志文件，则SQL Server将在开始将新事务写入第一个文件之前先浏览所有文件。 事务日志文件的最大大小为2 TB。

什么是虚拟日志文件– VLF？ (What are virtual log files – VLF?)

Each SQL Server transaction log file is made of smaller parts called virtual log files. The number of virtual log files is not limited or fixed per transaction log file. Also, there is no fixed size of virtual log file, if one is e.g. 512 kilobytes, all other will be of the same size.

每个SQL Server事务日志文件由称为虚拟日志文件的较小部分组成。 每个事务日志文件的虚拟日志文件数不受限制或固定。 另外，虚拟日志文件没有固定大小，如果一个文件大小为512 KB，则其他文件大小相同。

SQL Server determinates the size of a virtual log file dynamically when the transaction log file is created or extended. The goal is to maintain the small number of the virtual log files in the transaction log file, because SQL Server handles the smaller number of files easier. The size or number of virtual log files can’t be configured or set by a database administrator.

创建或扩展事务日志文件时，SQL Server动态确定虚拟日志文件的大小。 目标是在事务日志文件中维护少量的虚拟日志文件，因为SQL Server可以更轻松地处理较少数量的文件。 数据库管理员无法配置或设置虚拟日志文件的大小或数量。

By default, the SQL Server transaction log file is set at an initial size of 2MB. Also, the default growth value is 10% of the current size. While creating a SQL Server database, these options can be modified to accommodate planned needs for the database. The auto-growth option is optional and turned on by default. File growth can be specified in megabytes or percent. There is also the clause to limit the maximum file size. By default, SQL Server creates a database with unrestricted file growth.

默认情况下，SQL Server事务日志文件的初始大小设置为2MB。 同样，默认增长值是当前大小的10％。 在创建SQL Server数据库时，可以修改这些选项以适应数据库的计划需求。 自动增长选项是可选的，默认情况下处于启用状态。 可以以兆字节或百分比指定文件增长。 还有一个子句可以限制最大文件大小。 默认情况下，SQL Server创建的文件增长不受限制的数据库。

If the auto-growth settings are not properly managed, a SQL Server database can be forced to auto-grow, which can cause serious performance issues. SQL Server will stop all processing until the auto-grow event is finished. Due to physical organization of the hard drive, the auto-growth event will take up the space which is not close physically to the previous one occupied by the transaction log file. This leads to the physical fragmentation of the files which also causes slower response.

如果未正确管理自动增长设置，则可能会强制SQL Server数据库自动增长，这可能会导致严重的性能问题。 SQL Server将停止所有处理，直到自动增长事件完成为止。 由于硬盘的物理组织，自动增长事件将占用与事务日志文件占用的上一个物理位置并不接近的空间。 这导致文件的物理碎片化，这也导致响应速度变慢。

There is no general rule how to determine the best values for the auto-growth option, as these vary from case to case. Having too many or too little virtual log files causes bad performance.

没有通用规则确定如何为自动增长选项确定最佳值，因为这些值因情况而异。 虚拟日志文件太多或太少都会导致性能下降。

There isn’t an option in SQL Server Management Studio which can provide the number of virtual log files. The virtual log files can be shown via T-SQL script for each SQL Server database. There will be other blog post which describes this topic in detail.

SQL Server Management Studio中没有一个选项可以提供虚拟日志文件的数量。 可以通过T-SQL脚本为每个SQL Server数据库显示虚拟日志文件。 还将有其他博客文章详细描述此主题。

The number of virtual log files can be increased by an auto-grow event, this process is common, but it needs strict rules to avoid unplanned problems with space or unresponsiveness in peak hours. The number of virtual log files can be decreased by shrinking the SQL Server transaction log file, which also requires strict rules to avoid deleting the data which hasn’t been backed up yet.

可以通过自动增长事件来增加虚拟日志文件的数量，此过程很常见，但是它需要严格的规则，以避免出现计划外的空间问题或高峰时段的无响应性。 可以通过缩小SQL Server事务日志文件来减少虚拟日志文件的数量，这也需要严格的规则，以避免删除尚未备份的数据。

翻译自: https://www.sqlshack.com/virtual-log-files-sql-server-transaction-log/