SQL Server中的事务日志

一、 物理与逻辑结构

事务日志(.ldf)记录与对应DB上所有事务及这些事务对DB的修改。它和数据文件同样重要，日志文件损坏与丢失将等于数据库损坏。

SQL Server将ldf文件划分成多个逻辑上的虚拟日志文件(virtual log files,简称VLF)。VLF没有固定大小，并且每个物理日志文件包含的VLF数不固定，但日志文件每自动增长一次，会增加一个VLF。若日志文件有过多的小自动增长，可能产生非常多的VLF，影响日志管理性能及DB启动时间。

SQL Server创建数据库时，根据日志文件(ldf)的大小，生成VLF的数量公式如下:

ldf文件的大小	VLF的数量
1M到64M	4
64M到1GB	8
大于1GB	16

可以使用DBCC LOGINFO查看VLF信息

事务所做的任何修改保存到数据库之前，相应的日志会被按照先后顺序记录到日志文件的逻辑末尾，并分配一个全局唯一的日志序列号(log sequence number,简称LSN）,这个序列号完全是按照顺序来的，如果日志中两个序列号LSN2>LSN1,则说明LSN2所在LSN1之后发生的。由此可以看出，将日志文件分为多个文件除了磁盘空间的考虑之外。完全不会像数据那样可以并行访问，所以将日志文件分为多个完全不会有性能上的提升。

 

二、 sample模式下日志的空间使用

1. 最小恢复LSN

     Minimum Recovery LSN（MinLSN）是下列三者之一的最小值:

• CheckPoint的开始LSN
• 还未结束的事务在日志的最小LSN
• 尚未传递给分发数据库的最早的复制事务起点的 LSN.

    下图是一个日志的片段：

    

     可以看到，最新的LSN是148，147是CheckPoint，在这个CheckPoint之前事务1已经完成，而事务2还未完成，所以对应的MinLSN应该是事务2的开始，也就是142。

 

2. 活动日志

     从MinLSN到日志的逻辑结尾处，则称为活动日志(Active Log)。

     活动日志分布在物理VLF上的关系可以用下图表示:

    

    VLF的状态是源自其上所含有的LSN的状态，可以分为两大类：活动VLF和不活动VLF

    更加细分可以将VLF的状态分为以下四类:

1. 活动(Active) – 在VLF上存储的任意一条LSN是活动的（事务未结束），VLF为活动状态
2. 可恢复(Recoverable) – VLF中不包含活动事务的日志记录，此时DB处于维护一个完整日志序列的状态，但是某些操作（例如数据库镜像、复制、日志备份等）还需要用到这些数据，因此不可以被覆盖。
3. 可重用(Reusable) – VLF中的数据已经不需要了，可以被覆盖（该状态也称为“可截断”）
4. 未使用(Unused) – VLF从未被使用

各种状态对比oracle redo log参考：https://blog.csdn.net/Hehuyi_In/article/details/90719072

 

    概念如下图：

      所谓的截断(truncated)是将可恢复状态的VLF转换到可重用状态。在简单恢复模式下，每一次CheckPoint，都会去检查是否有日志可以截断。如果有，CheckPoint都会将可截断部分进行截断，并将MinLSN向后推。当达到日志文件末尾，也就是上图的VLF8时，会重新循环到VLF1开始，以便重用空间。所以日志虽然可以从物理顺序上是从VLF1到VLF8，但逻辑顺序可以是从VLF6开始到VLF2结束：

    

      可以看出，简单恢复模式下日志是不保存的（当事务结束后，相关日志会被截断）。仅仅是用于保证事务回滚和崩溃恢复。所以备份日志也就无从谈起，更不能利用日志来恢复数据库。

 

三、 FULL模式下日志的角色

在完整恢复模式下，日志的作用不仅仅是保证了数据库事务的ACID，还可以使数据恢复到在日志范围内的任何时间点。

在简单恢复模式下，日志几乎是不用进行管理的。每一次CheckPoint都有可能截断日志，从而来回收不活动的VLF以便重复利用空间。因此在简单恢复模式下，日志的空间使用几乎可以不去考虑。

与之相反，在完整恢复模式下，日志作为恢复数据的重要组成部分，日志的管理和对日志空间使用的管理则需要重视。在完整恢复模式下，CheckPoint不会截断日志。只有对日志的备份才会将MinLSN向后推并截断日志。因此在一个业务量稍大的系统中，日志的增长速度将会变得很快。

    因此日志备份的目的分为以下两个:

• 减少活动日志的大小
• 减少日志损坏的风险

    

例如上图，在DB_1处做了完整备份，并且接下来两次分别做了两次日志备份(Log_1和Log_2)，在Log_2备份完不久服务器由于数据所在磁盘损坏。这时如果日志文件完好，则可以通过备份尾部日志(Tail of log)后，从DB_1开始恢复，依次恢复Log_1,Log_2,尾部日志来将数据库恢复到灾难发生时的时间点。理论上可以使数据的损失为0。

从日志恢复数据的原理是Redo，也就是将日志中记载的事务再重做一遍。这个开销和从完整或差异备份中恢复相比，要大很多。因此尽可能的减少利用日志的恢复量。而使用完整或者差异备份来恢复更多的数据。

 

四、 大容量日志(Bulk-logged)恢复模式

在完整恢复模式下，对数据库的每一项操作都会记录在日志中。而对于某些大量数据的导入导出操作，无疑会在日志中留下大量记录。很多情况下，我们并不需要将这些信息记录在日志中。大容量日志恢复模式作为完整恢复模式的备选方案，微软推荐的最佳实践是在进行大量数据操作（比如索引的创建、rebuilt，select into操作等），暂时由完整恢复模式切换到大容量恢复模式来节省日志。这个转换并不会破坏日志链。

  本文仅仅是对这个概念做简单解释。假设我要插入一批数据，则两种模式在日志中所记录的信息如下图。由此可以看出，在日志中，大容量恢复模式将这类操作变为一个原子。

     

 

五、 日志链(Log Chain)

连续的日志备份被称之为日志链，表示日志是连续的。这个概念可以用下图表示:

    

日志备份1的last LSN必须小于等于日志备份2的first LSN则这两个备份的日志链是连续的。通常是大于，但由于存在“只备份日志”选项，只备份日志并不截断日志，所以有可能重叠。

下图是一个生产环境下，在SSMS中查看日志链连续的例子:

可以看出，第一次完整备份后，备份多次事务日志，每一个事务日志的开始LSN都等于上一个事务日志的结束LSN。因此可以从第一次完整备份开始，恢复到最后一个日志备份期间的任何时间点。从日志恢复数据要求从最近一次完整或差异备份到所恢复的时间点之间的日志链是连续的。

 

六、 恢复顺序

还原恢复数据需要经历如下几步骤：

• 复制数据阶段：从完整备份和差异备份中将数据、索引页和日志复制到被恢复数据库文件。
• Redo阶段（前滚）：将记录在日志中的事务应用到从备份中复制过来的数据，使数据前滚到指定的时间点。这个阶段完成后，数据库还处于不可使用阶段:

      

    上面两步就是Restore

• Undo阶段（回滚）：传说中的Recovery，将任何未提交的事务回滚。这个阶段过后，数据库处于可用状态，任何后续备份将不能接着应用到当前数据库。

 

七、 日志损坏

日志可能会由于IO故障而损坏，当出现日志损坏时，若对日志的原理略有了解，并能在日志损坏的情况下尽量挽救数据，那么感觉一定是非常好的，下面我们来了解几种日志损坏的情况下的恢复情况。

1. 数据库正常关闭，日志损坏

当数据库正常关闭时，日志损坏就不是那么重要了，因为此时数据库中所有提交的事务对应的脏数据都已经CheckPoint到物理磁盘，因此不存在数据不一致的问题。因此，如果MDF和NDF文件完好，直接指定 FOR ATTACH_REBUILD_LOG参数后附加即可。值得注意的是，使用该方式附加数据库会自动重建日志文件，日志文件大小为0.5MB，也就是2个VLF，自动增长为10%，因此您需要手动再来设置一下日志的大小，避免出现太多VLF的情况。

 

2. 数据库非正常关闭，日志损坏

  在讲述这种情况之前，我们首先来看数据库所能处在的几种状态

       

强调两种和日志损坏关系很大的状态：RECOVERY_PENDING和SUSPECT状态。

假如出现了数据库没有正常关闭，也就是还有数据没有写到磁盘，此时数据库要启动就必须经历Recovery过程，如果日志损坏，则无法进行该Recovery过程，就会造成数据不一致的问题。

此时，数据库可能处于下面两种状态之一：

• RECOVERY_PENDING：需要运行crash recovery，但该过程由于资源等待无法开始，比如说日志完全损坏
• SUSPECT：crash recovery已经开始，但无法完成

处理该类情况要基于您所在的业务环境是否允许数据损失，可以选择从备份中恢复数据，或是将数据库状态改为EMERGENCY。EMERGENCY模式意味着数据库跳过crash recovery阶段，此时虽然可以访问数据库，但是会存在数据事务不一致的问题，如果仅仅是某些数据页不一致还好，但如果是对表结构修改的事务存在，那就可能存在数据库架构不一致的问题。

如果您没有合适的备份集，那只能通过该方式来恢复数据。

-- 将数据库设置为EMERGENCY模式
ALTER DATABASE AdventureWorks2012 SET EMERGENCY

与该模式有关的一个选项是REPAIR_ALLOW_DATA_LOSS，该选项依然会执行crash recovery过程，但会跳过受损的日志，从而尽可能的修复数据一致性问题，该选项会创建一个新的日志文件，最后使得数据库处于ONLINE状态

ALTER DATABASE AdventureWorks2012 SET SINGLE_USER;
DBCC CHECKDB(AdventureWorks2012,REPAIR_ALLOW_DATA_LOSS)

 

3. 数据库处于在线状态，日志损坏

在这种情况下，如果SQL Server在运行时需要使用的日志损坏（比如说回滚时），则SQL Server会将数据库下线，并转为SUSPECT模式。如果没有备份的话，只能考虑使用EMERGENCY模式。还有一种方式是，将数据库的恢复模式改为简单，然后手动发起一个CheckPoint来截断日志，最后再将数据库改回完整恢复模式。这种方式会破坏日志链，但可能会将被损坏的日志清除掉。

 

参考

http://www.voidcn.com/article/p-xsvycoea-zh.html

http://www.cnblogs.com/datazhang/p/5238677.html

http://www.cnblogs.com/CareySon/archive/2012/02/13/2349751.html 系列

 

checkpoint 相关 

https://docs.microsoft.com/en-us/sql/relational-databases/logs/database-checkpoints-sql-server?view=sql-server-ver15

https://docs.microsoft.com/zh-cn/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver15#physical_arch