SQL server日志

SQL server 大牛：
https://www.cnblogs.com/CareySon/category/354290.html

---

为了保证IO，事务状态是先在内存中进行，但当系统故障就会丢失信息，不知道事务是不是已经被执行，所以就用日志 来存放事务状态和详细的执行步骤，并且是存档在磁盘中，而不是内存。
也许你会有疑问，那每次对于修改的数据还是会写入日志文件.同样消耗磁盘IO。上篇文章讲过，每一笔写入日志的记录都是按照先后顺序，给定顺序编号的LSN进行写入的，日志只会写入到日志文件的逻辑末端。而不像数据那样，可能会写到磁盘的各个地方.所以，写入日志的开销会比写入数据的开销小很多。

即使事务已经到了Commit阶段，也仅仅只是把缓冲区的日志页写入日志，而直到Lazy Writer或CheckPoint时，才真正将缓冲区的数据页写入磁盘文件。

这样系统故障时一起数据库不一致时，在数据库再次启动的时候，会去扫描日志，找出那些未提交却写入持久化存储的数据，或已提交却未写入持久化存储的数据，来进行Undo和Redo来保证事务的一致性。（Undo用于解决事务未完成和事务回滚的情况，而Redo则是为了保证已经提交的事务写到磁盘。）
要注意的是：CheckPoint会将所有缓冲区的脏页写入磁盘，不管脏页中的数据是否已经Commit。这意味着有可能已经写入磁盘的“脏页”会在之后回滚（RollBack).不过不用担心，如果数据回滚，SQL Server会将缓冲区内的页再次修改，并写入磁盘。

但内存不可能一直保留很多日志，要定期把内存中信息写入到磁盘，所以就有checkpoint记录点。CheckPoint之前的日志就可以被安全删除（简单恢复模式）或归档了（完整恢复模式），在Recovery时，仅仅需要从CheckPoint开始扫描日志，从而减少宕机时间。

日志截断:
日志文件是由多个VLF组成，VLF中又按顺序记录，记录用不同LSN标识。 因为一个事务还未完成或者需要保留用于回滚，这个事务对应的多个动作记录就会保留，这样包含这些要使用的LSN的VLF就是活动的。 不包括这些需要使用的LSN的VLF就是不活动的。
截断就是把这些不活动的VLF状态转变会可重用状态。在简单恢复模式下，每一次CheckPoint，都会去检查是否有日志可以截断.如果有inactive的VLF时，CheckPoint都会将可截断部分进行截断，并将MinLSN向后推.

---

SQL结尾日志备份

利用结尾日志备份，Avamar 在恢复过程中会备份事务日志的尾部，以捕获备份中未包含的日志记录。完成数据库恢复后，Avamar 将使用结尾日志备份来恢复备份中未包含的事务。

要执行结尾日志备份，数据库必须在线并使用完整恢复模式或大容量日志恢复模式。
因此，无法对主数据库和 msdb 数据库等系统数据库执行结尾日志备份，因为这些数据
库使用的是简单恢复模式。

SQLServer中的日志
SQL Server中靠日志来维护一致性（当然，日志的作用非常多，但一致性是日志的基本功能，其他功能可以看作是额外的功能）。通常我们创建数据库的时候，会附带一个扩展名为ldf的日志文件。日志文件其实本质上就是日志记录的集合。
与日志Undo方面的不同之处在于：Undo用于解决事务未完成和事务回滚的情况，而Redo则是为了保证已经提交的事务所做的修改持久化到辅助存储（磁盘）。

Undo/Redo Recovery
当SQL Server非正常原因关闭时，也就是在没有走CheckPoint（会在下面提到）时关闭了数据库，此时数据库中数据本身可能存在不一致的问题。因此在数据库再次启动的时候，会去扫描日志，找出那些未提交却写入持久化存储的数据，或已提交却未写入持久化存储的数据，来进行Undo和Redo来保证事务的一致性。

