太阳集团游戏官方网站 10

事务日志,备份与恢复系列二

一. 概述

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

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

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

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

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

     1.1 存储结构

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

 

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

太阳集团游戏官方网站 1

虚拟日志文件的状态:
太阳集团游戏官方网站,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

  太阳集团游戏官方网站 2

太阳集团游戏官方网站 3

 

  图2 事务日志的循环使用

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

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

   
 太阳集团游戏官方网站 4

  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

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

    太阳集团游戏官方网站 5

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

  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)阶段。

 

太阳集团游戏官方网站 6

  1.5 改变日志文件大小

引发Log 读的操作

 二. ApexSQL Log工具

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

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

  
 太阳集团游戏官方网站 7

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

  太阳集团游戏官方网站 8

-- 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 可能是因为该软件需要付费。

太阳集团游戏官方网站 9

  总结: 使用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个事务被提交

  太阳集团游戏官方网站 10

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

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

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

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注