SQL Server事务日志的初学者指南

什么是交易日志？ (What is a transaction log?)

事务日志是一个文件–每个SQL Server数据库的组成部分。 它包含在SQL Server数据库的日志记录过程中生成的日志记录。 当涉及灾难恢复时，事务日志是SQL Server数据库的最重要组成部分–但是，它必须不被损坏。 每次数据库修改–发生事务后，都会将日志记录写入事务日志。 所有更改均按顺序写入

SQL Server事务日志存储什么？ (What does a SQL Server transaction log store?)

A transaction log stores every transaction made to a SQL Server database, except some which are minimally logged like BULK IMPORT or SELECT INTO. Internally it is split into the smaller parts called Virtual Log Files (VLFs). When one VLF becomes full logging continue to write into the next available in the transaction log. The transaction log file can be represented as a circular file. When the logging reaches the end of the file it starts again from the beginning, but only if all the requirements has been met and the inactive parts has been truncated. The truncation process is necessary to mark all inactive parts so they can be used again and overwritten

事务日志存储对SQL Server数据库进行的每笔事务，但有些事务很少记录，例如BULK IMPORT或SELECT INTO。 在内部，它分为称为虚拟日志文件（VLF）的较小部分。 当一个VLF记录已满时，请继续写入事务日志中的下一个可用记录。 事务日志文件可以表示为循环文件。 当日志记录到达文件末尾时，它将重新从头开始，但前提是所有条件均已满足且非活动部分已被截断。 截断过程对于标记所有不活动的部分是必需的，因此可以再次使用它们并将其覆盖

A log record is no longer needed in the transaction log if all of the following are true:

如果满足以下所有条件，则不再需要事务日志中的日志记录：

• The transaction of which it is part has committed
  它所参与的交易已提交
• The database pages it changed have all been written to disk by a checkpoint
  更改后的数据库页已由检查点全部写入磁盘
• The log record is not needed for a backup (full, differential, or log)
  备份不需要日志记录（完整，差异或日志）
• The log record is not needed for any feature that reads the log (such as database mirroring or replication) [1]
  读取日志的任何功能（例如数据库镜像或复制）都不需要日志记录[1]

Logical log is an active part of the transaction log. A Log Sequence Number (LSN) identifies every transaction in the transaction log. The MinLSN is the starting point of the oldest active transaction in the online transaction log

逻辑日志是事务日志的活动部分。 日志序列号（LSN）标识事务日志中的每个事务。 MinLSN是在线事务日志中最早的活动事务的起点

SQL Server数据库可以在没有事务日志的情况下工作吗？ (Can SQL Server database work without a transaction log?)

No, that is not possible due to the SQL Server design and ACID compliance. ACID stands for atomicity, consistency, isolation, and durability. All the transactions must fullfil these characteristics:

不可以，由于SQL Server设计和ACID合规性，这是不可能的。 ACID代表原子性，一致性，隔离性和持久性。 所有交易必须满足以下特征：

• atomic transaction is either fully completed, or is not begun at all原子事务要么完全完成，要么根本不开始
• consistency in the system state by ensuring that at the end of any transaction the system is in a valid state保持一致
• isolation, it appears to be the only action that the system is carrying out at one time隔离运行，它似乎是，该系统是在同一时间进行的唯一操作
• durable meaning that once it has been successfully completed, all of the changes it made to the system are permanent持久的，这意味着一旦成功完成事务，对系统所做的所有更改都是永久的

一个SQL Server数据库可以有多个事务日志吗？ (Can one SQL Server database have more than one transaction log?)

Yes, that is possible, but it’s only recommended in the specific situations. Adding multiple transaction log files will not improve performance of the SQL Server database in any way. Writing can occur only into one file at the time, so parallel I/O operations are not possible

是的，这是可能的，但是仅在特定情况下才建议使用。 添加多个事务日志文件不会以任何方式提高SQL Server数据库的性能。 一次只能写入一个文件，因此无法进行并行I / O操作

Having multiple transaction log files is recommended only if the first one is full or the disk drive is running out of space. Either way, these problems should be addressed earlier and handled by creating transaction log backups and monitoring available space on the disk drive

仅当第一个已满或磁盘驱动器空间不足时，才建议使用多个事务日志文件。 无论哪种方式，都应该更早解决这些问题，并通过创建事务日志备份和监视磁盘驱动器上的可用空间来解决这些问题。

为什么SQL Server事务日志不断增长？ (Why is the SQL Server transaction log growing?)

Every transaction is followed by logging into the online transaction log. During SQL Server work, the transaction log grows if changes are made into a database, therefore maintaining the transaction log is crucial for proper SQL Server operation

每笔交易之后都要登录在线交易日志。 在SQL Server工作期间，如果对数据库进行了更改，则事务日志会增长，因此，维护事务日志对于SQL Server的正常运行至关重要