CheckPoint：
给出的简单例子足以说明Recovery机制。但例子过于简单，假如一个非常繁忙的数据库可能存在大量日志，一个日志如果全部需要在Recovery过程中被扫描的话，那么Recovery过程所导致的宕机时间将会成为噩梦。因此，我们引入一个叫CheckPoint的机制，就像其名称那样，CheckPoint就是一个存档点，意味着我们可以从该点继续开始。
在Undo/Redo机制的数据库系统中，CheckPoint的机制如下：
１.将CheckPoint标记写入日志（标记中包含当前数据库中活动的事务信息），并将Log Block写入持久化存储
2.将Buffer Pool中所有的脏页写入磁盘，所有的脏页包含了未提交事务所修改的数据
3.将结束CKPT标记写入日志，并将Log Block写入持久化存储
由CheckPoint的机制可以看出，由于内存中的数据往往比持久化存储中的数据更新，而CheckPoint保证了这部分数据能够被持久化到磁盘，因此CheckPoint之前的数据一定不会再需要被Redo。而对于未提交的事物所修改的数据写入持久化存储，则可以通过Undo来回滚事务（未提交的事物会导致CheckPoint无法截断日志，因此这部分日志可以在Recovery的时候被读取到，即使这部分日志在CheckPoint之前）。
此时，我们就可以100%的保证，CheckPoint之前的日志就可以被安全删除（简单恢复模式）或归档了（完整恢复模式），在Recovery时，仅仅需要从CheckPoint开始扫描日志，从而减少宕机时间。

预写式日志（Write-Ahead Logging (WAL)）
SQL Server使用了WAL来确保了事务的原子性和持久性.实际上，不光是SQL Server,基本上主流的关系数据库包括oracle,mysql,db2都使用了WAL技术.
WAL的核心思想是:在数据写入到数据库之前，先写入到日志.
因为对于数据的每笔修改都记录在日志中，所以将对于数据的修改实时写入到磁盘并没有太大意义，即使当SQL Server发生意外崩溃时，在恢复(recovery)过程中那些不该写入已经写入到磁盘的数据会被回滚(RollBack),而那些应该写入磁盘却没有写入的数据会被重做(Redo)。从而保证了持久性(Durability)
但WAL不仅仅是保证了原子性和持久性。还会提高性能.
硬盘是通过旋转来读取数据,通过WAL技术，每次提交的修改数据的事务并不会马上反映到数据库中，而是先记录到日志.在随后的CheckPoint和lazy Writer中一并提交,如果没有WAL技术则需要每次提交数据时写入数据库。
也许你会有疑问，那每次对于修改的数据还是会写入日志文件.同样消耗磁盘IO。上篇文章讲过，每一笔写入日志的记录都是按照先后顺序，给定顺序编号的LSN进行写入的，日志只会写入到日志文件的逻辑末端。而不像数据那样，可能会写到磁盘的各个地方.所以，写入日志的开销会比写入数据的开销小很多。
事务日志并不是一步步写入磁盘.而是首先写入缓冲区后，一次性写入日志到磁盘.这样既能在日志写入磁盘这块减少IO，还能保证日志LSN的顺序.
即使事务已经到了Commit阶段，也仅仅只是把缓冲区的日志页写入日志。
而直到Lazy Writer或CheckPoint时，才真正将缓冲区的数据页写入磁盘文件

Lazy Writer和CheckPoint的区别
Lazy Writer和CheckPoint往往容易混淆。因为Lazy Writer和CheckPoint都是将缓冲区内的“脏”页写入到磁盘文件当中。但这也仅仅是他们唯一的相同点了。
Lazy Writer存在的目的是对缓冲区进行管理。当缓冲区达到某一临界值时，Lazy Writer会将缓冲区内的脏页存入磁盘文件中，而将未修改的页释放并回收资源。
CheckPoint存在的意义是减少服务器的恢复时间(Recovery Time).
要注意的是：CheckPoint会将所有缓冲区的脏页写入磁盘，不管脏页中的数据是否已经Commit。这意味着有可能已经写入磁盘的“脏页”会在之后回滚（RollBack).不过不用担心，如果数据回滚，SQL Server会将缓冲区内的页再次修改，并写入磁盘。
通过CheckPoint的运作机制可以看出，CheckPoint的间歇(Recovery Interval)长短有可能会对性能产生影响。

