SQL Server事务日志分析

　　

<强>



SQL服务器有两个未公开的函数<强> 和<强> 非常有用并且提供的信息量很大。你可以使用这些函数来获取100多列大量的有用信息。


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


例如:

SELECT  *,得到fn_dblog (NULL, NULL);


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


例如:

SELECT  * 　　得到fn_dump_dblog  ( 　　空,空,,‘盘’,,1,,“D: \ \ Pay_201707280400_LOG.trn支付”, 　　违约,违约,违约,违约,违约,违约,违约, 　　违约,违约,违约,违约,违约,违约,违约, 　　违约,违约,违约,违约,违约,违约,违约, 　　违约,违约,违约,违约,违约,违约,违约, 　　违约,违约,违约,违约,违约,违约,违约, 　　违约,违约,违约,违约,违约,违约,违约, 　　违约,违约,违约,违约,违约,违约,违约, 　　违约,违约,违约,违约,违约,违约,违约, 　　DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);


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

SQL Server事务日志分析



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


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

INSERT、UPDATE、DELETE、DROPOBJ

次要值有：

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

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


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

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


事务操作的开始时间。


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


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



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

SELECT [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], [PartitionID], [TRANSACTION SID]
　　得到fn_dump_dblog  (
　　空,空,,‘盘’,,1,,“D: \ \ Pay_201707280400_LOG.trn支付”,
　　违约,违约,违约,违约,违约,违约,违约,
　　违约,违约,违约,违约,违约,违约,违约,
　　违约,违约,违约,违约,违约,违约,违约,
　　违约,违约,违约,违约,违约,违约,违约,
　　违约,违约,违约,违约,违约,违约,违约,
　　违约,违约,违约,违约,违约,违约,违约,
　　违约,违约,违约,违约,违约,违约,违约,
　　违约,违约,违约,违约,违约,违约,违约,
　　违约,违约,违约,违约,违约,违约,违约)
　　WHERE  [Transaction  ID]=?000:5c9b41e2”;

SQL Server事务日志分析


根据(事务名)为插入知道这是一个插入操作,具体哪条是插入的数据行,哪条是索引行,可以根据后面的PartitionID再去关联查询到。


根据(事务SID)可以查询到操作的用户:

SELECT  SUSER_SNAME (0 x017017a631b52141b2338990dcffadcc);


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

SELECT  so.name 　　得到sys.objects  　　INNER  JOIN  sys.partitions  sp 提醒so.object_id =sp.object_id 　　WHERE  partition_id  ( 　　72057594041204736, 　　72057594070630400),

SQL Server事务日志分析