您的位置:新葡亰496net > 网络数据库 > 新葡亰496net:日志文件结构及误操作数据找回,

新葡亰496net:日志文件结构及误操作数据找回,

发布时间:2019-06-16 03:55编辑:网络数据库浏览(73)

    一. 概述

      1.1  日志文件与数据文件一致性

    由于日志是顺序写入,而修改数据分散在数据库各个页面,属于随机写入,而磁盘顺序写入速度远高于随机写入,因此主流数据库都采用预写日志的方式来确保数据完整性

    MySQL的redo log结构和SQL Server的log结构对比

    innodb 存储引擎 mysql技术内幕

    log buffer根据一定规则将内存中的log block刷写到磁盘,这个规则是

    1、事务提交时

    2、当log buffer 中有一半的内存空间已经被使用时

    3、checkpoint时

    跟sqlserver一样

     

     

    mysql的重做日志的logblock是512字节,跟磁盘的扇区一样,重做日志写入可以保证原子性,不需要double write技术

     

    mysql的重做log record,由于innodb存储引擎的存储管理是基于页的,所以其重做日志格式也是基于页面的,跟SqlServer一样

     

    重装日志记录通用页头格式
    1、redo_log_type:重做日志类型
    2、space:表空间id
    3、page_no:页的偏移量

     

    P302  lsn记录的是重做日志的总量,其单位是字节

    current lsn>flushed lsn>checkpoint lsn

     

    undo只是逻辑日志,只是将数据库逻辑恢复到原来的样子,所有修改都被逻辑取消,事务分配的新的一个段,表空间会增大

    用户执行rollback时,会将插入的事务进行回滚,但是表空间并不会因此收缩(跟sqlserver一样)。

     

    mysql的MVCC由共享表空间ibdata1的undo回滚段实现,读取undo里面的行版本信息

     

    每做一个undo操作都会产生redo,因为undo也要持久化

     

    f

    1新葡亰496net 1

    f

    2新葡亰496net 2

    f

    3新葡亰496net 3

    f

    4新葡亰496net 4

    f

    5新葡亰496net 5

    f

    6新葡亰496net 6

    f

    7新葡亰496net 7

    f

     

     

    sqlserver 

     

    事务对数据库中每次修改都会分解成多个多个原子层级的条目被记录到持久存储中,这些条目就是所谓的日志记录(Log Record),我们可以通过fn_dblog来查看这些条目。如图2所示。

    新葡亰496net 8

    图2.Fn_dblog

     

        每个日志记录都会被背赋予一个唯一的顺序编号,这个编号大小为10字节,由三部分组成,分别为:

    •     VLF顺序号(4字节)
    •     Log Block顺序号(4字节)
    •     Log Block内的顺序编号(2字节)

     

        因此,由于VLF是不断递增的(同一个VLF被复用会导致编号改变),因此LSN序号也是不断递增的。因此,通过上面的LSN结构不难发现,如果比VLF更小的粒度并不是直接对应LOG RECORD,而是LOG Block。Log Block是日志写入持久化存储的最小单位,Log Block的大小从512字节到60K不等,这取决于事务的大小,那些在内存还未被写入持久化存储的Log Block也就是所谓的In-Flight日志。以下两个因素决定Log Block的大小:

    • 事务提交或回滚
    • Log Block满60K会强制Flush到持久化存储,以保证WAL

        因此当一个事务很大时(比如说大面积update),每60K就会成为一个Log Block写入持久化存储。而对于很多小事务,提交或回滚就会称为一个Block写入持久化存储,因此根据事务的大小,LOG Block的大小也会不同。值得疑惑的是,因为磁盘上分配单元的大小是2的N次方,因此最接近LOG BLOCK的大小应该是64K,而SQL Server为什么不把Log Block设定为64K呢。这样可以更优化IO。

        VLF和Log Block和Log Record的关系如图3所示。

        新葡亰496net 9

        图3.三者之间的关系

       

        从比较高的层级了解了日志之后,我们再仔细了解日志中应该存储的关键信息,每条Log Record中都包含下面一部分关键信息:

    • LSN
    • Log Record的Context
    • Log Record所属的事务ID(所有的用户事务都会存在事务ID)
    • Log Record所占的字节
    • 同一个事务中上一条Log Record的LSN(用于Undo)
    • 为Undo所保留的日志空间

        当然,这些仅仅是日志的一小部分内容。通过Log Record所记录的内容,就能够精确的记录对数据库所做的修改。

     

     

      在sql server 里有数据文件.mdf和日志文件.ldf,日志文件是sqlserver数据库的另一个重要组成部分,日志文件记录了所有事务以及每个事务对数据库所做的修改。为了提高数据库的性能, sqlserver 数据是缓存在内存里,并没有实时写入到磁盘,直到数据库出现检查点(checkpoint)或者内存不足必须(Lazy Write)将数据的修改写入到磁盘。 sql server在开启了事务并对内存中的数据进行修改时,会生成日志记录。 sqlserver 对数据页的插入修改删除都是在内存中完成后提交事务,但并不会同步到硬盘的数据页上。 为了保证数据库事务的一致性 如(服务器崩溃,断电)等 内存中的修改没有来得及写入硬盘,下次重启时候要能够恢复到一个事务一致的时间点,就必须依赖于事务日志。

     在上一章备份与恢复里了解到事务日志的重要性,这篇重点来了解事务日志。 事务日志记录了数据库所有的改变,能恢复该数据库到改变之前的任意状态。在sql server实例每次启动时都会去检查数据文件与日志文件的一致性。 包括日志记录的任何已提交的数据必须体现在数据文件上,未被标记为已提交的将禁止写入数据文件,日志还存储了收到客户端回滚事务请求,sqlserver出错如死锁等,日志产生一个rollback命令。

    1.日志记录的是数据的变化而不是引发数据的操作
    2.每条记录都有唯一的编号:LSN,并且记录了它属于的事务号。
    3.日志记录的行数和实际修改的数据量有关
    4.日志记录了事务发生的时间,但不记录发起者的程序名称和客户端信息
    5.日志记录数据修改前和修改后的数据

     

    本文是对SQL Server事务日志的总结,文章有一些内容和知识来源于官方文档或一些技术博客,本文对引用部分的出处都有标注。

         1.1 存储结构

       事务日志是在数据库创建或改变时与数据库关联起来的一个或多个文件。 任务改变数据库的操作都会在事务日志中写入描述这些改变的记录,包括要改变的页码,增加或删除的数据值,事务信息,起止的日期和时间信息等。通过dbcc log可以看到如下信息

     

    日志用于Undo

        在了解为了Undo,日志所起的作用之前,我们首先可以了解一下为什么需要事务存在回滚:

    • 因为事务可能失败,或者死锁等原因,如果希望事务不违反原子性而造成数据库不一致的话,则需要通过回滚将已经部分执行的事务回滚掉。
    • 根据业务需求,如果在某些关联业务失败等情况下,回滚数据。

     

     

    SELECT * FROM sys.[fn_dblog](NULL,null)
    

     

    新葡亰496net 10

     

     

    sqlserver跟mysql不一样,lsn不是redo log的总量

    新葡亰496net 11

    新葡亰496net 12

     

     

      与数据文件不同 日志文件不是按页/区来进行组织的。每个物理日志文件是分成多个虚拟日志单元,虚拟日志单元没有固定大小,且数量不固定, 管理员也不能配置大小和数量。 例如:日志文件每自动增长一次(默认是按10%的空间扩展),会至少增加一个虚拟单元。

    新葡亰496net 13

    虚拟日志文件的状态:
    1.活动(ACTIVE),在VLF上有任一条LSN是活动的
    2.可恢复(RECOVERABLE),VLF上的LSN不活动的,但尚未被截断(truncated),该片区域的日志将可能被用于备份/镜像/复制等
    3.可重用(REUSED),VLF上无活动的LSN,且已经被截断,该空间可以被再次使用
    4.未使用(UNUSED),VLF是不活动的,且空间从未被使用过

     

      事务日志是一种回绕的文件。例如一个数据库里的日志文件包括5个虚拟日志单元,在创建数据库时,逻辑日志文件从物理文件的始端开始,新的日志记录被添加到逻辑日志未端,然后向物理日志未端扩张。

      sql server里每个日志记录都有一个唯一的日志序列号标识LSN, 同一个事务里的所有日志记录是一个连接起来的整体,这样能够容易的定位一个事务的各个部分,从而实现撤销undo或重做redo操作。

    (PS: DBCC LOGINFO 中Status=0表示可重用或未使用,Status=2表示活动或可恢复)

     

      当逻辑日志的末端到达物理日志的末端时,新的日志记录将回绕到物理日志文件的始端继续向后写(这是因为日志备份会截断使日志空间重用)。

      1.2 优先写日志

     

    事务日志介绍

      下图是日志文件的流程图,当日志备份后虚拟日志1和虚拟日志2会被截断,虚拟日志3成为了逻辑日志的开头,当虚拟日志3和虚拟日志4在使用后,再次备份时,由于日志文件是一个回绕的文件,此时又从虚拟日志1开始。
       图1  日志文件的外观

      在日志里有个名词叫“优先写日志”。是指:缓存管理器能够保证日志写入磁盘优先于相应的数据改变写入磁盘,这叫优先写日志。一旦某个数据页发生改变,相应的日志项的LSN将会被写入该数据页的页头,缓存管理器能够保证日志页以特定的顺序写入磁盘,使得无论故障在何时发生,sqlserver 能清楚知道在系统故障之后应该处理哪些日志块。如下图所示

    数据增长大小与VLF增长数量
    1-64M:4个VLF
    64M-1G:8个VLF
    1G以上:16个VLF

     

      新葡亰496net 14

    新葡亰496net 15

     

     

      图2 事务日志的循环使用

       但一个事务日志记录被写入到磁盘,实际上被更改的数据可能还未来得及写入数据页,对于事务日志写操作是异步的,数据页的写操作也是异步的,但数据页不需要立即完成,因为日志包含了用来重做这些写操作的所有信息。

    截断(Truncated)是将VLF从Recoberable 状态转变成 reused 状态

     

         新葡亰496net 16

      1.3 日志文件与重启恢复
      在sqlserver错误日志 error log 里会报告每个数据库重启恢复的进展,它会告诉我们每一个数据库有多少事务被前滚,多少事务被回滚, 有时被称为“崩溃”恢复,因为sqlserver崩溃或服务异常停止,需要恢复过程在服务重启时运行。 如果sqlserver里 事务日志与数据文件一致,则重启服务很快。

    In sample recovery model,Every checkpiont will check is there any vlf could be truncated, truncated the recoverable lsn and move the min lsn

    在SQL Server中,事务日志是数据库的重要组件,如果系统出现故障,则可能需要使用事务日志将数据库恢复到一致状态。每个SQL Server数据库都拥有自己的事务日志,用于记录所有事务以及每个事务对数据库所做的修改。那么数据库的哪些操作会记录在事务日志中呢?具体一点的说,这些操作包括:

       在一个虚拟日志单元里,分成很多块,块内有具体的日志记录,每条日志记录有一个LSN(Log Sequence Number)编号,这个编号由三部分组成。第一部分是虚拟日志单元(Virtual Log File)序列号,第二部分是在虚拟日志单元中块的编号,第三部分是在块中日志记录的编号。对于某个LSN,其编号为000001D:000000FD:0002。 这表明这个LSN是属于虚拟日志000001D,该虚拟日志中属于块000000FD,在该块中对应记录2。

        新葡亰496net 17

    在简单恢复模式下,日志仅用于事务回滚和数据库崩溃时的恢复。

     

      1.2 DBCC LOG
      使用DBCC LOG来查看日志文件里存放了些什么信息, dbcc log(dbname, formart_id),formart_id 使用"3" 参数输出会比较详细。

      1.4 日志文件redo与undo

    在完整恢复模式下,只有经过日志备份过的日志才可以被截断

     

    Create database TestLog
    go
    use TestLog
    go
    Create Table Test(ID int,name nvarchar(50))
    GO
    Insert into Test Values(1,'aaaa')
    update Test set name='bbbb' where ID=1
    Go
    dbcc traceon (3604)
    go
    dbcc log (TestLog,3)
    

      如果事务在提交时,sql server服务突然停止,数据还未来得及写入数据页(注意不是磁盘),当服务启动,该事务必须前滚,根据事务日志所指示的更改来重做事务,这称为恢复的重做(redo)阶段。

    从完整恢复模式切换到大容量日志恢复模式并不会破坏日志链条,因此可以在可能产生大量日志的操作(SELECT INTO/INSERT INTO SELECT /REBUILD INDEX/CREATE INDEX)等之前将恢复模式转换成大容量日志模式,操作结束后在换回完整模式,这样不会破坏现在的备份策略同时有效避免此操作生成大量日志和日志文件急速增长

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

      由于dbcc log是未公开的命令,所以未找到相关说明, 如下图所示 包括了当前序号号,操作类型,事务号等相关信息。

      如果一个检查点checkpoint 在事务提交前发生, 它将会把未提交的更改写入磁盘,随后sql server服务在提交前被停止, 恢复过程将会找出未提交事务对数据的改动,该过程必须撤销反映在事务日志中的改动,回滚所有不完整事务称为恢复的撤销(undo)阶段。

     

     

    新葡亰496net 18

      1.5 改变日志文件大小

    引发Log 读的操作

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

     二. ApexSQL Log工具

      由于dbcc log数据不太直观,现通过第三方工具ApexSQL Log来查看,该工具可以看到对上面表的创建,插入,更新,删除的操作记录,在数据库日志文件里还标注了起始时间表,以及操作由哪个用户执行的,对于每一个操作,可以看到更具体的更新信息。

        这是刚刚操作的二条记录如下图所示

        新葡亰496net 19

      选中insert 该行可以找到该语句做undo (撤消回滚 旧值覆盖)和redo(提交 新值覆盖)

      新葡亰496net 20

    -- Undo   INSERT (0000001E:00000047:0013) done at 2018-07-29 09:49:55.570 by hsr-PChsr in transaction 0000:00000301 (Committed)
    BEGIN TRANSACTION
    DELETE FROM [dbo].[Test] WHERE /*** WARNING: WHERE CLAUSE FOR THIS STATEMENT WAS GENERATED FOR A TABLE WITH NO PRIMARY KEY AND NO CLUSTERED INDEX ***/[ID] = 1 AND [name] = N'aaaa' COLLATE Chinese_PRC_CI_AS
    IF @@ROWCOUNT <= 1 COMMIT TRANSACTION ELSE BEGIN ROLLBACK TRANSACTION; PRINT 'ERROR: STATEMENT AFFECTED MORE THAN ONE ROW. ALL THE CHANGES WERE ROLLED BACK.' END
    
    --Redo    INSERT (0000001E:00000047:0013) done at 2018-07-29 09:49:55.570 by hsr-PChsr in transaction 0000:00000301 (Committed)
    INSERT INTO [dbo].[Test] ([ID], [name]) VALUES (1, N'aaaa' COLLATE Chinese_PRC_CI_AS)
    
    -- 下面ID=1的语句做四做操作
    update Test set name='cccc' where ID=1
    update Test set name='dddd' where ID=1
    update Test set name='eeee' where ID=1
    delete from  Test  where ID=1
    

     下列记录了相应的操作,trial restricted 可能是因为该软件需要付费。

    新葡亰496net 21

      总结: 使用truncate table 来删除操作是不会记录日志的,且无法做undo操作。日志记录与实际修改的数据量有关,每一条记录的修改都会保存日志记录。sql server日志里面能读到数据修改前的值和修改后的值。

     

    参考文献:

      sq lserver2012实施与管理实战指南

        数据库管理员为了控制文件在大小,可能有时候要收缩文件空间可以使用dbcc shrinkdatabase或  dbcc  shrinkfile。shrinkdatabase 是收缩指定数据库中的所有数据文件和日志文件大小。shrinkfile 是收缩当前数据库的指定数据文件或日志文件的大小。注意的是不能在备份数据库时收缩数据库。 反之,也不能在数据库执行收缩操作时备份数据库。收缩一般在数据库维护时段可以进行。使用dbcc  shrinkfile来一个文件一个文件地做比较稳妥。

    1. Transcation rollback
    2. crash recovery
    3. create a database snapshot
    4. running dbcc checkdb
    5. transaction log backup
    6. database full backup or differential backup
    7. transcation replication
    8. change data capture
    9. database mirroring
    10. a checkpoint in the simple recovery mode
    11. processing a DML trigger(on sql server 2000)
    12. manually looking in the log(dbcc log or fn_log)

     

    -- 验证文件是否有足够的可用空间可供删除
    SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
    FROM sys.database_files;
    

    由于单个事务会产生多天事务日志记录,如果每条事务日志记录都写一次磁盘,会造成严重的瓶颈,并且严重延迟事务执行时间,因此SQL SERVER 将事务日志先存放在Log Buffer中,在满足以下条件时将日志记录写入磁盘:
    1>事务提交或回滚
    2>有超过60KB的日志没有刷新写入磁盘

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

      1.6 虚拟日志文件VLF

    在log flush时,会将log buffer中所有日志记录都写入磁盘,无论该日志所属的事务是否提交。

     

      在前面“sql server 日志文件结构及误操作数据找回”中讲过每个物理日志文件是分成多个虚拟日志单元,虚拟日志单元没有固定大小,且数量不固定。可以通过dbcc loginfo来观察虚拟日志文件的关键属性。当我们在当前数据库下运行dbcc loginfo,会为每个VLF返回一行记录。

    由于每个事务提交或回滚都会造成一次log flush,每次事务提交需等待日志被写入磁盘才算成功,因此日志写入磁盘延迟直接影响事务的执行时间。

    ·         创建或删除表或索引。

    use test
    dbcc loginfo
    

    SQL SERVER限制log flush的并发数最大为32,因此,在同一时间点,只能有32个事务被提交

     

      新葡亰496net 22

    解决日志写等待的问题
    1>减少日志的写入量
    2>提高事务日志的写入速度

     

      上面是查看了test库日志文件里的VLF,  Fileld是指物理日志文件ID,这里test只有一个日志文件。 FileSize是文件大小(byte), StartOffset是指起点偏移(byte)。第一个VLF 是包含页头信息而不是日志记录,VLF从第二页开始。Status 表示该VLF是否可被重用,状态2表示该VLF或者是活动的或者是可恢复的,状态0表示该VLF是可复用的或者完全没有被使用过。通过备份事务日志会改变可恢复的VLF到可复用状态也就是状态为0.

    提高事务日志的写入速度
    1>如果日志所在磁盘较慢,可以将日志移动到较快的磁盘上
    2>如果日志所在磁盘已经足够快的情况下,有大量并发的小事务操作,可拆分为多个数据库来解决

    另外,像SELECT这样的操作是不会记录在事务日志当中的。如果你想对事务日志记录信息有一个直观的认识,那么你可以在测试环境做一些SELECT、INSERT、UPDATE、DDL等操作,然后使用ApexSQL Log这款工具查看具体的事务日志记录信息。

     

     

     

    USE YourSQLDba;

    GO

    CREATE TABLE dbo.TEST(ID  INT);

    GO

    INSERT INTO dbo.TEST SELECT 100;

    GO

    SELECT * FROM dbo.TEST;

    GO

    UPDATE dbo.TEST SET ID=101;

    GO

    DELETE FROM dbo.TEST WHERE ID=101;

    GO

     

     

    新葡亰496net 23

     

    如上所示,像DDL、DML操作都会记录在事务日志当中,但是SELECT是不会记录在事务日志当中(当然SELECT INTO除外,其实SELECT INTO在事务日志里面转化为了CREATE TABLE形式)。另外,需要注意: 事务日志并不是审计跟踪。也就是说事务日志并不能完全替代审计跟踪。它不提供对数据库做出改变的审计跟踪;它不保持对数据库已执行命令的记录,只有数据如何改变的结果。其实很多对事务日志了解不深入的人都以为事务日志可以代替审计跟踪(曾经有项目经理想让我从事务日志当中挖掘出谁误删了数据,其实事务日志只会记录那个账号删除了记录,并不会记录客户端信息,所以不能定位到谁删除了数据)。如下所示,我们多了一个DROP TABLE操作。你会看到跟上面不一样的结果。

     

    USE YourSQLDba;

    GO

    CREATE TABLE dbo.TEST(ID  INT);

    GO

    INSERT INTO dbo.TEST SELECT 100;

    GO

    SELECT * FROM dbo.TEST;

    GO

    UPDATE dbo.TEST SET ID=101;

    GO

    DELETE FROM dbo.TEST WHERE ID=101;

    GO

    DROP TABLE dbo.Test;

    GO

     

     

    新葡亰496net 24

     

     

    这篇博客transactionlog中有一张图,描述了一个更新操作的流程中,事务日志在这个流程中的位置以及作用。想必看过这张图后,大家在大脑中会对事务日志的功能作用有一个初步的形象认识。

     

     

    新葡亰496net 25

     

     

     

    其实这张图还包含了很多隐藏的重要信息,下面我们一一来述说一下:

     

     

    预写式日志(Write-Ahead Logging)

     

    什么是预写式日志呢? 其实其核心思想就是在变化的数据写入到数据库之前,将相关日志记录信息先写入到日志. SQL Server的预写式日志(Write-Ahead Logging)机制保证修改的描述(例如日志记录)会在数据本身修改写入数据文件前写入,会写入磁盘上的事务日志文件。它是SQL Server保证事务持久性(Durability)的基本机制。一个日志记录会包含已提交事务或未提交事务的详细信息,在数据被事务修改的不同情况下,可能已经写入数据文件或还没来得及写入数据文件,这取决于检查点是否已发生。

     

    浅谈SQL Server中的事务日志(二)----事务日志在修改数据时的角色 这篇博客有深入浅出的介绍(如下所示):

     

    Write-Ahead Logging的核心思想是:在数据写入到数据库之前,先写入到日志.

     

    因为对于数据的每笔修改都记录在日志中,所以将对于数据的修改实时写入到磁盘并没有太大意义,即使当SQL Server发生意外崩溃时,在恢复(recovery)过程中那些不该写入已经写入到磁盘的数据会被回滚(RollBack),而那些应该写入磁盘却没有写入的数据会被重做(Redo)。从而保证了持久性(Durability)。

     

         但WAL不仅仅是保证了原子性和持久性。还会提高性能.

         硬盘是通过旋转来读取数据,通过WAL技术,每次提交的修改数据的事务并不会马上反映到数据库中,而是先记录到日志.在随后的CheckPoint和Lazy Writer中一并提交,如果没有WAL技术则需要每次提交数据时写入数据库......

     

     

    官方文档SQL Server 事务日志体系结构和管理指南介绍如下(个人对翻译做了一下调整,也增加了一点点内容):

     

    新葡亰496net:日志文件结构及误操作数据找回,事务日志。 

    要了解预写日志的工作方式,了解如何将修改的数据写入磁盘很重要。SQL Server维护一个缓冲区缓存(buffer cache),在必须检索数据时从其中读取数据页。 在缓冲区缓存中修改页后,不会将其立即写回磁盘;而是将其标记为“脏”数据。在将数据页物理写入磁盘之前,这些脏数据可以多次被修改。 对于每次逻辑写入,都会在日志缓存(log cache)中插入一条事务日志记录记录这些修改。在将关联的脏页从缓冲区缓存中删除并写入磁盘之前,必须将这条些日志记录写入磁盘。检查点进程定期在缓冲区高速缓存中扫描包含来自指定数据库的页的缓冲区,然后将所有脏页写入磁盘。 CHECKPOINT 可创建一个检查点,在该点保证全部脏页都已写入磁盘,从而在以后的恢复过程中节省时间。

     

    将修改后的数据页从高速缓冲存储器写入磁盘的操作称为刷新页。 SQL Server具有一个逻辑,它可以在写入关联的日志记录前防止刷新脏页。 日志记录将在提交事务时写入磁盘。

     

     

     

    检查点作用

     

     

    检查点将脏数据页从当前数据库的缓冲区高速缓存刷新到磁盘上。这最大限度地减少了数据库完整恢复时必须处理的活动日志,减少的崩溃恢复需要的时间。其实CheckPoint是为了优化IO和减少Recovery时间 在完整恢复时,需执行下列操作:

     

    §  前滚系统停止之前尚未刷新到磁盘上的日志记录修改信息。

    §  回滚与未完成的事务(如没有 COMMIT 或 ROLLBACK 日志记录的事务)相关联的所有修改。

     

     

     

     

    检查点操作

     

     

     

    检查点在数据库中执行下列过程:

     

    ·         将记录写入日志文件,标记检查点的开始。

     

    ·         将为检查点记录的信息存储在检查点日志记录链内。

     

    ·         记录在检查点中的一条信息是第一条日志记录的日志序列号 (LSN),它必须存在才能成功进行数据库范围内的回滚。 该 LSN 称为“最小恢复 LSN”(“MinLSN”)。 MinLSN 是下列各项中的最小者:

     

    o   检查点开始的 LSN。

    o   最早的活动事务起点的 LSN。

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

    o   检查点记录还包含所有已修改数据库的活动事务的列表。

     

    ·         如果数据库使用简单恢复模式,检查点则标记在 MinLSN 前重用的空间。

    ·         将所有脏日志和脏数据页写入磁盘。

    ·         将标记检查点结束的记录写入日志文件。

    ·         将这条链起点的 LSN 写入数据库引导页。

     

     

    导致检查点的活动

     

     

    下列情形下将出现检查点:

     

    ·         显式执行 CHECKPOINT 语句。 用于连接的当前数据库中出现检查点。

    ·         在数据库中执行了最小日志记录操作,例如,在使用大容量日志恢复模式的数据库中执行大容量复制操作。

    ·         已经使用 ALTER DATABASE 添加或删除了数据库文件。

    ·         通过 SHUTDOWN 语句或通过停止 SQL Server (MSSQLSERVER) 服务停止了 SQL Server 实例。 任一操作都会在 SQL Server 实例的每个数据库中生成一个检查点。

    ·         SQL Server 实例在每个数据库内定期生成自动检查点,以减少实例恢复数据库所需的时间。

    ·         进行了数据库备份。

    ·         执行了需要关闭数据库的活动。 例如,AUTO_CLOSE 设置为 ON ,并且关闭了数据库的最后一个用户连接,或者执行了需要重新启动数据库的数据库选项更改。

     

     

    事务日志物理结构

     

     

    SQL Server数据库中的事务日志可以有一个或多个事务日志文件。当存在多个事务日志文件时,这些日志文件也只能顺序调用,并不能并行使用,因此使用多个日志文件并不会带来性能上的提升(后面内容会展开讨论这个)。其实,如果你对ORACLE当中联机重做日志体系结构非常熟悉的话,多个事务日志文件就相当于多个redo log file,不同的是,ORACLE下面的redo log可以实现多路复用(日志组可以有一个或多个同样的日志成员redo log file,多个日志成员的原因是防止日志文件组内某个日志文件损坏后及时提供备份,所以同一组的日志成员一般内容信息相同,但是存放位置不同)。一般会将同一组的不同日志成员文件放到不同的磁盘或不同的裸设备上。以提高安全性。SQL Server似乎没有这个架构设计。另外,ORACLE的REDO 与UNDO在结构设计上是分开的。而SQL Server可以通过事务日志进行REDO和UNDO操作。

     

     

    新葡亰496net 26

     

     

    事务日志逻辑结构

     

     

    从逻辑结构上看,SQL Server对于日志文件的管理,是将逻辑上一个ldf文件划分成多个逻辑上的虚拟日志文件(virtual log files,简称VLFs).以便于管理。SQL Server事务日志按逻辑运行,就好像事务日志是一串日志记录一样。每条日志记录由一个日志序列号 (LSN) 标识。 每条新日志记录均写入日志的逻辑结尾处,并使用一个比前面记录的 LSN 更高的 LSN。 日志记录按创建时的串行序列存储。 每条日志记录都包含其所属事务的 ID。对于每个事务,与事务相关联的所有日志记录通过使用可提高事务回滚速度的向后指针挨个链接在一个链中。 虚拟日志文件没有固定大小,且物理日志文件所包含的虚拟日志文件数不固定。 数据库引擎在创建或扩展日志文件时动态选择虚拟日志文件的大小。 数据库引擎尝试维护少量的虚拟文件。 在扩展日志文件后,虚拟文件的大小是现有日志大小和新文件增量大小之和。 管理员不能配置或设置虚拟日志文件的大小或数量。但是如果设置日志文件的增量过小,则会产生过多的VLFS,也就是日志文件碎片,过多的日志文件碎片会拖累SQL Server性能.因此,指定合适的日志文件初始大小和增长,是减少日志碎片最关键的部分.

     

     

    事务日志是一种回绕的文件。 例如,假设有一个数据库,它包含一个分成四个虚拟日志文件的物理日志文件。 当创建数据库时,逻辑日志文件从物理日志文件的始端开始。 新日志记录被添加到逻辑日志的末端,然后向物理日志的末端扩张。 日志截断将释放记录全部在最小恢复日志序列号 (MinLSN) 之前出现的所有虚拟日志。 MinLSN 是成功进行数据库范围内回滚所需的最早日志记录的日志序列号。 示例数据库中的事务日志的外观与下图所示相似。

     

    新葡亰496net 27

     

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

     

     

     

    新葡亰496net 28

     

    上面关于事务日志的虚拟日志循环覆盖使用是否有点眼熟的感觉,这个跟ORACLE下REDO LOG的循环覆盖使用的理念是一模一样的。只不过是不同的概念和不同的实现方式。

     

     

     

    事务日志功能

     

    新葡亰496net, 

     

     

    事务日志有啥功能呢?关于事务日志的功能,详细具体内容可以参考官方文档事务日志 (SQL Server),里面已经详细介绍了事务日志的几个功能,在此不做展开。

     

     

    事务日志支持以下操作:

     

    ·         恢复个别的事务。

     

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

     

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

     

    ·         支持事务复制。

     

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

     

     

     

     

    事务日志截断

     

     

    什么是事务日志截断呢? 在介绍事务日志截断前,我们必须先了解一下MinLSN、活动日志(Actvie Log)等概念。

     

     

    最小恢复LSN(Minimum Recovery LSN(MinLSN))概念

     

     

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

     

    ·         CheckPoint的开始LSN

     

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

     

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

     

     

     

    从MinLSN到日志的逻辑结尾处,则称为活动日志(Active Log)。日志文件中从 MinLSN 到最后写入的日志记录这一部分称为日志的活动部分,或者称为活动日志(Active log)。 这是进行数据库完整恢复所需的日志部分。 永远不能截断活动日志的任何部分。所有的日志记录都必须从 MinLSN 之前的日志部分截断。也就是说永远不能截断活动日志的任何部分。

     

     

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

     

    新葡亰496net 29 新葡亰496net 30 新葡亰496net 31

     

    LSN 148 是事务日志中的最后一条记录。 在处理 LSN 147 处记录的检查点时,Tran 1 已经提交,而 Tran 2 是唯一的活动事务。 这就使 Tran 2 的第一条日志记录成为执行最后一个检查点时处于活动状态的事务的最旧日志记录。 这使 LSN 142(Tran 2 的开始事务记录)成为 MinLSN。

     

     

    活动日志必须包括所有未提交事务的每一部分。如果应用程序开始执行一个事务但未提交或回滚,将会阻止数据库引擎推进 MinLSN。 这可能会导致两种问题:

     

        如果系统在事务执行了许多未提交的修改后关闭,以后重新启动时,恢复阶段所用的时间将比“恢复间隔”选项指定的时间长得多。

        因为不能截断 MinLSN 之后的日志部分,日志可能变得很大。 即使数据库使用的是简单恢复模式,这种情况也有可能出现,在简单恢复模式下,每次执行自动检查点操作时通常都会截断事务日志。

     

    日志截断其实指从SQL Server数据库的逻辑事务日志中删除不活动的虚拟日志文件,释放逻辑日志中的空间以便物理事务日志重用这些空间。 如果事务日志从不截断,它最终将填满分配给物理日志文件的所有磁盘空间。 但是,在截断日志前,必须执行检查点操作。检查点将当前内存中已修改的页(称为“脏页”)和事务日志信息从内存写入磁盘。 执行检查点时,事务日志的不活动部分将标记为可重用。 此后,日志截断可以释放不活动的部分。有关检查点的详细信息,请参阅数据库检查点 (SQL Server)。

     

    关于日志截断,必须定期截断事务日志,防止其占满分配给物理日志文件的磁盘空间。日志截断并不减小物理日志文件的大小。 若要减少物理日志文件的物理大小,则必须收缩日志文件。

    新葡亰496net:日志文件结构及误操作数据找回,事务日志。 

    日志截断会在下面事件后自动进行截断:

     

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

     

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

     

       CHECKPOINT only truncates the transaction log (marks the VLF for reuse) only in simple recovery model. In Full recovery, you have to take log backup.

     

     

     

    实际上,日志截断会由于多种原因发生延迟。 查询 sys.databases 目录视图的 log_reuse_wait 和 log_reuse_wait_desc 列,了解哪些因素(如果存在)阻止日志截断。 下表对这些列的值进行了说明:

     

    Log_reuse_wait 值

    Log_reuse_wait_desc 值

    说明

    0

    NOTHING

    当前有一个或多个可重复使用的虚拟日志文件。

    1

    CHECKPOINT

    自上次日志截断之后,尚未生成检查点,或者日志头尚未跨一个虚拟日志文件移动。 (所有恢复模式)

    这是日志截断延迟的常见原因。 有关详细信息,请参阅数据库检查点 (SQL Server)。

    2

    LOG_BACKUP

    在截断事务日志前,需要进行日志备份。 (仅限完整恢复模式或大容量日志恢复模式)

    完成下一个日志备份后,一些日志空间可能变为可重复使用。

    3

    ACTIVE_BACKUP_OR_RESTORE

    数据备份或还原正在进行(所有恢复模式)。

    如果数据备份阻止了日志截断,则取消备份操作可能有助于解决备份直接导致的此问题。

    4

    ACTIVE_TRANSACTION

    事务处于活动状态(所有恢复模式):

    一个长时间运行的事务可能存在于日志备份的开头。 在这种情况下,可能需要进行另一个日志备份才能释放空间。 请注意,长时间运行的事务将阻止所有恢复模式下的日志截断,包括简单恢复模式,在该模式下事务日志一般在每个自动检查点截断。

    延迟事务。 “延迟的事务 ”是有效的活动事务,因为某些资源不可用,其回滚受阻。 有关导致事务延迟的原因以及如何使它们摆脱延迟状态的信息,请参阅延迟的事务 (SQL Server)。

    长时间运行的事务也可能会填满 tempdb 的事务日志。 Tempdb 由用户事务隐式用于内部对象,例如用于排序的工作表、用于哈希的工作文件、游标工作表,以及行版本控制。 即使用户事务只包括读取数据(SELECT 查询),也可能会以用户事务的名义创建和使用内部对象, 然后就会填充 tempdb 事务日志。

    5

    DATABASE_MIRRORING

    数据库镜像暂停,或者在高性能模式下,镜像数据库明显滞后于主体数据库。 (仅限完整恢复模式)

    有关详细信息,请参阅数据库镜像 (SQL Server)。

    6

    REPLICATION

    在事务复制过程中,与发布相关的事务仍未传递到分发数据库。 (仅限完整恢复模式)

    有关事务复制的信息,请参阅 SQL Server Replication。

    7

    DATABASE_SNAPSHOT_CREATION

    正在创建数据库快照。 (所有恢复模式)

    这是日志截断延迟的常见原因,通常也是主要原因。

    8

    LOG_SCAN

    发生日志扫描。 (所有恢复模式)

    这是日志截断延迟的常见原因,通常也是主要原因。

    9

    AVAILABILITY_REPLICA

    可用性组的辅助副本正将此数据库的事务日志记录应用到相应的辅助数据库。 (完整恢复模式)

    有关详细信息,请参阅:AlwaysOn 可用性组概述 (SQL Server)。

    10

    仅供内部使用

    11

    仅供内部使用

    12

    仅供内部使用

    13

    OLDEST_PAGE

    如果将数据库配置为使用间接检查点,数据库中最早的页可能比检查点 LSN 早。 在这种情况下,最早的页可以延迟日志截断。 (所有恢复模式)

    有关间接检查点的信息,请参阅数据库检查点 (SQL Server)。

    14

    OTHER_TRANSIENT

    当前未使用此值。

     

     

     

    事务日志收缩

     

     

     

    有时候我们监控告警会发现事务日志出现暴增的情况,那么此时就必须对是事务日志进行收缩,不管数据库处于那种恢复模式,简单、完整模式。都可以按下面流程进行收缩。

     

     

     

     

    1:查看对应数据库事务日志的逻辑名称(name),后续操作需要用到。

     

     

    SELECT  database_id ,
    
            name ,
    
            type_desc ,
    
            physical_name
    
    FROM    sys.master_files
    
    WHERE   database_id = DB_ID('YourSQLDba')
    
        AND type_desc='LOG'
    

     

     

    2: 使用DBCC SQLPERF查看事务日志空间使用情况统计信息:

     

     

       

          DBCC SQLPERF (LOGSPACE)

     

       

         如果对应数据库的Log Space Used(%)的值较小,那么就可以收缩事务日志。

     

     

      3:执行类似下面的收缩事务日志文件语句。

     

     

    USE YourSQLDba;

    GO

    DBCC SHRINKFILE('YourSQLDba_Log', 128);

     

     

     

      如果Log Space Used(%)很小,而收缩效果又不佳,那么一般是因为日志截断延迟造成,一般可以通过下面脚本检查原因,大部分情况是因为等待LOG_BACKUP缘故。所以你对事务日志做一次备份后,再进行收缩即可解决。

     

    SELECT  name ,
    
            log_reuse_wait  ,
    
            log_reuse_wait_desc
    
    FROM    sys.databases
    
    WHERE   database_id = DB_ID('YourSQLDba');
    
     
    
     
    
    backup log [YourSQLDba] 
    
    to disk = 'M:DB_BACKUPLOG_BACKUPYourSQLDba_[2018-01-11_06h37m26_Thu]_logs.TRN' 
    
    with noInit, checksum, name = 'YourSQLDba:15h40: M:DB_BACKUPLOG_BACKUPYourSQLDba_[2018-01-11_06h37m26_Thu]_logs.TRN'
    

     

     

    增加事务日志文件

     

     

     

    SQL Server数据库中的事务日志可以有一个或多个事务日志文件,但是即使有多个事务日志文件,也不能并行写入多个事务日志文件,数据库引擎还是会串行使用多个事务日志文件。也就是说大多数场景,多个事务日志文件其实并没有什意义,那么它存在的意义是什么呢?例如,当你当前磁盘告警,事务日志无法继续增长,你需要在其他磁盘新增一个事务日志文件,让数据库继续顺畅运行。个人觉得多个事务日志文件确实是一个很鸡肋的东西。Paul S. Randal在“了解SQL Server的日志记录和恢复”中明确指出:不要创建多个的日志文件,因为它不会导致性能增益。

     

    下面是如何增加一个事务日志文件的样例:

     

     

    USE [master]
    
    GO
    
    ALTER DATABASE [YourSQLDba] ADD LOG FILE ( NAME = N'YourSQLDba_Log2', FILENAME = N'D:SQL_LOGYourSQLDba_Log1.LDF' , SIZE = 65536KB , MAXSIZE = 55296KB , FILEGROWTH = 10%)
    
    GO
    

     

     

     

     

     

    删除事务日志文件

     

     

    既然可以增加事务日志文件,那么当然也可以删除事务日志文件,但是这个删除操作是有限制的。主日志文件(primary log)是不能删除的。如果你删除primary log就会报“不能从数据库中删除主数据文件或主日志文件。”,下面我们来测试一下。

     

     

    准备测试环境如下:

     

     

    USE master;
    
    GO
    
    CREATE DATABASE [TEST]
    
     CONTAINMENT = NONE
    
     ON  PRIMARY 
    
    ( NAME = N'TEST', FILENAME = N'D:SQL_DATATEST.mdf' , SIZE = 100MB , MAXSIZE = 40GB, FILEGROWTH = 64MB )
    
     LOG ON 
    
    ( NAME = N'TEST_log' , FILENAME = N'D:SQL_LOGTEST_LOG_1.ldf' , SIZE = 20MB , MAXSIZE = 40MB , FILEGROWTH = 10MB),
    
    ( NAME = N'TEST_log2', FILENAME = N'D:SQL_LOGTEST_LOG_2.ldf' , SIZE = 20MB , MAXSIZE = 20GB , FILEGROWTH = 10MB)
    
    GO
    
     
    
    BACKUP DATABASE [TEST] TO  DISK = N'D:DB_BACKUPTest.bak' 
    
            WITH NOFORMAT, NOINIT,  
    
            NAME = N'TEST-Full Database Backup',
    
            SKIP, NOREWIND, NOUNLOAD,  STATS = 10;
    
    GO
    
     
    
     
    
    USE TEST;
    
    GO
    
    SELECT * INTO mytest FROM sys.objects;
    
    GO
    
    INSERT INTO mytest
    
    SELECT * FROM mytest
    
    GO 12
    
     
    
    DBCC SQLPERF(LOGSPACE)
    
     
    
    DBCC LOGINFO('TEST')
    

     

     

    新葡亰496net 32

     

     

    注意,此时DBCC LOGINFO显示FileId=3的日志文件对应的虚拟日志(VLF)的Status为2,此时删除事务日志文件会提示文件无法删除,因为Status=2意味着VLF不能被覆盖和重用。

     

    Status = 2 means that VLF can't be reused (overwritten) at this time and it doesn't necessarily mean that VLF is still active and writing transactions to that VLF. As Jonathan already mentioned, it means that the VLF is waiting for backup/REPL/Mirroring etc..

     

     

    USE master;

    GO

    ALTER DATABASE TEST REMOVE FILE TEST_log2

     

     

     

     

    新葡亰496net 33

     

     

    备份事务日志后,你会发现FileId=3的日志文件对应的虚拟日志(VLF)的Status变为了0,那么此时就可以移除事务日志文件了。

     

     

     

     

    BACKUP LOG TEST TO DISK = 'D:SQL_LOGTest.Trn'

    GO

     

    DBCC LOGINFO('TEST')

    GO

     

    USE master;

    GO

    ALTER DATABASE TEST REMOVE FILE TEST_log2

     

     

    新葡亰496net 34

     

     

     

    如果是生产环境或者在上述备份事务日志后,对应日志文件的VLF的状态仍然为2,那么可以用收缩日志文件和备份事务日志循环处理,直至对应日志文件下所有的VLF状态全部为0,就可以删除事务日志文件。

     

     

    USE TEST;

    GO 

    DBCC SHRINKFILE(TEST_log2);

     

     

    BACKUP LOG TEST TO DISK = 'D:SQL_LOGTest.Trn'

     

     

     

     

    注意,主日志文件(primary log)是不能删除的,如下测试所示:

     

     

    USE master;

    GO

    ALTER DATABASE TEST REMOVE FILE TEST_log

     

     

     

    Msg 5020, Level 16, State 1, Line 35

    The primary data or log file cannot be removed from a database.

     

     

     

     

    但是当你需要规划存储路径、移动事务日志文件时,你可以使用折中的方法将主事务日志文件(primary log)移动到其它目录。如下所示:

     

     

    1: 将当前数据库脱机;

     

     

    ALTER DATABASE TEST SET OFFLINE;

     

     

     

    2: 修改数据库的事务日志位置

     

     

    ALTER DATABASE TEST

    MODIFY FILE

    (

    NAME = N'TEST_log'

    , FILENAME = N'E:SQL_LOGTEST_LOG_1.ldf'

    )

     

     

     

    3: 手工将事务日志文件移动到上面位置

     

     

     

    4:将数据库联机操作。

     

     

    ALTER DATABASE TEST SET ONLINE;

     

     

     

     

    另外,如何判断那个日志文件是主事务日志文件?目前来说,我只能这样判断, sys.master_files当中,file_id最小值对应的日志文件为主事务日志文件。用脚本判断如下:

     

     

     

    SELECT  f.database_id            AS database_id  ,
    
            DB_NAME(f.database_id)   AS database_name,
    
            MIN(f.file_id)           AS primary_log_id ,
    
            f.type_desc              AS type_desc    
    
    FROM    sys.master_files  f
    
    WHERE  f.database_id= DB_ID('databasename') AND  type = 1
    
    GROUP BY f.database_id,f.type_desc;
    

     

     

     

    另外,你也可以用下面脚本查出哪些数据库拥有两个或以上事务日志。

     

     

    SELECT  f.database_id    AS database_id  ,
    
            d.name           AS database_name,
    
            f.type_desc      AS type_desc    ,
    
            COUNT(*)         AS log_count
    
    FROM    sys.master_files  f
    
    INNER  JOIN sys.databases d ON f.database_id = d.database_id
    
    WHERE   type = 1
    
    GROUP BY f.database_id ,
    
             f.type_desc,
    
             d.name
    
    HAVING  COUNT(*) >= 2;
    

     

     

     

     

     

     

    参考资料:

     

     

     

     

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net:日志文件结构及误操作数据找回,

    关键词: