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

用sql语句dbcclog查看SQLServer数据库的事务日志

1)用系统函数select

*

fromfn_dblog(null,null)2)用DBCCdbcclog(dbname,4)

--(n=0,1,2,3,4)1-更多信息plusflags,tags,rowlength2-非常详细的信息plusobjectname,indexname,pageid,slotid3-每种操作的全部信息4-每种操作的全部信息加上该事务的16进制信息

默认type=0

要查看MSATER数据库的事务日志可以用以下命令：DBCClog(master)使用fn_dblog解析SQLSERVER数据库日志方法一直以来我都很困惑，不知道怎么解析SQLSERVER的日志，因为微软提供了fn_dblog(NULL,NULL)和DBCCLOG获取数据库日志的基本信息，但是都是二进制码，看不懂。最近终于成功解析了SQLSERVERLOG信息在fn_dblog(NULL,NULL)输出结果中，获取表名是AllocUnitName字段。具体获取方法：AllocUnitNamelike'dbo.TEST%'操作类型是：Operation数据是：[RowLogContents0]字段内容如果是UPDATE操作：修改后数据存放在[RowLogContents1]字段内最基本3种操作类型：'LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW'具体解析代码如下：--解析日志

createfunctiondbo.f_splitBinary(@svarbinary(8000))

returns@ttable(idintidentity(1,1),Valuebinary(1))

as

begin

declare@iint,@imint

select@i=1,@im=datalength(@s)

while@i<=@im

begin

insertinto@tselectsubstring(@s,@i,1)

set@i=@i+1

end

return

endGOcreatefunctiondbo.f_reverseBinary(@svarbinary(128))

returnsvarbinary(128)

as

begin

declare@rvarbinary(128)

set@r=0x

select@r=@r+Valuefromdbo.f_splitBinary(@s)aorderbyiddesc

return@r

endGO

createproc[dbo].[p_getLog](@TableNamesysname,@cint=100)

/*

解析日志

:

p_getLog'tablename';*/

as

setnocounton

declare@svarbinary(8000),@s1varbinary(8000),@strvarchar(8000),@str1varchar(8000),@lbint,@leint,@operationvarchar(128)

declare@iint,@libint,@lieint,@ibint,@ieint,@lenVarint,@columnnamesysname,@lengthint,@columntypevarchar(32),@precint,@scaleint

declare@TUVLengthint,@vcint,@tcint,@bitAddint,@bitCountint,@countintselect,b.length,typename,b.colid,b.xprec,b.xscale,

casewhennotlike'%var%'andnotin('xml','text','image')then1else2endp,row_number()over(partitionby

casewhennotlike'%var%'andnotin('xml','text','image')then1else2endorderbycolid)pid

into#t

fromsysobjectsainnerjoinsyscolumnsbona.id=b.idinnerjoinsystypesconb.xtype=c.xusertype

=@TableNameorderbyb.colidSELECTtop(@c)Operation,[RowLogContents0],[RowLogContents1],[RowLogContents2],[RowLogContents3],[LogRecord],id=identity(int,1,1)into#t1

from::fn_dblog(null,null)

whereAllocUnitNamelike'dbo.'+@TableName+'%'and

Operationin('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW')

ANDContextnotin('LCX_IAM','LCX_PFS')

orderby[CurrentLSN]descselect@tc=count(*)from#tselect@lb=min(id),@le=max(id)from#t1

while@lb<=@le

begin

select@operation=Operation,@s=[RowLogContents0],@s1=[RowLogContents1]from#t1whereid=@lbAND[RowLogContents1]ISNOTNULL

set@TUVLength=convert(int,dbo.f_reverseBinary(substring(@s,3,2)))+3

select@i=5,@str='',@vc=0,@bitCount=0

select@lib=min(pid),@lie=max(pid)from#twherep=1

while@lib<=@lie

begin

