用sql语句dbcc log 查看SQL Server 数据库事务日志.docxVIP

用sql语句dbcc log 查看SQL Server 数据库的事务日志?1)用系统函数select?? *?? from fn_dblog(null,null)2)用DBCCdbcc log(dbname,4)?????? --(n=0,1,2,3,4)1 - 更多信息plus flags, tags, row length2 - 非常详细的信息plus object name, index name,page id, slot id3 - 每种操作的全部信息4 - 每种操作的全部信息加上该事务的16进制信息默认 type = 0要查看MSATER数据库的事务日志可以用以下命令：DBCC log (master)使用fn_dblog解析SQL SERVER 数据库日志方法一直以来我都很困惑，不知道怎么解析SQL SERVER的日志，因为微软提供了fn_dblog(NULL,NULL)与DBCC LOG获取数据库日志的基本信息，但是都是二进制码，看不懂。最近终于成功解析了SQL SERVER LOG信息在fn_dblog(NULL,NULL)输出结果中，获取表名是AllocUnitName字段。具体获取方法：AllocUnitName like dbo.TEST%操作类型是：Operation数据是：[RowLog Contents 0]字段内容如果是UPDATE操作：修改后数据存放在[RowLog Contents 1]字段内最基本3种操作类型：LOP_INSERT_ROWS,LOP_DELETE_ROWS,LOP_MODIFY_ROW具体解析代码如下：--解析日志create function dbo.f_splitBinary(@s varbinary(8000))returns @t table(id int identity(1,1),Value binary(1))asbegindeclare @i int,@im intselect @i=1,@im=datalength(@s)while @i=@imbegin? insert into @t select substring(@s,@i,1)? set @i=@i+1endreturnendGOcreate function dbo.f_reverseBinary(@s varbinary(128))returns varbinary(128)asbegindeclare @r varbinary(128)set @r=0xselect @r=@r+Value from dbo.f_splitBinary(@s) a order by id descreturn @rendGO??create proc [dbo].[p_getLog](@TableName sysname,@c int=100)/*解析日志:? p_getLog tablename;*/asset nocount ondeclare @s varbinary(8000),@s1 varbinary(8000),@str varchar(8000),@str1 varchar(8000),@lb int,@le int,@operation varchar(128)declare @i int,@lib int,@lie int,@ib int,@ie int,@lenVar int,@columnname sysname,@length int,@columntype varchar(32),@prec int,@scale intdeclare @TUVLength int,@vc int,@tc int,@bitAdd int,@bitCount int,@count intselect b.name,b.length,c.name typename,b.colid,b.xprec,b.xscale,??? case when c.name not like %var% and c.name not in (xml,text,image) then 1 else 2 end p,row_number() over(partition by??? case when c.name not like %var% and c.name not in (xml,text,image) then 1 else 2 end order by colid) pidinto #t??? from sysobjects a inner join syscolumns b on a.id=b.id inner join systypes c on b.xtype=c.xusertype??? wherea.