管理SQL Server事务日志

SQL Server databases consist of two physical files types; the data file in which the data and the database objects such as tables and indexes are stored, and the transaction log file in which SQL Server stores records for all transactions performed on that database.

SQL Server数据库由两种物理文件类型组成： 存储数据和数据库对象（例如表和索引）的数据文件，以及SQL Server存储在该数据库上执行的所有事务的记录的事务日志文件。

SQL Server transaction log contains metadata about the recorded transaction, such as the transaction ID, the transaction start and end time, the changed pages and the data changes.

SQL Server事务日志包含有关记录的事务的元数据，例如事务ID，事务开始和结束时间，更改的页面以及数据更改。

The SQL Server transaction log is an important database component that is used to make sure that the committed transaction’s data is preserved and the uncommitted transaction’s change will be rolled back. The transaction log is very useful when a hardware or application failures occur, by restoring the database to a previous point in time, as the log will be written to the log file before writing the data in the buffer cache to the data file.

SQL Server事务日志是重要的数据库组件，用于确保保留已提交事务的数据，并且将回退未提交事务的更改。 当发生硬件或应用程序故障时，通过将数据库还原到先前的时间点，事务日志非常有用，因为在将缓冲区高速缓存中的数据写入数据文件之前，该日志将被写入日志文件。

During the database recovery process, any transaction that is committed, without reflecting the data changes to the data files due to failure will be redone again, as the record already written to the database transaction log file before writing it to the data file. On the other hand, any data changes associated with uncommitted transaction will be rolled back by reversing the operation written in the transaction log file. In this way SQL Server guarantees the database consistency.

在数据库恢复过程中，将重新执行任何提交的事务，而不会由于失败而将数据更改反映到数据文件中，因为在将记录写入数据文件之前已将记录写入数据库事务日志文件中。 另一方面，与未提交的事务相关联的任何数据更改将通过反转事务日志文件中写入的操作来回滚。 这样，SQL Server可以保证数据库的一致性。

Data is written to the database data files randomly, but the logs are written to the transaction log file sequentially one by one, making the log write process faster. No performance gain can be taken from having multiple transaction log files in the database, as the logs will be written one at a time. It will be useful if the transaction log file become full and you need to create another file in a different disk that has more space.

数据是随机写入数据库数据文件的，但是日志是逐一依次写入事务日志文件的，这使得日志写入过程更快。 在数据库中拥有多个事务日志文件不会带来任何性能提升，因为这些日志将一次写入一个。 如果事务日志文件已满，并且您需要在另一个具有更多空间的磁盘中创建另一个文件，这将很有用。

It is recommended also to keep the transaction log file in a separate drive from the database data files, as placing both data and log files on the same drive can result poor database performance.

还建议将事务日志文件与数据库数据文件保存在单独的驱动器中，因为将数据和日志文件放在同一驱动器上可能会导致数据库性能下降。

The transaction log file internally consists of small units called Virtual Log Files (VLF).

事务日志文件在内部由称为虚拟日志文件（VLF）的小单元组成。

And the process in which SQL Server marks these VLFs as inactive, making it available for reuse by the database is called Truncation. In order to mark these VLFs as inactive, it shouldn’t contain any active log related to an opened transaction, as these logs may be used for redone or redo process. Log truncation doesn’t mean that the transaction log size will be reduced, only it will be marked as inactive to be used in future.

SQL Server将这些VLF标记为非活动状态以使其可供数据库重用的过程称为Truncation 。 为了将这些VLF标记为非活动状态，它不应包含与已打开的事务相关的任何活动日志，因为这些日志可用于重做或重做过程。 日志截断并不意味着事务日志大小会减少，只是将其标记为非活动状态以备将来使用。

Once the transaction log record inserted into the log file, it will be marked with a specific value called Logical Sequence Number (LSN). The log with the lowest LSN specifies the start of the active log that may be needed for any database activity, and the log with the highest LSN specifies the end of that active log. Any transaction log with LSN value less than the minimum LSN will be considered as inactive transaction log.

将事务日志记录插入日志文件后，将使用称为逻辑序列号（LSN）的特定值进行标记。 LSN最低的日志指定任何数据库活动可能需要的活动日志的开始，而LSN最高的日志指定该活动日志的结束。 LSN值小于最小LSN的任何事务日志都将被视为非活动事务日志。

The transaction log records that are needed by SQL Server Replication, Mirroring, Change Data Capture or Log Backup processes will remain as active until released by these activities.

SQL Server复制，镜像，更改数据捕获或日志备份过程所需的事务日志记录将保持活动状态，直到由这些活动释放为止。

The SQL Server database recovery model property determines how the transactions are logged. The default recovery model for the newly created databases is the same recovery model as the model system database. If the database recovery model is SIMPLE, the data pages will be written to data files, and all VLFs with no active log will be trun