select@columnname=name,@length=length,@columntype=typename,@prec=xprec,@scale=xscale,@vc=colid-1from#twherep=1andpid=@lib

--

if@columntype<>'bit'

--

printrtrim(@i)+'->'+rtrim(@length)

ifdbo.f_reverseBinary(substring(@s,@TUVLength,1+((@tc-1)/8)))&power(2,@vc)<>0

begin

if@columntype<>'bit'

select@str=@str+@columnname+'=NULL,',@i=@i+@length

else

begin

select@str=@str+@columnname+'=NULL,'

set@bitAdd=casewhen@bitCount=0then@ielse@bitAddend

set@bitCount=(@bitCount+1)%8

set@i=@i+case@bitCountwhen1then1else0end

--

printrtrim(@bitAdd)+'->'+rtrim(@length)

end

end

elseif@columntype='char'

select@str=@str+@columnname+'='+convert(varchar(256),substring(@s,@i,@length))+',',@i=@i+@length

elseif@columntype='nchar'

select@str=@str+@columnname+'='+convert(nvarchar(256),substring(@s,@i,@length))+',',@i=@i+@length

elseif@columntype='datetime'

select@str=@str+@columnname+'='+convert(varchar,dateadd(second,convert(int,dbo.f_reverseBinary(substring(@s,@i,4)))/300

,dateadd(day,convert(int,dbo.f_reverseBinary(substring(@s,@i+4,4))),'1900-01-01')),120)+',',@i=@i+8

elseif@columntype='smalldatetime'

select@str=@str+@columnname+'='+convert(varchar,dateadd(minute,convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))

,dateadd(day,convert(int,dbo.f_reverseBinary(substring(@s,@i+2,2))),'1900-01-01')),120)+',',@i=@i+4

elseif@columntype='int'

select@str=@str+@columnname+'='+rtrim(convert(int,dbo.f_reverseBinary(substring(@s,@i,4))))+',',@i=@i+4

elseif@columntype='decimal'

select@str=@str+@columnname+'=DECIMAL,',@i=@i+@length

elseif@columntype='bit'

begin

set@bitAdd=casewhen@bitCount=0then@ielse@bitAddend

set@bitCount=(@bitCount+1)%8

select@str=@str+@columnname+'='+rtrim(convert(bit,substring(@s,@bitAdd,1)&power(2,case@bitCountwhen0then8else@bitCountend-1)))+','

,@i=@i+case@bitCountwhen1then1else0end

--

printrtrim(@bitAdd)+'->'+rtrim(@length)

end

set@lib=@lib+1

end

set@i=convert(int,dbo.f_reverseBinary(substring(@s,3,2)))+4+((@tc-1)/8)

set@lenVar=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))

set@i=@i+2

set@ib=@i+@lenVar*2

set@ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))

set@count=0

select@lib=min(pid),@lie=max(pid)from#twherep=2

while@lib<=@lie

begin

--

printrtrim(@ib)+'->'+rtrim(@ie)

select@columnname=name,@length=length,@columntype=typename,@vc=colid-1from#twherep=2andpid=@lib

ifdbo.f_reverseBinary(substring(@s,@TUVLength,1+((@tc-1)/8)))&power(2,@vc)<>0

begin

select@str=@str+@columnname+'=NULL,'

select@ib=@ie+1,@i=@i+2

if@count<@lenVar

set@ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))

end

elseif@columntype='varchar'

begin

select@str=@str+@columnname+'='+convert(varchar(256),substring(@s,@ib,@ie-@ib+1))+','

select@ib=@ie+1,@i=@i+2

set@ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))

end

elseif@columntype='nvarchar'

begin

select@str=@str+@columnname+'='+convert(nvarchar(256),substring(@s,@ib,@ie-@ib+1))+','

select@ib=@ie+1,@i=@i+2

set@ie=convert(int,dbo.f_reverseBinary(substring(@s,@i,2)))

end

set@count=@count+1

