SQL Server事务日志终极攻略

1. 事务日志的简介

1.1什么是事务日志

SQL Server数据库的日志文件也称为事务日志文件。每个 SQL Server 数据库都有至少有一个日志文件，用于记录所有事务以及每个事务对数据库所做的修改。事务日志是每个数据库的重要组件，了解和管理此日志是数据库管理员角色的重要组成部分。

1.2事务日志的物理体系结构

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

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

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

如果要判断日志文件中有多少个虚拟日志文件，并且知道哪些虚拟日志文件是活动的，可以使用命令：

DBCC LOGINFO (<dbname>)

其中各个字段的描述见下表

字段名称	描述
StartOffset	物理文件开始到这个头文件的位移。
FSeqNo	VLF的序列号，如果是从未活动的，则为0。
Status	活动的是2，否则为0。
Parity	在64和128之间切换,以防止这段VLF被重用。
CreateLSN	此时需要创建的LSN。

下面通过一个实例来介绍一下这个命令的简单使用方法：

1. 首先创建一个测试数据库：

CREATE DATABASE [test] ON PRIMARY

( NAME = N'test', FILENAME = N'E:\SqlData\test.mdf' , SIZE = 2048KB , FILEGROWTH = 10%)

LOG ON

( NAME = N'test_log', FILENAME = N'E:\SqlData\test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

DBCC loginfo

结果如下：

从图中可以知道，活动的虚拟日志文件的状态(status)为2，test数据库有4个虚拟日志文件，当前仅有一个虚拟日志文件是活动的。

2. 现在创建一个表，然后填充一些行，以产生一些日志再查看日志的变化情况。

create table log_info(id int identity(1,1),info varchar(50))

insert into log_info(info) values ('a')

go 1000

DBCC loginfo

结果如下：

从上图中可以看出活动的虚拟日志变成了三个，可以知道日志是按顺序存储的。并且虚拟日志的序列号是连续的。

3. 再执行checkpoint命令之后再插入一些行，会发生什么变化

checkpoint

DBCC loginfo

结果如下:

insert into log_info(info) values ('a')

go 1000

DBCC loginfo

结果如下:

从操作3可以看出日志文件是一种回绕的文件。虚拟日志的序列号还是连续的，并且Parity的值变成了128.

1.3事务日志的逻辑体系结构

SQL Server 事务日志按逻辑运行，就好像事务日志是一串日志记录一样。每条日志记录由一个日志序列号 (LSN) 标识。每条新日志记录均写入日志的逻辑结尾处，并使用一个比前面记录的 LSN 更高的 LSN。

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

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

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

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

Ø 创建或删除表或索引。

1.4查看日志文件的使用情况

查看日志文件的使用情况可以使用以下代码

DBCC SQLPERF(LOGSPACE)

结果如下:

关于事务日志的详细介绍可查看下面的连接：

http://msdn.microsoft.com/zh-cn/library/ms190925.aspx

2. 查看事务日志

当知道什么是事务日志之后，那就要去看看事务日志中到底存放了那些信息。通过查看事务日志，我们可以知道在某时间段中所有事务或者每个事务对数据库所作的修改。

2.1查看活动的事务日志

Sql Server有一条“DBCC LOG”和一个系统函数“fn_dblog”命令可以查看当前活动的事务日志中的信息。它们的语法为：

1. DBCC LOG(<db_id>/<db_name> ,<formart_id>)

<db_id>/<db_name>：数据库编号或者数据库名

<formart_id>：DBCC LOG 命令翻译和解释日志记录的方式（可写1，2，3，4）

0 - 最少信息(operation, context, transaction id)

1 - 更多信息(plus flags, tags, row length)

2 - 非常详细的信息(plus object name, index name,page id, slot id)
3 - 每种操作的全部信息

4 - 每种操作的全部信息加上该事务的16进制信息

2. select * from fn_dblog(@StartingLSN, @EndingLSN)

其中，该函数的两个参数分别表示起始的LSN号和结束的LSN号。默认情况下，可以使用空值。如果将@StartingLSN设置为空，则表示从首日志记录开始查询，如果@EndingLSN为空值，则表示一直查询到日志的尾记录为止。

其中DBCC LOG(<db_id>/<db_name>,3)输出的结果与select * from fn_dblog(null,null)输出的结果是一样的。但是fn_dblog使用更加灵活，因为可以使用where条件进行筛选。

当我们知道了如何查看当前活动中的事务日志之后，就要去看看事务日志是如何记录每个事务的？下面介绍了数据库insert、update、delete操作在事务日志中记录的情况。

2.1.1 Insert

首先让我们看看insert操作在事务日志中是如何记录的？以下是测试的步骤、脚本和结论

1. 我们在test数据库上先建立一张测试用的表，清空活动中的日志

create table test(ID int,info varchar(250))

create clustered index ix_test_id on test(id)

create nonclustered index ix_test_info on test(info)

checkpoint

select * from fn_dblog (null,null)

结果如下:

由于是新建的数据库，为了测试方便并没有执行过完全备份，所以在执行了checkpoint命令后就截断了日志，可以看出，现在活动中的事务日志只有了两条记录：检查点开始和结束。

2. 接着在表格里插入一条记录

insert into test values(1,'abc')

select [Current LSN],[Operation],[Context],[Server UID],[SPid],

[Begin Time],[end time],[Transaction Name] ,[rowlog contents 0],

[rowlog contents 1],[AllocUnitName],[page id],[slot id]

from fn_dblog (null,null)

结果如下（图1.1，图1.2）:

图1.1

图1.2

可以看到有四条关于这个insert的记录，从记录中可以知道操作的类型，操作的时间等信息。Sql server先通过表中的聚集索引将数据插入到叶子节点的数据页上，在插入非聚集索引叶子节点的索引页

3. 再插入一条数据

insert into test values(2,'def')

select [Current LSN],[Operation],[Context],[Server UID],[SPid],[Begin Time],[end time]

,[Transaction Name] ,[rowlog contents 0],[rowlog contents 1],[AllocUnitName],[Description]

,[page id],[slot id] from fn_dblog (null,null)

结果如下:

可以看到新的insert记录拥有不同的LSN号，这样有助于查看一段时间内到底进行了多少条insert操作。

2.1.2 update

update在事务日志中的记录方式有是什么样的呢？现在让我们来看一下。

以下是测试的步骤、脚本和结论:

1. 由于要看修改数据对索引的影响，在test上id字段已经建立聚集索引，再修改数据

update test set info = 'ghi' where id=1

select [Current LSN],[Operation],[Context],[Server UID],[SPid],

[Begin Time],[end time],[Transaction Name] ,[rowlog contents 0],

[rowlog contents 1],[AllocUnitName],[page id],[slot id]

from fn_dblog (null,null)

结果如下（图2.1，图2.2）:

图2.1

图2.2

我们可以从图中看到会先通过聚集索引找到要修改的数据页，将数据修改，我们可以通过列rowlog contents0和列rowlog contents1知道数据修改前和修改后的值。再将非聚集索引中指向原数据的索引页的键值标记为虚影值(下面的章节会介绍虚影值)。最后插入新的指向修改后数据的索引页键值。

2.1.2_1 虚影值的简介

上面提到的虚影值到底是什么东西呢？这里做一下简单的简介

在表中存在索引时，当我们删除一条数据的时候，SQL Server都会从跟节点向下直到找到叶子节点。当找到叶子节点之后，SQL Server可能不会马上删除这条数据，而是在页中的标志位设置这页已经被删除。这种逻辑上被删除但物理上还存在术语称为:虚影记录（GHOST Record）,在接下来合适的时机，SQL Server才会删除虚影记录。

当表中的数据被标为虚影记录时，接下来的任何查询将无法看到此记录。虚影记录的数量可以从sys.dm_db_index_physical_stats动态管理视图中查看。

虚影记录是由于性能和并发的原因被引入，这不仅提高了DELETE语句的性能，如果DELETE被回滚(Rollback),也同样会提升性能。当回滚数据时，虚影记录仅仅需要将标志位改回来，而不是重新根据日志再创建一条记录。

虚影的具体介绍：

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/

2.1.3 delete

最后看一下delete在日志中是怎么记录的？以下是测试的步骤、脚本和结论

1. 为方便查看结果，先执行checkpoint命令，再删除数据看看活动中的事务日志的情况

checkpoint

delete test where id = 1

select [Current LSN],[Operation],[Context],[Server UID],[SPid],

[Begin Time],[end time],[Transaction Name] ,[rowlog contents 0],

[rowlog contents 1],[AllocUnitName],[page id],[slot id]

from fn_dblog (null,null)

结果如下（图3.1，图3.2）:

图3.1

图3.2

从上图不难看出，sql server会先将非聚集索引的叶子节点标记为虚影记录，再将聚集索引叶子节点的数据页标记为虚影记录并在PFS也中改变一个bit位来标注是否存在虚影值。

2.1.3_1 PFS的简介

PFS表示页可用空间。记录每页的分配状态，是否已分配单个页以及每页的可用空间量。PFS 对每页都有一个字节，记录该页是否已分配。

在数据文件中，PFS 页是文件头页之后的第一页（页码为 1）。可以使用DBCC page的命令来查看，以下是测试的步骤、脚本和结论:

dbcc traceon(3604)

dbcc page('test',1,1,3)

dbcc traceoff(3604)

结果如下：

(1:0) - (1:3) = ALLOCATED 100_PCT_FULL

(1:4) - (1:5) = NOT ALLOCATED 0_PCT_FULL

(1:6) - (1:7) = ALLOCATED 100_PCT_FULL

(1:8) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext

(1:9) - = ALLOCATED 100_PCT_FULL Mixed Ext

(1:10) - = ALLOCATED 0_PCT_FULL Mixed Ext

(1:11) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext

(1:12) - = ALLOCATED 100_PCT_FULL IAM Page Mixed Ext

(1:13) - (1:16) = ALLOCATED 0_PCT_FULL Mixed Ext

(1:17) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext

(1:18) - (1:19) = ALLOCATED 0_PCT_FULL Mixed Ext

(1:20) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext

(1:21) - = ALLOCATED 0_PCT_FULL Has Ghost Mixed Ext

(1:22) - (1:25) = ALLOCATED 0_PCT_FULL Mixed Ext

(1:26) - = ALLOCATED 0_PCT_FULL IAM Page Mixed Ext

(1:27) - = ALLOCATED 0_PCT_FULL Mixed Ext

……………………………………………

PFS 对每页都有一个字节，记录该页是否已分配。统一区还是混合区。是否是IAM页。如果已分配，则记录该页是为空、已满 1% 到 50%、已满 51% 到 80%、已满 81% 到 95% 还是已满 96% 到 100%。

PFS的具体介绍：

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/07/08/under-the-covers-gam-sgam-and-pfs-pages.aspx

2.2查看备份中的事务日志

在上面已经介绍了如何查看活动中的事务日志。但是，之前的测试实例中，我们未对数据库进行过完全备份。当执行过备份后，日志就会发生截断，之前看到的事务日志就会被保存到备份文件之中。这种情况我们应该如何查看日志呢？我们可以用另一个系统函数fn_dump_dblog（函数的具体用法会在后面介绍），现在让我们来进行测试，看看此函数能否满足查看日志的要求

2.2.1测试实例

1. 为了测试结果明显，我们重新建立一个测试的数据库

CREATE DATABASE [logtest] ON PRIMARY

( NAME = N'logtest', FILENAME = N'E:\SqlData\logtest.mdf' , SIZE = 3072KB , FILEGROWTH = 10%)

LOG ON

( NAME = N'logtest_log', FILENAME = N'E:\SqlData\logtest_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

2. 建好数据库后先做一下全备份，这里我用了litespeed工具进行了数据库的全备份，若没有此工具，用普通的脚本备份也是可以的。

EXEC configdb_r.dbo.Spb_Backup_LiteSpeed @dbname='logtest',

@path='e:\backup\logtest201303201101000.BAK', @backupset='FULL'

3. 我们新建表，并往里面插入100条数据

create table logtest(id int , info varchar(50))

insert into logtest values (1,'abc')

go 100

4. 查看当前活动的事务日志找到这100条插入的日志

select [Current LSN],[Operation],[Context],[Server UID],[SPid],

[Begin Time],[end time],[Transaction Name] ,[rowlog contents 0],

[rowlog contents 1],[AllocUnitName],[page id],[slot id]

from fn_dblog (null,null)

where Operation='LOP_INSERT_ROWS' and Context = 'LCX_HEAP'

结果如下:

5. 现在进行日志备份，这里的日志备份不能使用litespeed进行，因为使用此工具得到的备份的文件是会加密的，无法用系统函数读取。

Backup log logtest to disk='E:\backup\logtest20130320110600_log.bak'

6.备份好之后再查看当前活动的事务日志

select [Current LSN],[Operation],[Context],[Server UID],[SPid],

[Begin Time],[end time],[Transaction Name] ,[rowlog contents 0],

[rowlog contents 1],[AllocUnitName],[page id],[slot id]

from fn_dblog (null,null)

结果如下:

因为发生了日志的截断，发现当前活动的日志只有检查点的记录，那如何查看之前的100条插入的记录呢？由于这100条日志记录已经被我们备份过了，就可以用之前提到的系统函数fn_dump_dblog。

SELECT *

FROM fn_dump_dblog (

NULL, NULL, 'DISK', 1, 'e:\backup\logtest20130320110600_log.bak ',

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

where Operation='LOP_INSERT_ROWS' and Context = 'LCX_HEAP'

结果如下:

比对一下查询结果跟日志备份前fn_dblog的查询结果相同。所以就算发生了日志截断，想要查看数据库的操作情况，还是可以通过备份文件来

2.2.2 fn_dump_dblog函数用法的介绍

在上面的测试实例中，可以看到fn_dump_dblog函数还是很好用的，但是可能对函数后面的参数的意义还不是很清楚，下面会简单介绍：

函数的使用的语法：

select *

from fn_dump_dblog(

@start,@end,@devtype,@seqnum,@fname1,

@fname2,@fname3,@fname4,@fname5,@fname6,@fname7,@fname8,

@fname9,@fname10,@fname11,@fname11,@fname12,@fname13,@fname14,

@fname15,@fname16,@fname17,@fname18,@fname19,@fname20,@fname21,

@fname22,@fname23,@fname24,@fname25,@fname26,@fname27,@fname28,

@fname29,@fname30,@fname31,@fname32,@fname33,@fname34,@fname35,

@fname36,@fname37,@fname38,@fname39,@fname40,@fname41,@fname42,

@fname43,@fname44,@fname45,@fname46,@fname47,@fname48,@fname49,

@fname50,@fname51,@fname52,@fname53,@fname54,@fname55,@fname56,

@fname57,@fname58,@fname59,@fname61,@fname62,@fname63,@fname64)

注意：需要指定63个默认NULL参数，否则无法返回数据。前5个参数含义如下：

1. 开始LSN

2. 结束LSN

3. 备份文件类型（disk或者Type）

4. 备份文件顺序号（假设多个备份到同一个文件）

5. 备份文件名

而之后的参数是怎么用的呢？让我用一个实例进行解释一下，以下是测试的步骤、脚本和结论

1. 往表中插入1000条数据

insert into test values (2,'def')

go 1000

2. 备份日志文件，将它备份成两个文件

backup log logtest to disk='e:\backup\logtest20130325091600_log1.bak',

disk='e:\backup\logtest20130325091600_log2.bak'

3. 查看两个备份文件中的日志

SELECT *

FROM fn_dump_dblog (

NULL, NULL, 'DISK', 1, 'e:\backup\logtest20130325091600_log1.bak',

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

where Operation='LOP_INSERT_ROWS' and Context = 'LCX_HEAP'

注：如果只这么写是会报错的

Msg 3132, Level 16, State 1, Line 1

The media set has 2 media families but only 1 are provided. All members must be provided.

正确的写法应该是将两个备份文件都写上

SELECT *

FROM fn_dump_dblog (

NULL, NULL, 'DISK', 1, 'e:\backup\logtest20130325091600_log1.bak',

'e:\backup\logtest20130325091600_log2.bak', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,

DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

where Operation='LOP_INSERT_ROWS' and Context = 'LCX_HEAP'

执行结果：

函数fn_dblog和fn_dump_dblog具体介绍的参考资料：

Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN - Paul S. Randal

3.小结

通过两章的学习，我们对事务日志有了一定的了解，通过查看事务日志可以对数据库进行更好的管理和监控。对数据库知识的深入研究打下了基础。

以下是对本文档的小结：

Ø 本文档主要介绍了SQL SQRVER的事务日志的查看，并从中得到对数据库管理有用的信息。

Ø 事务日志是用于记录所有事务以及每个事务对数据库所做的修改。它是是一种回绕的文件，是按逻辑顺序记录运行的。

Ø 查看虚拟日志的命令：DBCC LOGINFO (<dbname>)；查看日志文件的使用情况的命令：DBCC SQLPEFR(LOGSPACE)

Ø 通过在事务日志中的记录可以知道在某时间段中所有事务或者每个事务对数据库所作的修改。

查看活动中的事务日志的命令：

1. DBCC LOG

2. fn_dblog

Ø 当数据库进行了备份后，会发生事务日志的截断，而此时想看事务日志的内容就可以用命令：fn_dump_dblog

posted @ 2013-05-11 10:03 我是 SQLDBA 阅读(2035) 评论(0) 编辑收藏举报

刷新页面返回顶部

我是 SQLDBA

SQL Server事务日志终极攻略

1. 事务日志的简介

1.1什么是事务日志

1.2事务日志的物理体系结构

1.3事务日志的逻辑体系结构

1.4查看日志文件的使用情况

2. 查看事务日志

2.1查看活动的事务日志

2.1.1 Insert

2.1.2 update

2.1.2_1 虚影值的简介

2.1.3 delete

2.1.3_1 PFS的简介

2.2查看备份中的事务日志

2.2.1测试实例

2.2.2 fn_dump_dblog函数用法的介绍

3.小结

公告