SQL Server事务日志分析-白红宇

SQL Server事务日志分析

fn_dblog()和fn_dump_dblog()函数介绍

SQL Server有两个未公开的函数fn_dblog()和fn_dump_dblog()非常有用并且提供的信息量很大。你可以使用这些函数来获取100多列大量的有用信息。

fn_dblog()用于分析数据库当前的事务日志文件，它需要两个参数，分别为事务开始LSN和结束LSN，默认为NULL，表示返回事务日志文件的所有日志记录。

例如：

                         
SELECT
* 
FROM
fn_dblog(
null
,
null
);
        
      

fn_dump_dblog()用于分析数据库的事务日志备份文件，该函数需要的参数很多，但我们只需要传入备份文件的完整路径名称，其他参数使用默认值DEFAULT。

例如：

                         
SELECT
*
FROM
fn_dump_dblog (
NULL
, 
NULL
, 
'DISK'
, 1, 
'D:\Pay\Pay_201707280400_LOG.trn'
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
);
        
      

再来看看下图多个事务操作写入到事务日志文件的表现：

重要数据输出列值

我们再来分析下100多列输出中的几个重要列：

[Transaction Name]

该列描述该事务操作的类型，主要值有：

INSERT、UPDATE、DELETE、DROPOBJ

次要值有：

AllocPages、SplitPage、AllocHeapPageSysXactDML、UpdateQPStats、Backup:CommitLogArchivePoint、BTree Split/Shrink等。

典型的应用是通过DROPOBJ值来查找对象删除操作。

[Operation]

该列描述日志里记录的操作的具体类型，主要值有：

LOP_BEGIN_XACT、LOP_COMMIT_XACT、LOP_INSERT_ROWS、LOP_DELETE_ROWS、LOP_MODIFY_ROW、LOP_MODIFY_COLUMNS

次要值有：

LOP_BEGIN_CKPT、LOP_END_CKPT、LOP_XACT_CKPT、LOP_LOCK_XACT、

LOP_DELETE_SPLIT、LOP_EXPUNGE_ROWS、LOP_MODIFY_HEADER、LOP_FORMAT_PAGE、LOP_COUNT_DELTA、LOP_HOBT_DELTA、LOP_INSYSXACT、LOP_INVALIDATE_CACHE、LOP_MIGRATE_LOCKS、LOP_SET_BITS、LOP_SET_FREE_SPACE、LOP_SHRINK_NOOP、LOP_TEXT_INFO_BEGIN、LOP_TEXT_INFO_END

[Begin Time]

事务操作的开始时间。

[PartitionID]

具体操作的哪个分区，可以关联查询到具体影响的哪个表或索引。

[TRANSACTION SID]

该事务操作的用户SID，可以通过SUSER_SNAME()函数转换为用户名。

具体示例分析

再来看一个具体事务操作：

                         
SELECT
[
Current
LSN], [
Transaction
ID], [
Transaction
Name
], [Operation], [
Begin
Time
], [PartitionID], [
TRANSACTION
SID]
FROM
fn_dump_dblog (
NULL
, 
NULL
, 
'DISK'
, 1, 
'D:\Pay\Pay_201707280400_LOG.trn'
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
,
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
, 
DEFAULT
)
WHERE
[
Transaction
ID]=
'0000:5c9b41e2'
;
        
      

根据[Transaction Name]为INSERT知道这是一个插入操作，具体哪条是插入的数据行，哪条是索引行，可以根据后面的PartitionID再去关联查询到。

根据[TRANSACTION SID]可以查询到操作的用户：

                         
SELECT
SUSER_SNAME(0x017017A631B52141B2338990DCFFADCC);

根据[PartitionID]查询到操作的对象：

                         
SELECT
so.
name
FROM
sys.objects so
INNER
JOIN
sys.partitions sp 
on
so.object_id = sp.object_id
WHERE
partition_id 
in
(
72057594041204736,
72057594070630400);
        
      

根据partition_id还可以更详细的查看是数据行还是索引行：

                         
--查看某个表的具体数据分布
SELECT
DISTINCT
so.
name
AS
'table_name'
, so.object_id,sp.partition_id,si.
name
AS
'index_name'
,internals.type_desc,internals.total_pages, internals.used_pages, internals.data_pages,first_iam_page, first_page, root_page
FROM
sys.objects so
INNER
JOIN
sys.partitions sp 
ON
so.object_id = sp.object_id
INNER
JOIN
sys.indexes si 
ON
sp.object_id = si.OBJECT_ID 
AND
sp.index_id = si.index_id
INNER
JOIN
sys.allocation_units sa 
ON
sa.container_id = sp.hobt_id
INNER
JOIN
sys.system_internals_allocation_units internals 
ON
internals.container_id = sa.container_id
WHERE
so.object_id = object_id(
'NotificationRecord'
);
        
      

                         
--查看某个表的索引详细信息
SELECT
TableId=O.[object_id],
TableName=O.
Name
,
IndexId=
ISNULL
(KC.[object_id],IDX.index_id),
IndexName=IDX.
Name
,
IndexType=
ISNULL
(KC.type_desc,
'Index'
),
Index_Column_id=IDXC.index_column_id,
ColumnID=C.Column_id,
ColumnName=C.
Name
,
Sort=
CASE
INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,
'IsDescending'
)
WHEN
1 
THEN
'DESC'
WHEN
0 
THEN
'ASC'
ELSE
''
END
,
PrimaryKey=
CASE
WHEN
IDX.is_primary_key=1 
THEN
N
'√'
ELSE
N
''
END
,
[UQIQUE]=
CASE
WHEN
IDX.is_unique=1 
THEN
N
'√'
ELSE
N
''
END
,
Ignore_dup_key=
CASE
WHEN
IDX.ignore_dup_key=1 
THEN
N
'√'
ELSE
N
''
END
,
Disabled=
CASE
WHEN
IDX.is_disabled=1 
THEN
N
'√'
ELSE
N
''
END
,
Fill_factor=IDX.fill_factor,
Padded=
CASE
WHEN
IDX.is_padded=1 
THEN
N
'√'
ELSE
N
''
END
FROM
sys.indexes IDX
INNER
JOIN
sys.index_columns IDXC
ON
IDX.[object_id]=IDXC.[object_id]
AND
IDX.index_id=IDXC.index_id
LEFT
JOIN
sys.key_constraints KC
ON
IDX.[object_id]=KC.[parent_object_id]
AND
IDX.index_id=KC.unique_index_id
INNER
JOIN
sys.objects O
ON
O.[object_id]=IDX.[object_id]
INNER
JOIN
sys.columns C
ON
O.[object_id]=C.[object_id]
AND
O.type=
'U'
AND
O.is_ms_shipped=0
AND
IDXC.Column_id=C.Column_id 
where
O.
name
=
'NotificationRecord'
;
        
      

本文转自UltraSQL51CTO博客，原文链接：http://blog.51cto.com/ultrasql/1953572 ，如需转载请自行联系原作者