set@lib=@lib+1

end

set@str=left(@str,len(@str)-1)

IF@operation='LOP_MODIFY_ROW'

BEGIN

set@TUVLength=convert(int,dbo.f_reverseBinary(substring(@s1,3,2)))+3

select@i=5,@str1='',@vc=0,@bitCount=0

select@lib=min(pid),@lie=max(pid)from#twherep=1

while@lib<=@lie

begin

select@columnname=name,@length=length,@columntype=typename,@prec=xprec,@scale=xscale,@vc=colid-1from#twherep=1andpid=@lib

--

if@columntype<>'bit'

--

printrtrim(@i)+'->'+rtrim(@length)

ifdbo.f_reverseBinary(substring(@s1,@TUVLength,1+((@tc-1)/8)))&power(2,@vc)<>0

begin

if@columntype<>'bit'

select@str1=@str1+@columnname+'=NULL,',@i=@i+@length

else

begin

select@str1=@str1+@columnname+'=NULL,'

set@bitAdd=casewhen@bitCount=0then@ielse@bitAddend

set@bitCount=(@bitCount+1)%8

set@i=@i+case@bitCountwhen1then1else0end

--

printrtrim(@bitAdd)+'->'+rtrim(@length)

end

end

elseif@columntype='char'

select@str1=@str1+@columnname+'='+convert(varchar(256),substring(@s1,@i,@length))+',',@i=@i+@length

elseif@columntype='nchar'

select@str1=@str1+@columnname+'='+convert(nvarchar(256),substring(@s1,@i,@length))+',',@i=@i+@length

elseif@columntype='datetime'

select@str1=@str1+@columnname+'='+convert(varchar,dateadd(second,convert(int,dbo.f_reverseBinary(substring(@s1,@i,4)))/300

,dateadd(day,convert(int,dbo.f_reverseBinary(substring(@s1,@i+4,4))),'1900-01-01')),120)+',',@i=@i+8

elseif@columntype='smalldatetime'

select@str1=@str1+@columnname+'='+convert(varchar,dateadd(minute,convert(int,dbo.f_reverseBinary(substring(@s1,@i,2)))

,dateadd(day,convert(int,dbo.f_reverseBinary(substring(@s1,@i+2,2))),'1900-01-01')),120)+',',@i=@i+4

elseif@columntype='int'

select@str1=@str1+@columnname+'='+rtrim(convert(int,dbo.f_reverseBinary(substring(@s1,@i,4))))+',',@i=@i+4

elseif@columntype='decimal'

select@str1=@str1+@columnname+'=DECIMAL,',@i=@i+@length

elseif@columntype='bit'

begin

set@bitAdd=casewhen@bitCount=0then@ielse@bitAddend

set@bitCount=(@bitCount+1)%8

select@str1=@str1+@columnname+'='+rtrim(convert(bit,substring(@s1,@bitAdd,1)&power(2,case@bitCountwhen0then8else@bitCountend-1)))+','

,@i=@i+case@bitCountwhen1then1else0end

--

printrtrim(@bitAdd)+'->'+rtrim(@length)

end

set@lib=@lib+1

end

set@i=convert(int,dbo.f_reverseBinary(substring(@s1,3,2)))+4+((@tc-1)/8)

set@lenVar=convert(int,dbo.f_reverseBinary(substring(@s1,@i,2)))

set@i=@i+2

set@ib=@i+@lenVar*2

set@ie=convert(int,dbo.f_reverseBinary(substring(@s1,@i,2)))

set@count=0

select@lib=min(pid),@lie=max(pid)from#twherep=2

while@lib<=@lie

begin

--

printrtrim(@ib)+'->'+rtrim(@ie)

select@columnname=name,@length=length,@columntype=typename,@vc=colid-1from#twherep=2andpid=@lib

ifdbo.f_reverseBinary(substring(@s1,@TUVLength,1+((@tc