基本术语
Transaction, Commit, and Rollback
事务包括对数据库的一次更改或一系列更改。它有一个明确开始和明确结束。开始时使用BEGIN TRANSACTION语句，或者SQL Server会自动为您开始一个事务。结束可以是以下四种情况之一：
- 事务在执行COMMIT TRANSACTION语句时提交。
- 在自动提交事务的情况下，SQL Server会自动提交事务。
- 在执行ROLLBACK TRANSACTION语句后，事务完成回滚。
- 在出现问题后，SQL Server自动回滚事务，事务完成回滚
当事务提交时，事务所做的更改在数据库中被最终确定，并在磁盘上的SQL Server事务日志中持久化。注意是“在事务日志中”。最终，数据文件页面将由检查点操作写入磁盘。
相反，当事务回滚时，事务所做的数据更改将不再存在于数据库中。数据库中仍然会有一些物理更改，因为回滚事务意味着执行更多的更改，但你可以认为回滚的事务没有影响数据库中的数据。
the SQL Server Transaction Log
每个 SQL Server 数据库都有事务日志，记录所有事务以及每个事务对数据库所做的修改，目的是为了在需要时允许在数据库中重新播放修改或回滚更改。
日志最初是在内存中，可能会在事务提交之前写入磁盘，但必须在事务完成提交之前肯定写入磁盘，注意“磁盘”指的是日志文件。当启用延迟持久性特性时，这个规则有一个例外。
Crash Recovery (故障/崩溃/实例恢复)
crash 是指SQL Server意外关闭，各种更改过的数据库没有能够正确关闭（确保所有更改过的数据文件页面都写入磁盘，并且数据库被标记为干净关闭）
当SQL Server启动时，它会检查所有数据库，看是否有未标记为干净关闭的数据库。如果它发现了，那么该数据库必须经历实例恢复(crash recovery)。这确保了以下几点：
- 对于在崩溃之前提交的任何事务，确保事务中的所有更改都反映在数据库中（即，重新播放事务）。
- 对于在崩溃之前未提交的任何事务，确保事务中的更改都不反映在数据库中（即，回滚事务）。
为什么需要事务日志？
日志记录的最基本原因是允许SQL Server数据库使事务持久化，以便在崩溃恢复期间可以恢复，或者在正常数据库操作期间需要时可以回滚。如果没有日志记录，数据库在崩溃后将会事务不一致，并且可能结构性损坏。如果没有日志记录，SQL Server中的许多其他特性将无法实现，包括：
- 恢复个别的事务。
- SQL Server 启动时恢复所有未完成的事务。
- 将还原的数据库、文件、文件组或页前滚至故障点。
- 支持事务复制。
- 支持高可用性和灾难恢复解决方案：AlwaysOn 可用性组、数据库镜像和日志传送
SQL Server（以及所有类似的数据库）都使用所谓的预写日志技术。这意味着事务日志必须在变更数据页面写入磁盘前写入磁盘，以保证能够正确地恢复数据库。如果变更数据页面在事务日志记录之前被写入磁盘数据文件，并且SQL Server崩溃了，将无法知道要回滚什么，数据库将会不一致。无论隔离级别、事务类型或是否使用延迟持久性特性，记住：先写日志记录，后写数据页面。
checkpoint(检查点)
由于性能原因，数据库引擎会在内存中的缓冲区缓存中对数据库页面进行修改，并且在每次更改后不会立即将这些页面写入磁盘。相反，数据库引擎会定期对每个数据库发出检查点。检查点将当前在内存中修改过的页面（称为脏页）和事务日志信息从内存写入磁盘，并在事务日志中记录这些信息。
触发检查点的活动
- 显式执行 CHECKPOINT 语句。
- 已经使用 ALTER DATABASE 添加或删除了数据库文件。
- 通过 SHUTDOWN 语句或通过停止 SQL Server (MSSQLSERVER) 服务停止了 SQL Server 实例。
- SQL Server 实例在每个数据库内定期生成自动检查点，以减少实例恢复数据库所需的时间。
- 进行了数据库备份。
检查点操作
检查点在数据库中会执行下列过程：
- 将记录写入日志文件，标记检查点的开始。
- 将所有脏日志和数据页写入磁盘。
- 将标记检查点结束的记录写入日志文件。
检查点类型
|
名称 |
Transact-SQL 接口 |
说明 |
|
自动 |
EXEC sp_configure 'recovery interval', 'seconds' |
自动在后台发出，以满足 recovery interval 服务器配置选项建议的时间上限。 |
|
间接 |
ALTER DATABASE ... SET TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES } |
在后台发出，以满足给定数据库的用户指定的目标恢复时间要求。 从 SQL Server 2016 (13.x) 开始，默认值为 1 分钟。 较旧版本的默认值为 0，表示数据库使用自动检查点，其频率依赖于针对服务器实例的恢复间隔设置。 |
|
手动 |
CHECKPOINT [ checkpoint_duration ] |
在执行 Transact-SQL CHECKPOINT 命令时发出。 在连接的当前数据库中执行手动检查点操作。 默认情况下，手动检查点运行至完成。 checkpoint_duration 参数指定完成检查点所需的时间（秒）。 |
|
内部 |
无 |
由各种服务器操作（如备份和数据库快照创建）发出，以确保磁盘映像与日志的当前状态匹配。 |
数据落盘过程举例
以隐式事务中更新单个表行为例。想象一个简单的堆表，有一个整数列 c1 和一个字符列 c2。表有 10,000 行，用户提交了以下更新查询：
UPDATE SimpleTable SET c1 = 10 WHERE c2 LIKE '%Paul%';以下操作发生：
- SimpleTable 的数据页面从磁盘读取到内存（缓冲池）中，以便搜索匹配的行。结果发现有三个数据页面包含五条符合 WHERE 子句条件的行。
- 存储引擎自动开始了一个隐式事务。 这三个数据页面和五条数据行被锁定，以便进行更新。
- 对内存中三个数据页面上的五条数据记录进行了更改。
- 这些更改也被记录在磁盘上的事务日志中的日志记录里。
- 存储引擎自动提交了隐式事务，并在日志中为该事务写入提交记录。
- 返回给客户端。
总结：
- 对于每次逻辑写操作，都会在日志缓存中插入一个事务日志记录，记录这些修改。
- 在关联的脏页从缓冲区缓存中移除并写入磁盘之前，日志记录必须先写入磁盘。
- 在事务提交或日志缓冲区变满时，日志记录会被写入磁盘
- 检查点（checkpoint）进程会定期扫描缓冲区缓存中包含特定数据库页面的缓冲区，并将所有脏页写入磁盘
