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';