There are three recovery models available in SQL Server, depending on which one is used transaction log growth is manifested differently:

SQL Server中提供了三种恢复模型，具体取决于所使用的哪种，事务日志的增长以不同的方式体现出来：

• Simple recovery model – the transaction log backups are not supported. The truncation process is automated and space is reclaimed for reuse. There is data loss risk because changes since the most recent database backup are exposed. In the Simple recovery there is little chance for the transaction log growing – just in specific situations when there is a long running transaction or transaction that creates many changes
  简单恢复模型–不支持事务日志备份。 截断过程是自动化的，空间被回收以供重用。 存在数据丢失的风险，因为公开了自最近数据库备份以来的更改。 在简单恢复中，事务日志增长的机会很小-仅在特定情况下，当长时间运行的事务或产生许多更改的事务
• Bulk-logged recovery model – the transaction log backups are supported and needed on regular basis. There is no automated process of transaction log truncation, the transaction log backups must be made regularly to mark unused space available for overwriting. Bulk-logged recovery model reduces transaction log space usage by using minimal logging for most bulk operations
  批量记录恢复模型–定期支持和需要事务日志备份。 没有自动的事务日志截断过程，必须定期进行事务日志备份，以标记可用于覆盖的未使用空间。 批量记录恢复模型通过对大多数批量操作使用最少的日志记录来减少事务日志空间的使用
• Full recovery model – the transaction log backups are supported and needed on regular basis. There is no data loss risk under the normal circumstances. There is no automated process of transaction log truncation, the transaction log backups must be made regularly to mark unused space available for overwriting. In the Full recovery there is biggest chance of the transaction log growing because all transactions are logged
  完全恢复模型–定期支持和需要事务日志备份。 在正常情况下，没有数据丢失的风险。 没有自动的事务日志截断过程，必须定期进行事务日志备份，以标记可用于覆盖的未使用空间。 在完全恢复中，由于所有事务均已记录，因此事务日志增长的最大机会

如何在SQL Server中维护事务日志？ (How to maintain a transaction log in SQL Server?)

A transaction log maintenance is important task in SQL Server administration. Monitoring is recommended on daily basis or even more often is a SQL Server database has high amount of traffic. The transaction log space can be monitored by using the DBCC SQLPREF command:

事务日志维护是SQL Server管理中的重要任务。 建议每天进行监视，或者如果SQL Server数据库的通信量很大，则更经常进行监视。 可以使用DBCC SQLPREF命令监视事务日志空间：

DBCC SQLPERF(LOGSPACE);
GO

• • Database Name – Name of the database for the log statistics displayed
  • Log Size (MB) – Current size allocated to the log. This value is always smaller than the amount originally allocated for log space because the Database Engine reserves a small amount of disk space for internal header information
  • Log Space Used (%) – Percentage of the log file currently occupied with transaction log information
  • Status – Status of the log file. Always 0
  [3]

• 数据库名称–显示的日志统计信息的数据库名称
• 日志大小（MB）–当前分配给日志的​​大小。 此值始终小于最初为日志空间分配的数量，因为数据库引擎为内部标头信息保留了少量磁盘空间
• 使用的日志空间（％）–当前已被事务日志信息占用的日志文件的百分比
• 状态–日志文件的状态。 总是0
• [3]

The transaction log should be backed up on the regular basis to avoid the auto growth operation and filling up a transaction log file. Space in a transaction log can be truncated (cleared) through SQL Server Management Studio by selecting Transaction Log as backup type or through CLI by executing the following command:

应当定期备份事务日志，以避免自动增长操作并填满事务日志文件。 可以通过选择事务日志作为备份类型，或者通过执行以下命令通过CLI通过SQL Server Management Studio截断（清除）事务日志中的空间：

BACKUP LOG ACMEDB
TO DISK = 'C:\ACMEDB.TRN'
GO

That backed up space can be reused again and it’ll be overwritten with new transactions. Some operations can’t be combined and must be executed separately:

备份的空间可以再次重用，并且将被新的事务覆盖。 某些操作无法合并，必须单独执行：

我需要SQL Server事务日志备份吗？ (Do I need SQL Server transaction log backups?)

Yes, that is one of the most important resource when it comes to disaster recovery. They are not needed (and available) only if the Simple recovery model is used – but there is data loss exposure. Majority of Database Administrators uses 15 minutes interval or even less for a high traffic SQL Server databases. The transaction log backups are important because when taken they mark inactive VLFs that can be used for writing down new transactions

是的，这是灾难恢复中最重要的资源之一。 仅在使用简单恢复模型时才需要（和可用）它们，但是存在数据丢失的风险。 对于高流量SQL Server数据库，大多数数据库管理员使用15分钟甚至更少的时间间隔。 事务日志备份非常重要，因为在进行备份时，它们会标记可用于记下新事务的非活动VLF

翻译自: https://www.sqlshack.com/beginners-guide-sql-server-transaction-logs/