https://www.cnblogs.com/CareySon/archive/2012/02/17/2355200.html
在简单恢复模式下日志的角色
SQL Server提供的几种备份类型
SQL Server所提供的几种备份类型基本可以分为以下三种（文件和文件组备份以及部分备份不在本文讨论之列):
1.完整(Full)备份:直接将所备份的数据的所有区(Extent)进行复制。这里值得注意的有2点:
完整备份并不像其名字“完整”那样备份所有部分，而是仅备份数据库本身，而不备份日志(虽然仅仅备份少量日志用于同步）
完整备份在备份期间，数据库是可用的。完整备份会记录开始备份时的MinLSN号，结束备份时的LSN号，将这个区间的日志进行备份，在恢复时应用到被恢复的数据库(这里经过修改，感谢魔君六道指出)
2.差异(Differential)备份:只备份上次完整备份后，做修改的部分。备份单位是区(Extent)。意味着某个区内即使只有一页做了变动，则在差异备份里会被体现.差异备份依靠一个BitMap进行维护，一个Bit对应一个区，自上次完整备份后，被修改的区会被置为1，而BitMap中被置为1对应的区会被差异备份所备份。而到下一次完整备份后，BitMap中所有的Bit都会被重置为0。
3.日志(Log)备份:仅仅备份自上次完整备份或日志备份之后的记录。在简单模式下，日志备份毫无意义(SQL Server不允许在简单恢复模式下备份日志)，下文会说明在简单恢复模式下，为什么日志备份没有意义。

在简单恢复模式下,为了保证事务的持久性，那些有可能回滚的数据会被写入日志。这些日志需要被暂时保存在日志以确保在特定条件下事务可以顺利回滚。这就涉及到了一个概念—最小恢复LSN（Minimum Recovery LSN（MinLSN） ）
MinLsn是在还未结束的事务记录在日志中最小的LSN号,MinLSN是下列三者之一的最小值:
CheckPoint的开始LSN
还未结束的事务在日志的最小LSN
尚未传递给分发数据库的最早的复制事务起点的 LSN.

可以看到，最新的LSN是148，147是CheckPoint,在这个CheckPoint之前事务1已经完成，而事务2还未完成，所以对应的MinLSN应该是事务2的开始，也就是142.
而从MinLSN到日志的逻辑结尾处，则称为活动日志(Active Log)。
而活动日志分布在物理VLF上的关系可以用下图表示:

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

而更加细分可以将VLF的状态分为以下四类:
活动(Active) –在VLF 上存储的任意一条LSN是活动的时，则VLF则为活动状态，即使一个200M的VLF只包含了一条LSN，如上图的VLF3
可恢复(Recoverable) – VLF是不活动的，VLF上不包含活动LSN,但还未被截断(truncated)
可重用(Reusable) – VLF是不活动的，VLF上不包含活动LSN,已经被截断(truncated)，可以重用
未使用(Unused) – VLF是不活动的,并且还未被使用过

而所谓的截断(truncated)只是将可恢复状态的VLF转换到可重用状态。在简单恢复模式下，每一次CheckPoint，都会去检查是否有日志可以截断.如果有inactive的VLF时，CheckPoint都会将可截断部分进行截断，并将MinLSN向后推.
在日志达到日志文件(ldf文件）末尾时，也就是上图的VLF8时，会重新循环到VLF1开始，以便让空间进行重复利用。
因此可以看出，简单恢复模式下日志是不保存的（当事务结束后，相关的会被截断）。仅仅是用于保证事务回滚和崩溃恢复的用途.所以备份日志也就无从谈起，更不能利用日志来恢复数据库。

---

https://www.cnblogs.com/CareySon/archive/2012/02/23/2364572.html
在完整恢复模式下日志的角色
在简单恢复模式下，日志几乎是不用进行管理的。每一次CheckPoint都有可能截断日志，从而来回收不活动的VLF以便重复利用空间。因此在简单恢复模式下，日志的空间使用几乎可以不去考虑。与之相反，在完整恢复模式下，日志作为恢复数据的重要组成部分，日志的管理和对日志空间使用的管理则需要重视。

在完整恢复模式下，CheckPoint不会截断日志。只有对日志的备份才会将MinLSN向后推并截断日志。因此在一个业务量稍大的系统中，日志的增长速度将会变得很快。

大容量日志(Bulk-logged)恢复模式
大容量恢复模式在很多地方和完整恢复模式相同。但由于在完整恢复模式下，对数据库的每一项操作都会记录在日志中。而对于某些大量数据的导入导出操作，无疑会在日志中留下大量记录。很多情况下，我们并不需要将这些信息记录在日志中。
恢复次序
从备份恢复数据需要经历如下几步骤:
1.复制数据阶段:从完整备份和差异备份中将数据，索引页和日志复制到被恢复数据库文件。
2.Redo(roll forward)阶段:将记录在日志中的事务应用到从备份中复制过来的数据。使数据Roll Forward到指定的时间点.这个阶段完成后，数据库还处于不可使用阶段:
如图:
上面两部就是Restore
3.Undo(Roll Back)阶段:这也是传说中的Recovery，将任何未提交的事务回滚。这个阶段过后，数据库处于可用状态。任何后续备份将不能接着应用到当前数据库。

这个概念比如:
在连续两个日志链的日志备份，在第一个事务日志备份中定义的事务，在第二个事务日志备份中Commit.如果在第一个事务日志还原后使用了Recovery选项.也就是经历了Undo阶段。则事务1在Undo阶段会被回滚：

可见，日志备份1中的T1被回滚，在日志备份2中的Commit也就毫无意义。这也就是为什么经历过Undo阶段后不允许再恢复后续备份。因此，微软推荐的最佳实践是使用NoRecovery选项不进行Undo阶段。而在所有备份恢复后单独进行Undo阶段,这个操作可以通过还原日志尾部时，指定Recovery选项进行。

---

http://www.cnblogs.com/CareySon/archive/2012/02/23/2365006.html
https://www.cnblogs.com/mc67/p/4860338.html
SQL Server中灾难时备份结尾日志(Tail of log)的两种方法
在DB_1处做了完整备份，并且接下来两次分别做了两次日志备份(Log_1和Log_2),在Log_2备份完不久服务器由于数据所在磁盘损坏。这时如果日志文件完 好，则可以通过备份尾部日志(Tail of log)后，从DB_1开始恢复，依次恢复Log_1,Log_2,尾部日志来将数据库恢复到灾难发生时的时间点。理论上可以使数据的损失为0。

案例一：
做一次完整备份，–插入数据，-备份事务日志，再次插入数据；
突然，就在此时，disaster 发生了，模拟灾难： 把服务停掉了，删除mdf 主数据库
此时需要冷静处理：
–备份尾部日志。
USE master
GO
BACKUP LOG MC67 TO DISK=‘F:\TEST\MC67_TAIL.TRN’
WITH INIT, NO_TRUNCATE --必须指定NO_TRUNCATE，没有这个option，你备份不了
GO
–然后依次恢复
案例二：

由于各种原因，所处的SQL Server实例也崩溃，无法通过T-SQL来备份结尾日志。此时数据库文件损坏，而事务日志文件保持正确。
假设情况和上面例子一样，此时我手里有一个完整备份(MC67_FULL.BAK)和一个日志备份(MC67_log1.TRN),还有一个日志文件(ldf)。
解决步骤：

1. 这时我将这几个文件拷贝到其他拥有SQL Server实例的机器上。
2. 新建一个和原数据库名一样的数据库。设置为脱机状态。
3. 删除新建数据库的MDF文件。
4. 将需要备份的数据库的日志文件替换掉原有的LDF文件。
5. 此时直接备份结尾日志
   原有Sql server实例恢复后一次恢复完整备份和两个日志备份。成功恢复到灾难发生点。