您的位置:新葡亰496net > 网络数据库 > 新葡亰496netServer事务日志及其组织,SERVER大话存

新葡亰496netServer事务日志及其组织,SERVER大话存

发布时间:2019-10-30 00:38编辑:网络数据库浏览(50)

     

    事务日志又称为重做日志,Oracle与SQL Server中的事务日志功能是类似的。与Oracle不同的是,对数据库添加重做日志文件时,可以如同SQL Server数据库的数据文件一样指定初始化大小及增长率、最大大小属性等属性。Oracle数据库添加事务日志文件时,只能指定初始大小,不能指定增长率、最大大小属性等属性

    简介

    SQL Server中的事务日志无疑是SQL Server中最重要的部分之一。因为SQL SERVER利用事务日志来确保持久性(Durability)和事务回滚(Rollback)。从而还部分确保了事务的ACID属性.在SQL Server崩溃时,DBA还可以通过事务日志将数据恢复到指定的时间点。当SQL Server运转良好时,多了解一些事务日志的原理和概念显得并不是那么重要。但是,一旦SQL SERVER发生崩溃时,了解事务日志的原理和概念对于快速做出正确的决策来恢复数据显得尤为重要.本系列文章将会从事务日志的概念,原理,SQL Server如何使用日志来确保持久性属性等方面来谈SQL Server的事务日志.

     

    SQL SERVER大话存储结构(5)_SQL SERVER 事务日志解析

    阅读目录(Content)

    • 1 基本介绍
    • 2 对数据库启动的影响
    • 3 日志文件添加方式
    • 4 物理结构
    • 5 延迟日志截断原因
    • 6 管理事务日志


    本系列上一篇博文链接:[SQL SERVER大话存储结构(4)_复合索引与包含索引](http://www.cnblogs.com/xinysu/p/6928305.html)
    


    回到顶部(go to top)


    事务日志支持的操作

    SQL Server中靠日志来维护一致性(当然,日志的作用非常多,但一致性是日志的基本功能,其他功能可以看作是额外的功能)。
      事务日志支持以下操作:

    • 恢复个别的事务
      如果应用程序发出 ROLLBACK 语句,或者数据库引擎检测到错误(例如失去与客户端的通信),使用日志记录回退未完成的事务所做的修改。
    • 在 SQL Server 启动时恢复所有未完成的事务
      运行 SQL Server 的服务器发生故障时,数据库可能处于这样的状态:还没有将某些修改从缓存写入数据文件,在数据文件内有未完成的事务所做的修改。 启动 SQL Server 实例时,它将对每个数据库执行恢复操作,在事务日志中找到的每个未完成的事务并进行回滚,以确保数据库的完整性。这种恢复称为实例恢复
    • 将还原的数据库、文件、文件组或页前滚至故障点
      在硬件丢失或磁盘故障影响到数据库文件后,用户用过去的数据库备份来恢复数据库。而过去的数据库备份数据显然是当初备份时的状态,不会包含从备份完成到数据库崩溃时刻这段时间内产生的数据,因为重做日志文件中记录了所有数据的修改,SQL Server会把事务日志中的操作记录应用到恢复的数据文件,从而可以使数据库恢复到数据库存储介质发生故障的时刻,这种恢复称为介质恢复
    • 支持事务复制
      事务复制的原理是先将发布服务器数据库中的初始快照发送到各订阅服务器,然后监控发布服务器数据库中数据发生的变化,捕获个别数据变化的事务并将变化的数据发送到订阅服务器。日志读取器代理程序监视已为事务复制配置的每个数据库的事务日志,并将已设复制标记的事务从事务日志复制到分发数据库中。只有已提交的事务才能发送到分发数据库中。
    • 支持高可用性和灾难恢复解决方案
      备用服务器解决方案、AlwaysOn 可用性组、数据库镜像和日志传送极大程度上依赖于事务日志。

    事务日志的物理组织构架

    事务日志仅仅是记录与其对应数据库上的事务行为和对数据库修改的日志文件.在你新建数据库时,伴随着数据库文件,会有一个默认以ldf为扩展名的事务日志文件. 当然,一个数据库也可以配有多个日志文件,但是在逻辑上,他们可以看成一个.

    在SQL Server对于日志文件的管理,是将逻辑上一个ldf文件划分成多个逻辑上的虚拟日志文件(virtual log files,简称VLFs).以便于管理。用个类比方法来看,日志文件(ldf)好比一趟火车,每一节车厢都是一个虚拟日志文件(VLFs):

    新葡亰496net 1

    新葡亰496net 2

     

    那为什么SQL Server要把日志文件划分出多个VLFS呢?因为SQL Server通过这种方式使得存储引擎管理事务日志更加有效.并且对于日志空间的重复利用也会更加高效。使用VLF作为收缩数据库的最小单位比使用ldf文件作为最小单位无疑是更加高效的.

    VLFS的个数和大小无法通过配置进行设定,而是由SQL Server进行管理.当Create或Alter数据库时,SQL Server通过ldf文件的大小来决定VLFS的大小和数量。在日志文件增长时,SQL Server也会重新规划VLFS的数量.

    注意:根据这个原理不难看书,如果设置日志文件的增量过小,则会产生过多的VLFS,也就是日志文件碎片,过多的日志文件碎片会拖累SQL Server性能.

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

    ldf文件的大小

    VLF的数量

    1M到64M

    4

    64M到1GB

    8

    大于1GB

    16

    下面我们来看一个例子:

    创建数据库,指定日志大小为65M

    新葡亰496net 3

    通过DBCC,我们可以看到,对应的有8个VLFs:

    新葡亰496net 4

    再次创建数据库,指定日志初始大小为28M:

    新葡亰496net 5

    可以看到,对应的,VLF的数量变为4:

    新葡亰496net 6

    而对于日志文件的增长,SQL Server使用了和创建数据库时相同的公式,也就是每次增长比如为2M,则按照公式每次增长4个VLFs.

    我们创建一个TestGrow数据库,指定日志文件为2M,此时有4个VLFS:

    新葡亰496net 7

    当我们增长2M时,这个2M则是按照公式,再次分配4个VLFs:

    新葡亰496net 8

    此时,这时能看到的VLFs数量应该为4 4=8个:

    新葡亰496net 9

    由此可以看出,指定合适的日志文件初始大小和增长,是减少日志碎片最关键的部分.

     

    ****1 ******基本介绍**

    每个数据库都具有事务日志,用于记录所有事物以及每个事物对数据库所作的操作。
    
    日志的记录形式需要根据数据库的恢复模式来确定,数据库恢复模式有三种:
    
    • 完整模式,完全记录事物日志,需要定期进行日志备份。
    • 大容量日志模式,适用于批量操作的数据库,可以以更压缩的方式处理日志,需要定期进行日志备份。
    • 简单模式,也有日志文件,只是该模式下可以通过checkpoint自动重用virtual log file,所以日志文件会处于一直重复使用的过程,保持一定大小,但是,如果有一个事务启动,很久没有commit,那么从这个事务开始到最后commit的时间段内的事务日志存储空间都无法checpoint自动重用,这时,你很可能看到一个很大的日志文件;注意,简单模式下是无法进行日志备份

    数据库里边,任何对数据库的读写都是在内存页中找到对应的数据也,再做修改,如果内存页中不存在数据页,则从磁盘加载如内存中。当一个修改操作发生时,修改的将是内存页中对应的数据页面,同时也会实时记录到日后文件ldf中。那么,什么时候数据会被同步到mdf文件呢,只有以下三种情况:

    • 做checkpoint时,后续会专门整理checkpoint的相应文章;

    • Lazy write运行时,即内存出现压力,需要把内存中的数据页写入到磁盘,腾出内存空间;

    • eager write时,即发生bulk insert和select into操作时。

      DB中的事务日志记录,可以给我们带来很多好处,它可以支持以下操作:

    • 恢复个别的事务。

    • 在 SQL Server 启动时恢复所有未完成的事务。

    • 将还原的数据库、文件、文件组或页前滚至故障点。

    • 支持事务复制。

    • 支持高可用性和灾难恢复解决方案:AlwaysOn 可用性组、数据库镜像和日志传送。

    回到顶部(go to top)


    事务日志文件的组织

    新葡亰496net 10

    事务日志的逻辑组织构架

    当针对数据库对象所做的任何修改保存到数据库之前,相应的日志首先会被记录到日志文件。这个记录会被按照先后顺序记录到日志文件的逻辑末尾,并分配一个全局唯一的日志序列号(log sequence number,简称LSN),这个序列号完全是按照顺序来的,如果日志中两个序列号LSN2>LSN1,则说明LSN2所在LSN1之后发生的.

    由此可以看出,将日志文件分为多个文件除了磁盘空间的考虑之外。完全不会像数据那样可以并行访问,所以将日志文件分为多个完全不会有性能上的提升.

    LSN号可以看作是将日志文件和其记录数据之间的纽带.每一条日志不仅有LSN号,还有其对应事务的事务日志:

    一个简单的图片示例如下:

    新葡亰496net 11

    许多类型的操作都记录在事务日志中。这些操作包括:

    • 每个事务的开始和结束。

    • 每次数据修改(插入、更新或删除)。这包括系统存储过程或数据定义语言 (DDL) 语句对包括系统表在内的任何表所做的更改。

    • 每次分配或释放区和页。

    • 创建或删除表或索引。

     

    对于LSN如何在ROLLBACK或者是ROLL FORWARD中以及在备份恢复过程中起作用,会在后续文章中提到

     

     

    ****2 ******对数据库启动的影响**

    当数据库重启或者还原到最后的时候,数据库都会进入 recovery状态,正常情况下,这个状态持续时间在几十秒间,但是特殊情况下,它会花费非常长的时间,甚至几个小时,如果这个步骤失败,数据库则进入到挂起 suspect状态,无法正常提供使用。
    
    **那么,当数据库进入 recovery 的时候,它在操作些什么呢?**
    
    SQL SERVER日志会记录所有修改记录(数据的修改情况,不包含SQL语句),包括Begin Transaction和Commit / Rollback Transaction 操作。由于对事务日志的修改,要比数据文件的修改要快,所有会出现,数据修改更新到了日志文件,但是还没有落盘到数据文件,那么这个时候数据库就处于recovery状态,同时对事务日志最近的一个checkpoint点以后的所有数据修改记录做以下检查:
    

    新葡亰496net 12

    image

    所有检查结束后,则会对数据库做一个checkpoint的表示,并写入事务日志中,表明日志文件跟数据文件已经同步结束,完成了recovery过程,数据库可正常提供使用。这里需要注意一点,如果你数据库最近一次checkpoint到现在的修改操作足够多,那么将会耗费相对较长时间来检查,同时也能够在 error log中看到百分比标识的recovery完成进展,避免漫无目的的等待。
    

    Error Log的检查,可以通过图形界面(见下图)查看当前日志,也可以运行xp_readerrorlog 查询。

    [

    新葡亰496net 13

    复制代码

    ](javascript:void(0); "复制代码")

    新葡亰496netServer事务日志及其组织,SERVER大话存储结构。<pre style="margin: 0px; padding: 0px; white-space: pre-wrap; word-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;"> 1 /*
    2 xp_readerrorlog参数说明
    3 1. 存档编号
    4 2. 日志类型(1为SQL Server日志,2为SQL Agent日志)
    5 3. 查询包含的字符串
    6 4. 查询包含的字符串
    7 5. LogDate开始时间
    8 6. LogDate结束时间
    9 7. 结果排序,按LogDate排序(可以为降序"Desc" Or 升序"Asc"),默认升序
    10 */
    11
    12 Exec xp_readerrorlog 0,1,Null,Null,'2017-02-16 10:53:32.300','2017-02-16 12:53:32.300'</pre>

    [

    新葡亰496net 14

    复制代码

    ](javascript:void(0); "复制代码")

    新葡亰496net 15

    image.png

     假设出现这种情况,由于上线的重要程度远远重要过 数据丢失的情况,并且你跟所有部门沟通确认 **可以承担 data file跟log file之间的差异数据的丢失**,那么你可以按以下步骤操作,**严重建议不要这么操作**,因为会带来不可预估的数据丢失情况,**如果你命悬一线,真打算放弃这部分数据**,那么,可以按照以下操作:
    

    [

    新葡亰496net 16

    复制代码

    ](javascript:void(0); "复制代码")

    <pre style="margin: 0px; padding: 0px; white-space: pre-wrap; word-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;"> 1 #设置数据库单用户
    2 alter database backupdb set single_user with rollback immediate
    3
    4 #设置数据库紧急状态
    5 alter database backupdb set emergency with rollback immediate
    6
    7 #获取事务日志的物理名和逻辑名后,重建日志文件
    8 select name,physical_name from sys.master_fiels where database_id=db_id('backupdb')
    9 alter database backupdb rebuild log on (name='事务日志的逻辑名',filename='事务日志的物理名词')
    10
    11 #设置数据库online
    12 alter database backupdb set online with rollback immediate
    13
    14 #设置数据库为多用户
    15 alter database backupdb set multi_user with rollback immediate</pre>

    [

    新葡亰496net 17

    复制代码

    ](javascript:void(0); "复制代码")

    回到顶部(go to top)

     

    1. 事务日志物理体系结构

    事务日志仅仅是记录与其对应数据库上的事务行为和对数据库修改的日志文件。在你新建数据库时,伴随着数据库文件,会有一个默认以ldf为扩展名的事务日志文件。当然,一个数据库也可以配有多个日志文件。
      SQL Server把一个物理日志文件从逻辑上划分为多个虚拟日志文件(Virtual Log File,VLF)。用个类比方法来看,日志文件(ldf)好比一趟火车,每一节车厢都是一个虚拟日志文件(VLF)。

    新葡亰496net 18

      那为什么SQL Server要把日志文件划分出多个VLF呢?因为SQL Server通过这种方式使得存储引擎管理事务日志更加有效。物理日志以虚拟日志(VLF)为最小单位进行增长、收缩和使用,维护日志的时候也只需维护少量的VLF,这样对于日志空间的重复利用也会更加高效。
      SQL Server把所有物理日志文件当成一个连续的文件看待,顺序写入日志记录,用完第一个,再用下一个。即第一个日志文件的当前空间,如果没有可分配的VLF时,就会使用下一个日志文件的VLF,直到最后一个日志文件也没有可分配的VLF时,会重新回到第一个日志开始增长。多个日志文件之间并不存在镜像关系,也没有重做日志组的概念。VLF的使用如下图:

    新葡亰496net 19

      VLF的数量以及每个VLF的大小由SQL Server根据日志文件的大小及增长率自动确定,即VLF没有固定大小,且日志文件所包含的VLF数不固定。在日志文件增长时,SQL Server也会重新规划VLFS的数量。
      SQL Server创建数据库时,根据日志文件(ldf)的大小,生成VLF的数量公式如下:

    新葡亰496net 20

      从上面的公式图看到如果每次日志文件一点一点增长,比如1M1M地增长,那么到64M的时候,就会生成64x4个VLF;但是如果日志文件直接增长64M,最终生成的VLF数量只有8个。如果这些日志文件由于许多微小增量而增长到很大,则它们将具有很多VLF,也就是日志文件碎片, 这会降低数据库启动以及日志备份和还原操作的速度。
      所以,当我们在创建数据库的时候需要设置合适的文件的大小,使得文件的大小起码可以应付一段时间的增长。同时,也不要一下子就去创建一个很大的日志文件,因为里面可能只包含很少的VLF,最后却发挥不了太大作用,反而导致磁盘空间不足的错误发生。
      一个VLF可以以下面4种状态之一存在:

    • active:包含活动的事务,活动的事务指未结束的事务。
    • recoverable:不包含活动事务,但数据库此时处于维护一个完整日志序列的状态,而这些VLF还未进行备份,所以这时不能转变为 reusable状态使得其被重用,如果被重用覆盖,一个完整的日志序列就不连续了。
    • reusable:完全恢复模式下已经备份,或者简单恢复模式下,未包含活动事务。
    • unused:这个VLF从未被用到。

    总结

    本篇文章从事务日志的逻辑和物理构架简单介绍了事务日志的构成.这是理解SQL Server如何利用日志保证持久性和数据备份恢复的基础。下一篇文章将会介绍SQL Server在操作中会如何使用到日志文件

     

    3 日志文件添加方式

    日志记录在 后缀名为 ldf的文件,允许有多个日志文件,但是不会并发分开记录日志,而是使用填充满一个日志文件后,再转向一个日志文件,线性操作日志文件。
    
    可以通过下方来添加 ldf文件,需要注意几个地方:
    
    • 初始大小,建议直接设置为 截断日志的期间内最大值,比如,某DB 恢复模式是完整模式,每隔半个小时做一次事务日志备份且截断日志,那么设置 日志文件大小的时候,取业务高峰期 每半小时的日志增长 最大值是5G,则可设置初始大小为 5G-7G之间;
    • 增长大小,无论是 按比例增长还是按照MB增长,都不要设置过小,建议每次增长在100Mb左右,减少使用到自动增长,在最初设置的初始大小就满足其增长需求 ,如果开始设置的 初始大小 偏小,不满足,可以挑一个业务低峰期,修改变大初始大小。每一次文件自动增长期间,都会对写入的日志造成堵塞,虽然时间很短,但是如果增长频繁,则会影响数据库操作;
    • 自动增长,建议设置为自动增长,但是前提定期监控日志的增长情况,避免磁盘空间不足,同时,如果恢复模式是 完整模式或者大容量模式,还需定期做日志备份截断日志,避免 事务日志已满的9002错误;
    • 路径选择,建议与 mdf 文件放在不同的磁盘上,分散IO,若是磁盘读写瓶颈不大,则可放在一个磁盘上;

    添加方式有2种,如下:

    [

    新葡亰496net 21

    复制代码

    ](javascript:void(0); "复制代码")

    <pre style="margin: 0px; padding: 0px; white-space: pre-wrap; word-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;"> 1 USE [master]
    2 GO
    3 ALTER DATABASE [backupdb]
    4 ADD LOG FILE (
    5 NAME = N'backupdb_log_1',
    6 FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAbackupdb_log_1.ldf' ,
    7 SIZE = 524288KB ,
    8 MAXSIZE = 1048576KB ,
    9 FILEGROWTH = 10240KB
    10 )
    11 GO</pre>

    [

    新葡亰496net 22

    复制代码

    ](javascript:void(0); "复制代码")

    新葡亰496net 23

    image.png

    回到顶部(go to top)

        本系列上一篇博文链接:SQL SERVER大话存储结构(4)_复合索引与包含索引

    2. 事务日志逻辑体系结构

    当针对数据库对象所做的任何修改保存到数据库之前,相应的数据库逻辑操作的记录首先会被记录到日志文件。这个记录会被按照先后顺序记录到日志文件的逻辑末尾,并分配一个全局唯一的日志序列号(log sequence number,简称LSN),这个序列号完全是按照顺序来的,如果日志中两个序列号LSN2>LSN1,则说明LSN2所在LSN1之后发生的。

    新葡亰496net 24

      数据库中的事务日志映射在一个或多个物理文件上。 从概念上讲,SQL Server 事务日志按逻辑运行,就好像事务日志是一串日志记录一样。 从物理上讲,日志记录序列被有效地存储在实现事务日志的物理文件集中。
       日志记录按创建时的串行序列存储。** 每条日志记录都包含其所属事务的 ID。** 对于每个事务,与事务相关联的所有日志记录通过使用可提高事务回滚速度的向后指针挨个链接在一个链中。

    新葡亰496net 25

    SQL Server用日志记录来保证事务的基本属性,及数据库恢复。

    活动日志
      MinLSN 是成功进行数据库范围内回滚所需的最早日志记录的日志序列号。 日志文件中从 MinLSN 到最后写入的日志记录这一部分称为日志的活动部分,或者称为活动日志。 这是进行数据库完整恢复所需的日志部分。 永远不能截断活动日志的任何部分。 所有的日志记录都必须从 MinLSN 之前的日志部分截断。

    新葡亰496net 26

      下图显示了具有两个活动事务的结束事务日志的简化版本。 检查点记录已压缩成单个记录。

    新葡亰496net 27

    LSN 148 是事务日志中的最后一条记录。 在处理 LSN 147 处记录的检查点时,Tran 1 已经提交,而 Tran 2 是唯一的活动事务。 这就使 Tran 2 的第一条日志记录成为执行最后一个检查点时处于活动状态的事务(处于活动状态即还未commit,只有未commit的事务才能rollback)的最旧日志记录。 这使 LSN 142(Tran 2 的开始事务记录)成为 MinLSN。
      活动日志必须包括所有未提交事务的每一部分。 如果应用程序开始执行一个事务但未提交或回滚,将会阻止数据库引擎推进 MinLSN。

    简介

    每一个SQL Server的数据库都会按照其修改数据(insert,update,delete)的顺序将对应的日志记录到日志文件.SQL Server使用了Write-Ahead logging技术来保证了事务日志的原子性和持久性.而这项技术不仅仅保证了ACID中的原子性(A)和持久性(D),还大大减少了IO操作,把对数据的修改提交到磁盘的工作交给lazy-writer和checkpoint.本文主要讲述了SQL Server修改数据时的过程以及相关的技术。

    预写式日志(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技术则需要每次提交数据时写入数据库:

    新葡亰496net 28

    而使用WAL合并写入,会大大减少磁盘IO:

    新葡亰496net 29

    也许你会有疑问,那每次对于修改的数据还是会写入日志文件.同样消耗磁盘IO。上篇文章讲过,每一笔写入日志的记录都是按照先后顺序,给定顺序编号的LSN进行写入的,日志只会写入到日志文件的逻辑末端。而不像数据那样,可能会写到磁盘的各个地方.所以,写入日志的开销会比写入数据的开销小很多。

     

    4 物理结构

     数据库的事务日志映射在一个或者多个的物理文件上,从概念上讲,日志文件是一系列的日志记录;从物理上讲,日志记录序列被有效的存储在实现事务日志的物理文件中。
    
    SQL Server 数据库引擎在内部将每一物理日志文件分成多个虚拟日志文件,即VLF(Virtual Log File),虚拟日志文件没有固定大小,且物理日志文件所包含的虚拟日志文件数不固定。数据库引擎在创建或扩展日志文件时**动态**选择虚拟日志文件的大小。数据库引擎尝试维护少量的虚拟文件。在扩展日志文件后,虚拟文件的大小是现有日志大小和新文件增量大小之和。
    
    只有当日志文件使用较小的 size 和 growth_increment 值定义时,虚拟日志文件才会影响系统性能。如果这些日志文件由于许多微小增量而增长到很大,则它们将具有很多虚拟日志文件。这会降低数据库启动以及日志备份和还原操作的速度。**建议您为日志文件分配一个接近于最终所需大小的size值,并且还要分配一个相对较大的 growth_increment 值。**
    
    管理员不能配置或设置虚拟日志文件的大小或数量,但是**在VLF影响系统性能的情况下,**可以尝试缩小,通过收缩日志文件的方式**。**
    

    dbcc loginfo(dbname) 返回的行数,即为 VLF 文件个数,当status为0时,即该文件没有被使用,还能写入LOG,2表示已被使用,并且无法重用,这个时候,可以通过 backup Log 的形式,备份并截断LOG文件,则可以回收 从最后一个0到最近一个2行的空间。

    收缩日志文件或者减少VLF文件的方式如下,需要先备份日志文件,才可以有效进行收缩,在没有备份日志文件的情况下,进行收缩,效果不大。详见以下代码:

    [

    新葡亰496net 30

    复制代码

    ](javascript:void(0); "复制代码")

    <pre style="margin: 0px; padding: 0px; white-space: pre-wrap; word-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;"> 1 #其行数及为VLF个数,status为0表示文件未用,为2表示已被使用,无法重用
    2 dbcc loginfo
    3
    4 #备份日志
    5 BACKUP LOG [backupdb]
    6 TO DISK = N'D:data20170215_backupdb_log.trn' WITH NOFORMAT, NOINIT, NAME = N'backupdb-事务日志 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
    7 GO
    8
    9 #收缩日志文件,根据日志文件名来收缩500Mb,建议收缩大小是合理大小,参考上文的 初始大小 判断
    10 USE [backupdb]
    11 GO
    12 SELECT name FROM sys.database_files WHERE type_desc='log'
    13 DBCC SHRINKFILE (N'jiankong_db_log' , 500)
    14 GO
    15
    16 #其行数及为VLF个数,VLF文件减少
    17 dbcc loginfo</pre>

    [

    新葡亰496net 31

    复制代码

    ](javascript:void(0); "复制代码")

    事务日志是一种回绕的文件。假设,数据库backupdb只有一个ldf文件,且刚好分成了5个虚拟日志,当我们开始使用数据库的时候,逻辑日志从物理日志的最开始端向末端记录,如下图。
    

    新葡亰496net 32

    image.png

    当出现checkpoint的时候,则会标注 最小恢复日志序列号 MinLSN,“MinLSN”是成功进行数据库范围内回滚所需的最早日志记录的日志序列号。如下图。
    

    新葡亰496net 33

    image.png

    在MinLSN之前的所有虚拟日志文件VLF都可以被截断,数据库会在以下两个事件后自行截断日志:
    
    • 简单恢复模式下,在检查点之后发生。

    • 在完整恢复模式或大容量日志恢复模式下,如果自上一次备份后生成检查点,则在日志备份后进行截断(除非是仅复制日志备份)。

      当截断日志的时候,这些VLF就可以被释放回收,逻辑日志的开头也会移动到最后一个被阶段VLF文件末端。

    新葡亰496net 34

    image.png

     假设这个时候,在MinLSN位置后,发生了一个事务,一直没有commit,导致VLF3,VLF4,VLF5都被使用,那么就会重用之前回收的VLF文件。
    

    新葡亰496net 35

    image.png

    正常情况下,如果经常截断旧的日志记录,保持逻辑日志的末端不到达逻辑日志的开头,满足下一个检查点之前船舰的所有新日志记录都有足够的空间存储,那么日志文件将永远不会被填满,保持一定的大小,可通过定期备份事务日志来达到。
    
    但是如果,逻辑日志的结尾跟开头碰面了,那么当磁盘空间足够的情况下,则按照 自动增大大小 指定的数量 增大日志文件,并在 物理日志文件中添加多个VLF文件;如果磁盘空间不足,比指定的 增量大小 要少,那么就会报错,出现9002错误,导致数据库无法进行所有写操作。
    

    回到顶部(go to top)

      

    日志截断

    物理日志的回绕
      事务日志是一种回绕的文件。 例如,假设有一个数据库,它包含一个分成四个虚拟日志文件的物理日志文件。 当创建数据库时,逻辑日志文件(具有日志记录的部分的VLF)物理日志文件(包含所有的VLF)的始端开始。 新日志记录被添加到逻辑日志的末端,然后向物理日志的末端扩张。日志截断将释放记录全部在最小恢复日志序列号 (MinLSN) 之前出现的所有虚拟日志,被截断的日志部分标记为可重用。

    新葡亰496net 36

    当逻辑日志的末端到达物理日志文件的末端时,新的日志记录将回绕到物理日志文件的始端。

    新葡亰496net 37

    这个循环不断重复,只要逻辑日志的末端不到达逻辑日志的始端。
      如果经常截断旧的日志记录,始终为到下一个检查点前创建的所有新日志记录保留足够的空间,则日志永远不会填满。

    日志截断
      日志截断主要用于阻止日志填充。日志截断把数据库日志文件中不包含活动事务(未结束的事务)的VLF状态修改为reusable,释放逻辑日志中的空间以便物理事务日志重用这些空间。如果事务日志从不截断,它最终将填满分配给物理日志文件的所有磁盘空间。 但是,在截断日志前,必须执行检查点操作,将当前内存中的脏页和事务日志信息从内存写入磁盘。
      下列各图显示了截断前后的事务日志。 第一个图显示了从未截断的事务日志。 当前,逻辑日志使用四个虚拟日志文件。 逻辑日志开始于第一个逻辑日志文件的前面,并结束于虚拟日志 4。 MinLSN 记录位于虚拟日志 3 中。 虚拟日志 1 和虚拟日志 2 仅包含不活动的日志记录。 这些记录可以截断。 虚拟日志 5 仍未使用,不属于当前逻辑日志。

    新葡亰496net 38

    第二个图显示了日志截断后的情形。 已释放虚拟日志 1 和虚拟日志 2 以供重新使用。 现在,逻辑日志开始于虚拟日志 3 的开头。 虚拟日志 5 仍未使用,它不属于当前逻辑日志。

    新葡亰496net 39

      除非由于某些原因导致延迟,否则将在以下事件后自动发生日志截断:

    • 简单恢复模式下,在检查点之后发生。
    • 完整恢复模式或大容量日志恢复模式下,在日志备份之后发生(如果自上次备份后出现检查点)。

    SQL Server修改数据的步骤

    SQL Server对于数据的修改,会分为以下几个步骤顺序执行:

    1.在SQL Server的缓冲区的日志中写入”Begin Tran”记录

    2.在SQL Server的缓冲区的日志页写入要修改的信息

    3.在SQL Server的缓冲区将要修改的数据写入数据页

    4.在SQL Server的缓冲区的日志中写入”Commit”记录

    5.将缓冲区的日志写入日志文件

    6.发送确认信息(ACK)到客户端(SMSS,ODBC等)

     

    可以看到,事务日志并不是一步步写入磁盘.而是首先写入缓冲区后,一次性写入日志到磁盘.这样既能在日志写入磁盘这块减少IO,还能保证日志LSN的顺序.

    上面的步骤可以看出,即使事务已经到了Commit阶段,也仅仅只是把缓冲区的日志页写入日志,并没有把数据写入数据库.那将要修改的数据页写入数据库是在何时发生的呢?

     

    5 延迟日志截断原因

     日志截断会由于多种因素发生延迟。可查询sys.databases目录视图的 **log_reuse_wait** 和 **log_reuse_wait_desc** 列来发现是什么(如果有)阻止了日志的截断。 下表对这些列的值进行了说明。
    

    新葡亰496net 40

    image

    回到顶部(go to top)


    如何查看事务日志记录

    大家知道在完整恢复模式下,SQLSERVER会记录每个事务所做的操作,这些记录会存储在事务日志里,那么事务日志记录怎么查看,里面都记录了些什么?
      事务日志记录里很多东西可以看的,里面记录了非常详细的数据库活动信息。打开可以利用下面SQL语句来查看所在数据库的事务日志记录:

    USE [GPOSDB] --要查看事务日志记录的数据库
    GO
    SELECT * FROM [sys].[fn_dblog](NULL,NULL)
    

    新葡亰496net 41

    在SSMS中执行查询日志操作之后可以看到所有的日志记录,我截取了部分的结果,图中有几列,下面说明一下其中几列的意思:

    • CurrentLSN:当前LSN号,事务日志中的每个记录都由一个唯一的日志序列号 (LSN) 标识。LSN 是这样排序的:如果 LSN2 大于 LSN1,则 LSN2 所标识的日志记录描述的更改发生在日志记录 LSN1 描述的更改之后。
    • Operation列中记录了对应的LSN所做的操作。下面列出Operation几种比较常见而重要的值:
    • LOP_BEGIN_XACT 事务的开始
    • LOP_LOCK_XACT 获取锁
    • LOP_MODIFY_ROW 修改行(具体修改的对象可以查看AllocUnitName)
    • LOP_COMMIT_XACT 提交事务
    • LOP_DELETE_ROWS 删除数据
    • LOP_INSERT_ROWS 插入数据
    • Context:操作的上下文。
    • Transaction Name显示了创建的数据库的名称。
    • TransactoinID:事务ID号。
    • Log Record Fixed Length:LSN记录的所占虚拟日志文件的固定长度。
    • Previous LSN:前一个LSN号。
    • AllocUnitID:修改的那条数据所属分配单元ID
    • AllocUnitName:修改了数据的表名。
    • Slot ID:数据所在数据页面的第几条记录
    • PartitionID:数据所在数据页面的所在分区ID

    Lazy Writer和CheckPoint

    上面提到,SQL Server修改数据的步骤中并没有包含将数据实际写入到磁盘的过程.实际上,将缓冲区内的页写入到磁盘是通过两个过程中的一个实现:

    这两个过程分别为:

    1.CheckPoint

    2.Lazy Writer

    任何在缓冲区被修改的页都会被标记为“脏”页。将这个脏页写入到数据磁盘就是CheckPoint或者Lazy Writer的工作.

    当事务遇到Commit时,仅仅是将缓冲区的所有日志页写入磁盘中的日志文件:

    新葡亰496net 42

     

    而直到Lazy Writer或CheckPoint时,才真正将缓冲区的数据页写入磁盘文件:

    新葡亰496net 43

     

    前面说过,日志文件中的LSN号是可以比较的,如果LSN2>LSN1,则说明LSN2的发生时间晚于LSN1的发生时间。CheckPoint或Lazy Writer通过将日志文件末尾的LSN号和缓冲区中数据文件的LSN进行对比,只有缓冲区内LSN号小于日志文件末尾的LSN号的数据才会被写入到磁盘中的数据库。因此确保了WAL(在数据写入到数据库之前,先写入日志)。

     

    6 管理事务日志

    定期监控日志文件的大小跟实际使用大小,以防日志增长异常,占满磁盘空间,可通过以下两种方式查看 日志文件使用情况.
    

    <pre style="margin: 0px; padding: 0px; white-space: pre-wrap; word-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">1 #查看日志使用情况,文件大小及实际使用大小
    2 dbcc sqlperf(logspace)
    3
    4 #查看文件相关信息
    5 select name,physical_name,size8.0/1024 size_Mb, from sys.database_files</pre>

    定期日志备份,两个备份的间隔是运行丢失数据的时间跨度,不要过于频繁备份,会对数据库IO造成一定影响。
    

    <pre style="margin: 0px; padding: 0px; white-space: pre-wrap; word-wrap: break-word; font-family: "Courier New" !important; font-size: 12px !important;">1 BACKUP LOG [backupdb]
    2 TO DISK = N'D:data20170215_backupdb_log.trn' WITH NOFORMAT, NOINIT, NAME = N'backupdb-事务日志 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
    3 GO</pre>

    事务尽可能短,避免长时间开启事务,或者忘记commit/roll back;
    
    解决事务日志已满问题(9002错误)
    
    • 若是限制了文件最大值,在磁盘空间有剩余的情况下,增加日志文件的大小。
    • 释放磁盘空间以便日志可以自动增长。
    • 在其他磁盘上添加日志文件。
    • 备份日志后,收缩日志。
    • 将日志文件移到具有足够空间的磁盘驱动器。

    参考文档:

    https://msdn.microsoft.com/zh-cn/library/ms189275.aspx

    https://msdn.microsoft.com/zh-cn/library/ms190925(v=sql.110).aspx

    https://msdn.microsoft.com/zh-cn/library/ms190422(v=sql.110).aspx

    https://blogs.msdn.microsoft.com/apgcdsd/2011/12/29/943/

    https://msdn.microsoft.com/zh-cn/library/ms365418(v=sql.110).aspx

    https://msdn.microsoft.com/zh-cn/library/ms175495(v=sql.110).aspx

    如果转载,请注明博文来源: www.cnblogs.com/xinysu/ ,版权归 博客园 苏家小萝卜 所有。望各位支持!


    Lazy Writer和CheckPoint的区别

    Lazy Writer和CheckPoint往往容易混淆。因为Lazy Writer和CheckPoint都是将缓冲区内的“脏”页写入到磁盘文件当中。但这也仅仅是他们唯一的相同点了。

    Lazy Writer存在的目的是对缓冲区进行管理。当缓冲区达到某一临界值时,Lazy Writer会将缓冲区内的脏页存入磁盘文件中,而将未修改的页释放并回收资源。

    而CheckPoint存在的意义是减少服务器的恢复时间(Recovery Time).CheckPoint就像他的名字指示的那样,是一个存档点.CheckPoint会定期发生.来将缓冲区内的“脏”页写入磁盘。但不像Lazy Writer,Checkpoint对SQL Server的内存管理毫无兴趣。所以CheckPoint也就意味着在这个点之前的所有修改都已经保存到了磁盘.这里要注意的是:CheckPoint会将所有缓冲区的脏页写入磁盘,不管脏页中的数据是否已经Commit。这意味着有可能已经写入磁盘的“脏页”会在之后回滚(RollBack).不过不用担心,如果数据回滚,SQL Server会将缓冲区内的页再次修改,并写入磁盘。

    通过CheckPoint的运作机制可以看出,CheckPoint的间歇(Recovery Interval)长短有可能会对性能产生影响。这个CheckPoint的间歇是一个服务器级别的参数。可以通过sp_config进行配置,也可以在SSMS中进行配置:

    新葡亰496net 44

    恢复间歇的默认参数是0,意味着由SQL Server来管理这个回复间隔。而自己设置恢复间隔也是需要根据具体情况来进行界定。更短的恢复间歇意味这更短的恢复时间和更多的磁盘IO,而更长的恢复间歇则带来更少的磁盘IO占用和更长的恢复时间.

    除了自动CheckPoint之外,CheckPoint还会发生在Alter DataBase以及关闭SQL Server服务器时。sysadmin和db_backupoperator组的成员以及db_owner也可以使用CheckPoint指令来手动保存CheckPoint:

    新葡亰496net 45

    通过指定CheckPoint后的参数,SQL Server会按照这个时间来完成CheckPoint过程,如果时间指定的短,则SQL Server会使用更多的资源优先完成CheckPoint过程。

    通常情况下,将“脏”页写入磁盘的工作,Lazy Writer要做的比CheckPoint会多出许多。

     

    **1 基本介绍**

        每个数据库都具有事务日志,用于记录所有事物以及每个事物对数据库所作的操作。

        日志的记录形式需要根据数据库的恢复模式来确定,数据库恢复模式有三种:

    • 完整模式,完全记录事物日志,需要定期进行日志备份。
    • 大容量日志模式,适用于批量操作的数据库,可以以更压缩的方式处理日志,需要定期进行日志备份。
    • 简单模式,也有日志文件,只是该模式下可以通过checkpoint自动重用virtual log file,所以日志文件会处于一直重复使用的过程,保持一定大小,但是,如果有一个事务启动,很久没有commit,那么从这个事务开始到最后commit的时间段内的事务日志存储空间都无法checpoint自动重用,这时,你很可能看到一个很大的日志文件;注意,简单模式下是无法进行日志备份

       数据库里边,任何对数据库的读写都是在内存页中找到对应的数据也,再做修改,如果内存页中不存在数据页,则从磁盘加载如内存中。当一个修改操作发生时,修改的将是内存页中对应的数据页面,同时也会实时记录到日后文件ldf中。那么,什么时候数据会被同步到mdf文件呢,只有以下三种情况:

    • 做checkpoint时,后续会专门整理checkpoint的相应文章;
    • Lazy write运行时,即内存出现压力,需要把内存中的数据页写入到磁盘,腾出内存空间;
    • eager write时,即发生bulk insert和select into操作时。

        DB中的事务日志记录,可以给我们带来很多好处,它可以支持以下操作:

    • 恢复个别的事务。
    • 在 SQL Server 启动时恢复所有未完成的事务。
    • 将还原的数据库、文件、文件组或页前滚至故障点。
    • 支持事务复制。
    • 支持高可用性和灾难恢复解决方案:AlwaysOn 可用性组、数据库镜像和日志传送。

    总结

    本文简单介绍了WAL的概念和修改数据库对象时,日志所扮演的角色。还分别介绍了CheckPoint和Lazy Writer,对于这些概念的理解是理解SQL Server DBA工作的基础。下篇文章将会讲述在简单恢复模式下日志的机制

     

    简介

    在简单恢复模式下,日志文件的作用仅仅是保证了SQL Server事务的ACID属性。并不承担具体的恢复数据的角色。正如”简单”这个词的字面意思一样,数据的备份和恢复仅仅是依赖于手动备份和恢复.在开始文章之前,首先要了解SQL Server提供的几种不同备份类型。

     

    **2 对数据库启动的影响**

        当数据库重启或者还原到最后的时候,数据库都会进入 recovery状态,正常情况下,这个状态持续时间在几十秒间,但是特殊情况下,它会花费非常长的时间,甚至几个小时,如果这个步骤失败,数据库则进入到挂起 suspect状态,无法正常提供使用。

     

        那么,当数据库进入 recovery 的时候,它在操作些什么呢?

     

        SQL SERVER日志会记录所有修改记录(数据的修改情况,不包含SQL语句),包括Begin Transaction和Commit / Rollback Transaction 操作。由于对事务日志的修改,要比数据文件的修改要快,所有会出现,数据修改更新到了日志文件,但是还没有落盘到数据文件,那么这个时候数据库就处于recovery状态,同时对事务日志最近的一个checkpoint点以后的所有数据修改记录做以下检查:

     

    新葡亰496net 46

        所有检查结束后,则会对数据库做一个checkpoint的表示,并写入事务日志中,表明日志文件跟数据文件已经同步结束,完成了recovery过程,数据库可正常提供使用。这里需要注意一点,如果你数据库最近一次checkpoint到现在的修改操作足够多,那么将会耗费相对较长时间来检查,同时也能够在 error log中看到百分比标识的recovery完成进展,避免漫无目的的等待。

    Error Log的检查,可以通过图形界面(见下图)查看当前日志,也可以运行xp_readerrorlog 查询。

     1 /*
     2 xp_readerrorlog参数说明
     3 1. 存档编号
     4 2. 日志类型(1为SQL Server日志,2为SQL Agent日志)
     5 3. 查询包含的字符串
     6 4. 查询包含的字符串
     7 5. LogDate开始时间
     8 6. LogDate结束时间
     9 7. 结果排序,按LogDate排序(可以为降序"Desc" Or 升序"Asc"),默认升序
    10 */
    11  
    12 Exec xp_readerrorlog 0,1,Null,Null,'2017-02-16 10:53:32.300','2017-02-16 12:53:32.300'
    

    新葡亰496net 47

     

         假设出现这种情况,由于上线的重要程度远远重要过 数据丢失的情况,并且你跟所有部门沟通确认 可以承担 data file跟log file之间的差异数据的丢失,那么你可以按以下步骤操作,严重建议不要这么操作,因为会带来不可预估的数据丢失情况,如果你命悬一线,真打算放弃这部分数据,那么,可以按照以下操作:

     1 #设置数据库单用户
     2 alter database backupdb set single_user with rollback immediate
     3 
     4 #设置数据库紧急状态
     5 alter database backupdb set emergency with rollback immediate
     6 
     7 #获取事务日志的物理名和逻辑名后,重建日志文件
     8 select name,physical_name from sys.master_fiels where database_id=db_id('backupdb')
     9 alter database backupdb rebuild log on (name='事务日志的逻辑名',filename='事务日志的物理名词')
    10 
    11 #设置数据库online
    12 alter database backupdb set online with rollback immediate
    13 
    14 #设置数据库为多用户
    15 alter database backupdb set multi_user with rollback immediate
    

    SQL Server提供的几种备份类型

    SQL Server所提供的几种备份类型基本可以分为以下三种(文件和文件组备份以及部分备份不在本文讨论之列):

    1.完整(Full)备份:直接将所备份的数据的所有区(Extent)进行复制。这里值得注意的有2点:

    • 完整备份并不像其名字“完整”那样备份所有部分,而是仅备份数据库本身,而不备份日志(虽然仅仅备份少量日志用于同步)
    • 完整备份在备份期间,数据库是可用的。完整备份会记录开始备份时的LSN号,结束备份时的LSN号,以便在备份结束时将这期间的改动应用到备份,所以完整备份后数据的时间点是备份结束的时间。

     

    2.差异(Differential)备份:只备份上次完整备份后,做修改的部分。备份单位是区(Extent)。意味着某个区内即使只有一页做了变动,则在差异备份里会被体现.差异备份依靠一个BitMap进行维护,一个Bit对应一个区,自上次完整备份后,被修改的区会被置为1,而BitMap中被置为1对应的区会被差异备份所备份。而到下一次完整备份后,BitMap中所有的Bit都会被重置为0。

     

    3.日志(Log)备份:仅仅备份自上次完整备份或日志备份之后的记录。在简单模式下,日志备份毫无意义(SQL Server不允许在简单恢复模式下备份日志),下文会说明在简单恢复模式下,为什么日志备份没有意义。

     

    3 日志文件添加方式

        日志记录在 后缀名为 ldf的文件,允许有多个日志文件,但是不会并发分开记录日志,而是使用填充满一个日志文件后,再转向一个日志文件,线性操作日志文件。

        可以通过下方来添加 ldf文件,需要注意几个地方:

    • 初始大小,建议直接设置为 截断日志的期间内最大值,比如,某DB 恢复模式是完整模式,每隔半个小时做一次事务日志备份且截断日志,那么设置 日志文件大小的时候,取业务高峰期 每半小时的日志增长 最大值是5G,则可设置初始大小为 5G-7G之间;
    • 增长大小,无论是 按比例增长还是按照MB增长,都不要设置过小,建议每次增长在100Mb左右,减少使用到自动增长,在最初设置的初始大小就满足其增长需求 ,如果开始设置的 初始大小 偏小,不满足,可以挑一个业务低峰期,修改变大初始大小。每一次文件自动增长期间,都会对写入的日志造成堵塞,虽然时间很短,但是如果增长频繁,则会影响数据库操作;
    • 自动增长,建议设置为自动增长,但是前提定期监控日志的增长情况,避免磁盘空间不足,同时,如果恢复模式是 完整模式或者大容量模式,还需定期做日志备份截断日志,避免 事务日志已满的9002错误;
    • 路径选择,建议与 mdf 文件放在不同的磁盘上,分散IO,若是磁盘读写瓶颈不大,则可放在一个磁盘上;

    添加方式有2种,如下:

     1 USE [master]
     2 GO
     3 ALTER DATABASE [backupdb]
     4 ADD LOG FILE (
     5                NAME = N'backupdb_log_1',
     6                FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAbackupdb_log_1.ldf' ,
     7                         SIZE = 524288KB ,
     8                         MAXSIZE = 1048576KB ,
     9                         FILEGROWTH = 10240KB
    10                       )
    11 GO
    

    新葡亰496net 48

    简单恢复模式(Simple Recovery Mode)

     

    在简单恢复模式下,日志仅仅是为了保证SQL Server事务的ACID。并没有恢复数据的功能.

    比如,我们有一个备份计划,如下:

    新葡亰496net 49

    我们在每周一0点做一次完整备份,在周三0点和周五0点分别做差异备份。在简单恢复模式下,如果周六数据库崩溃。我们的恢复计划只有根据周一0点的做的完整备份恢复后,再利用周五0点的差异备份进行恢复.而周五0点之后到服务器崩溃期间所有的数据将会丢失。

    正如”简单”这个词所涵盖的意思,在简单恢复模式下,日志可以完全不用管理。而备份和恢复完全依赖于我们自己的完整和差异备份.

    恢复模式是一个数据库级别的参数,可以通过在SSMS里或通过SQL语句进行配置:

    新葡亰496net 50

     

     

    4 物理结构

         数据库的事务日志映射在一个或者多个的物理文件上,从概念上讲,日志文件是一系列的日志记录;从物理上讲,日志记录序列被有效的存储在实现事务日志的物理文件中。

        SQL Server 数据库引擎在内部将每一物理日志文件分成多个虚拟日志文件,即VLF(Virtual Log File),虚拟日志文件没有固定大小,且物理日志文件所包含的虚拟日志文件数不固定。数据库引擎在创建或扩展日志文件时动态选择虚拟日志文件的大小。数据库引擎尝试维护少量的虚拟文件。在扩展日志文件后,虚拟文件的大小是现有日志大小和新文件增量大小之和。

        只有当日志文件使用较小的 size 和 growth_increment 值定义时,虚拟日志文件才会影响系统性能。如果这些日志文件由于许多微小增量而增长到很大,则它们将具有很多虚拟日志文件。这会降低数据库启动以及日志备份和还原操作的速度。建议您为日志文件分配一个接近于最终所需大小的size值,并且还要分配一个相对较大的 growth_increment 值。

        管理员不能配置或设置虚拟日志文件的大小或数量,但是在VLF影响系统性能的情况下,可以尝试缩小,通过收缩日志文件的方式

    dbcc loginfo(dbname) 返回的行数,即为 VLF 文件个数,当status为0时,即该文件没有被使用,还能写入LOG,2表示已被使用,并且无法重用,这个时候,可以通过 backup Log 的形式,备份并截断LOG文件,则可以回收 从最后一个0到最近一个2行的空间。

    收缩日志文件或者减少VLF文件的方式如下,需要先备份日志文件,才可以有效进行收缩,在没有备份日志文件的情况下,进行收缩,效果不大。详见以下代码:

     

     1 #其行数及为VLF个数,status为0表示文件未用,为2表示已被使用,无法重用
     2 dbcc loginfo 
     3 
     4 #备份日志
     5 BACKUP LOG [backupdb]
     6 TO  DISK = N'D:data20170215_backupdb_log.trn' WITH NOFORMAT, NOINIT,  NAME = N'backupdb-事务日志  备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
     7 GO
     8 
     9 #收缩日志文件,根据日志文件名来收缩500Mb,建议收缩大小是合理大小,参考上文的 初始大小 判断
    10 USE [backupdb]
    11 GO
    12 SELECT name FROM sys.database_files WHERE type_desc='log'
    13 DBCC SHRINKFILE (N'jiankong_db_log' , 500)
    14 GO
    15 
    16 #其行数及为VLF个数,VLF文件减少
    17 dbcc loginfo
    

        事务日志是一种回绕的文件。假设,数据库backupdb只有一个ldf文件,且刚好分成了5个虚拟日志,当我们开始使用数据库的时候,逻辑日志从物理日志的最开始端向末端记录,如下图。

    新葡亰496net 51

        当出现checkpoint的时候,则会标注 最小恢复日志序列号 MinLSN,“MinLSN”是成功进行数据库范围内回滚所需的最早日志记录的日志序列号。如下图。

    新葡亰496net 52

     

        在MinLSN之前的所有虚拟日志文件VLF都可以被截断,数据库会在以下两个事件后自行截断日志:

    • 简单恢复模式下,在检查点之后发生。

    • 在完整恢复模式或大容量日志恢复模式下,如果自上一次备份后生成检查点,则在日志备份后进行截断(除非是仅复制日志备份)。

        当截断日志的时候,这些VLF就可以被释放回收,逻辑日志的开头也会移动到最后一个被阶段VLF文件末端。

    新葡亰496net 53

         假设这个时候,在MinLSN位置后,发生了一个事务,一直没有commit,导致VLF3,VLF4,VLF5都被使用,那么就会重用之前回收的VLF文件。

     新葡亰496net 54

     

        正常情况下,如果经常截断旧的日志记录,保持逻辑日志的末端不到达逻辑日志的开头,满足下一个检查点之前船舰的所有新日志记录都有足够的空间存储,那么日志文件将永远不会被填满,保持一定的大小,可通过定期备份事务日志来达到。

        但是如果,逻辑日志的结尾跟开头碰面了,那么当磁盘空间足够的情况下,则按照 自动增大大小 指定的数量 增大日志文件,并在 物理日志文件中添加多个VLF文件;如果磁盘空间不足,比指定的 增量大小 要少,那么就会报错,出现9002错误,导致数据库无法进行所有写操作。

    简单恢复模式下日志的空间使用

    在本系列文章的第一篇文章提到过,日志文件会划分成多个VLF进行管理,在逻辑上记录是线性的,给每个记录一个顺序的,唯一的LSN。

    而在简单恢复模式下,为了保证事务的持久性,那些有可能回滚的数据会被写入日志。这些日志需要被暂时保存在日志以确保在特定条件下事务可以顺利回滚。这就涉及到了一个概念—最小恢复LSN(Minimum Recovery LSN(MinLSN) )

    MinLsn是在还未结束的事务记录在日志中最小的LSN号,MinLSN是下列三者之一的最小值:

    • CheckPoint的开始LSN

    • 还未结束的事务在日志的最小LSN

    • 尚未传递给分发数据库的最早的复制事务起点的 LSN.

     

    下图是一个日志的片段:

    新葡亰496net 55

    (图片摘自MSDN)

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

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

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

     

    新葡亰496net 56

     

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

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

    1. 活动(Active) –在VLF 上存储的任意一条LSN是活动的时,则VLF则为活动状态,即使一个200M的VLF只包含了一条LSN,如上图的VLF3
    2. 可恢复(Recoverable) – VLF是不活动的,VLF上不包含活动LSN,但还未被截断(truncated)
    3. 可重用(Reusable) – VLF是不活动的,VLF上不包含活动LSN,已经被截断(truncated),可以重用
    4. 未使用(Unused) – VLF是不活动的,并且还未被使用过

    概念如下图:

    新葡亰496net 57

    而所谓的截断(truncated)只是将可恢复状态的VLF转换到可重用状态。在简单恢复模式下,每一次CheckPoint会引发一次截断.而每一次CheckPoint都会将MinLSN向后推.所以当事务结束后并且过了CheckPoint点,其相关的日志将会被截断以便重复利用空间。

    在日志达到日志文件(ldf文件)末尾时,也就是上图的VLF8时,会重新循环到VLF1开始,以便让空间进行重复利用.所以日志虽然可以从物理顺序上是从VLF1到VLF8,但逻辑顺序可以是从VLF6开始到VLF2结束:

    新葡亰496net 58

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

     

    5 延迟日志截断原因

         日志截断会由于多种因素发生延迟。可查询sys.databases目录视图的 log_reuse_waitlog_reuse_wait_desc 列来发现是什么(如果有)阻止了日志的截断。 下表对这些列的值进行了说明。

     新葡亰496net 59

    总结

    本文介绍了简单恢复模式下日志的原理,并简单的引出了一些备份或者恢复数据的基础。而实际上,除了在开发或测试环境下。使用简单恢复模式的场景并不多,因为在现实生活中,在生产环境允许几个小时的数据丢失的场景几乎没有.下篇文章将会讲述在完整恢复模式下,日志的作用

     

    6 管理事务日志

        定期监控日志文件的大小跟实际使用大小,以防日志增长异常,占满磁盘空间,可通过以下两种方式查看 日志文件使用情况.

    1 #查看日志使用情况,文件大小及实际使用大小
    2 dbcc sqlperf(logspace)
    3 
    4 #查看文件相关信息
    5 select name,physical_name,size*8.0/1024 size_Mb,* from sys.database_files
    

        定期日志备份,两个备份的间隔是运行丢失数据的时间跨度,不要过于频繁备份,会对数据库IO造成一定影响。

    1 BACKUP LOG [backupdb]
    2 TO  DISK = N'D:data20170215_backupdb_log.trn' WITH NOFORMAT, NOINIT,  NAME = N'backupdb-事务日志  备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    3 GO
    

        事务尽可能短,避免长时间开启事务,或者忘记commit/roll back;

        解决事务日志已满问题(9002错误)

    • 若是限制了文件最大值,在磁盘空间有剩余的情况下,增加日志文件的大小。
    • 释放磁盘空间以便日志可以自动增长。
    • 在其他磁盘上添加日志文件。
    • 备份日志后,收缩日志。
    • 将日志文件移到具有足够空间的磁盘驱动器。

     

     

    参考文档:

     

    简介

    生产环境下的数据是如果可以写在资产负债表上的话,我想这个资产所占的数额一定不会小。而墨菲定律(事情如果有变坏的可能,无论这种可能性有多小,它总会发生)仿佛是给DBA量身定做的。在上篇文章介绍的简单恢复模式下,从最近一次备份到当前的数据都会存在丢失的风险。而完整备份模式使得数据丢失的风险大大减少。本文主要介绍在完整备份模式下概念原理和日志所处的角色。

     

    完整(Full)恢复模式

    完整恢复模式通过将对数据库的任何修改记录到日志来给予数据最大程度的保护。在完整恢复模式下,日志的作用不仅仅是保证了数据库事务的ACID。并且还可以使数据恢复到在日志范围内的任何时间点。

    在上一篇文章中说过,在简单恢复模式下,日志几乎是不用进行管理的。每一次CheckPoint都有可能截断日志,从而来回收不活动的VLF以便重复利用空间。因此在简单恢复模式下,日志的空间使用几乎可以不去考虑。与之相反,在完整恢复模式下,日志作为恢复数据的重要组成部分,日志的管理和对日志空间使用的管理则需要重视。

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

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

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

    通过从MSDN中摘自的下图可以看到:

     

    新葡亰496net 60

     

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

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

     

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

    大容量恢复模式在很多地方和完整恢复模式相同。但由于在完整恢复模式下,对数据库的每一项操作都会记录在日志中。而对于某些大量数据的导入导出操作,无疑会在日志中留下大量记录。很多情况下,我们并不需要将这些信息记录在日志中。

    而大容量日志恢复模式作为完整恢复模式的备选方案。微软推荐的最佳实践是在进行大量数据操作时(比如索引的创建和rebuilt,select into操作等),暂时由完整恢复模式切换到大容量恢复模式来节省日志。这个转换并不会破坏日志链。

    本文不会深入探讨这个模式,仅仅是对这个概念做简单解释。假设我要插入一批数据,则完整恢复模式和大容量日志恢复模式在日志中所记录的信息如下:

    新葡亰496net 61

    由此可以看出,在日志中,大容量恢复模式将这类操作变为一个原子。所以在大容量日志恢复模式下,不能redo大容量日志中的这类操作(select into之类的)

     

    日志链(Log Chain)

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

    新葡亰496net 62

    假设上面两个日志备份可以简单抽象成如上2个备份,则日志备份1的末尾LSN必须大于等于日志备份二的第一个LSN(通常情况下是第一个末尾LSN等于第二个日志备份的第一个LSN,但由于存在“只备份日志”选项只备份日志,并不截断日志,所以有可能重叠)。则这两个备份的日志链是连续的。

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

    新葡亰496net 63

    可以看出,第一次完整备份后,备份多次事务日志,每一个事务日志的开始LSN都等于上一个事务日志的结束LSN。因此可以从第一次完整备份开始,恢复到最后一个日志备份期间的任何时间点。

     

    完整的日志链以第一次完整备份或由简单恢复模式转为完整或大容量日志模式开始,到当前的时间点结束。

    而从日志恢复数据要求从最近一次完整或差异备份到所恢复的时间点之间的日志链是连续的。

     

    恢复次序

    从备份恢复数据需要经历如下几步骤:

    1.复制数据阶段:从完整备份和差异备份中将数据,索引页和日志复制到被恢复数据库文件。

    2.Redo(roll forward)阶段:将记录在日志中的事务应用到从备份中复制过来的数据。使数据Roll Forward到指定的时间点.这个阶段完成后,数据库还处于不可使用阶段:

    如图: 新葡亰496net 64

    上面两部就是Restore

    3.Undo(Roll Back)阶段:这也是传说中的Recovery,将任何未提交的事务回滚。这个阶段过后,数据库处于可用状态。任何后续备份将不能接着应用到当前数据库。

    这个概念比如:

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

    新葡亰496net 65

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

     

    总结

    本文简单介绍了在完整恢复模式下,日志的作用以及对数据恢复的一些概念。理解完整恢复模式的概念对于减少数据丢失的风险是无可替代的。

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496netServer事务日志及其组织,SERVER大话存

    关键词: