《2022年用sql语句dbcclog查看SQLServer数据库的事务日志归纳 .pdf》由会员分享，可在线阅读，更多相关《2022年用sql语句dbcclog查看SQLServer数据库的事务日志归纳 .pdf（9页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、用 sql 语句 dbcc log 查看 SQL Server 数据库的事务日志1) 用系统函数select * from fn_dblog(null,null) 2) 用 DBCC dbcc log(dbname,4) -(n=0,1,2,3,4) 1 - 更多信息 plus flags, tags, row length 2 - 非常详细的信息 plus object name, index name,page id, slot id 3 - 每种操作的全部信息4 - 每种操作的全部信息加上该事务的16 进制信息默认 type = 0 要查看 MSATER 数据库的事务日志可以用以下命令：
2、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字段内
3、容名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 9 页 - - - - - - - - - 如果是 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
4、identity(1,1),Value binary(1) as begin declare i int,im int select i=1,im=datalength(s) while i =imbegin insert into t select substring(s,i,1) set i=i+1 end return end GO create function dbo.f_reverseBinary(s varbinary(128) returns varbinary(128) as begin declare r varbinary(128) set r=0 x select r=
5、r+Value from dbo.f_splitBinary(s) a order by id desc return r end GO create proc dbo.p_getLog(TableName sysname,c int=100) /* 解析日志: p_getLog tablename; */ as set nocount on declare s varbinary(8000),s1 varbinary(8000),str varchar(8000),str1 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师
6、精心整理 - - - - - - - 第 2 页，共 9 页 - - - - - - - - - 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 int declare TUVLength int,vc int,tc int,bitAdd int,bitCount int,count int sel
7、ect 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) pid into #t from sy
8、sobjects a inner join syscolumns b on a.id=b.id inner join systypes c on b.xtype=c.xusertype wherea.name=TableNameorder by b.colid SELECT top(c) Operation,RowLog Contents 0,RowLog Contents 1,RowLog Contents 2,RowLog Contents 3,Log Record,id=identity(int,1,1) into #t1 from:fn_dblog (null, null) where
9、 AllocUnitName likedbo.+TableName+%and Operation in(LOP_INSERT_ROWS,LOP_DELETE_ROWS,LOP_MODIFY_ROW ) AND Context not in (LCX_IAM,LCX_PFS) order by Current LSN desc select tc=count(*) from #t select lb=min(id),le=max(id) from #t1 while lb =lebegin select operation=Operation,s=RowLog Contents 0,s1=Row
10、Log Contents 1 from #t1 whereid=lbAND RowLog Contents 1 IS NOT NULL 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 #t where p=1 while lib=lie begin select columnname=name,length=length,columntype=typename,prec=xp
11、rec,scale=xscale,vc=colid-1 from #t where p=1 andpid=lib- if columntypebit - print rtrim(i)+-+rtrim(length) if dbo.f_reverseBinary(substring(s,TUVLength,1+(tc-1)/8) & power(2,vc) 0 begin if columntypebit 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 9 页 - - -
12、- - - - - - select str=str+columnname+=NULL,i=i+length else begin select str=str+columnname+=NULL, set bitAdd = case when bitCount=0 then i else bitAdd end set bitCount = (bitCount + 1)%8 set i=i+case bitCount when 1 then 1 else 0 end - print rtrim(bitAdd)+-+rtrim(length) end end else if columntype=
13、char select str=str+columnname+=+convert(varchar(256),substring(s,i,length)+,i=i+length else if columntype=nchar select str=str+columnname+=+convert(nvarchar(256),substring(s,i,length)+,i=i+length else if columntype=datetime select str=str+columnname+=+convert(varchar,dateadd(second,convert(int,dbo.
14、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 else if 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_rev
15、erseBinary(substring(s,i+2,2),1900-01-01),120)+,i=i+4 else if columntype=int select str=str+columnname+=+rtrim(convert(int,dbo.f_reverseBinary(substring(s,i,4)+,i=i+4 else if columntype=decimal select str=str+columnname+=DECIMAL,i=i+length else if columntype=bit begin set bitAdd = case when bitCount
16、=0 then i else bitAdd end set bitCount = (bitCount + 1)%8 select str=str+columnname+=+rtrim(convert(bit,substring(s,bitAdd,1)&power(2,case bitCount when 0 then 8 else bitCount end-1)+, ,i=i+case bitCount when 1 then 1 else 0 end 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - -
17、 - - - 第 4 页，共 9 页 - - - - - - - - - - print rtrim(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(substri
18、ng(s,i,2) set count=0 select lib=min(pid),lie=max(pid) from #t where p=2 while lib+rtrim(ie) select columnname=name,length=length,columntype=typename,vc=colid-1 from #t where p=2 andpid=lib if dbo.f_reverseBinary(substring(s,TUVLength,1+(tc-1)/8) & power(2,vc) 0 begin select str=str+columnname+=NULL
19、, select ib=ie+1,i=i+2 if countlenVar set ie=convert(int,dbo.f_reverseBinary(substring(s,i,2) end else if 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 else if colum
20、ntype=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) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - -
21、名师精心整理 - - - - - - - 第 5 页，共 9 页 - - - - - - - - - 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 #t where p=1 while lib=lie begin select columnname=name,length=length,columnt
22、ype=typename,prec=xprec,scale=xscale,vc=colid-1 from #t where p=1 andpid=lib - if columntypebit - print rtrim(i)+-+rtrim(length) if dbo.f_reverseBinary(substring(s1,TUVLength,1+(tc-1)/8) & power(2,vc) 0 begin if columntypebit select str1=str1+columnname+=NULL,i=i+length else begin select str1=str1+c
23、olumnname+=NULL, set bitAdd = case when bitCount=0 then i else bitAdd end set bitCount = (bitCount + 1)%8 set i=i+case bitCount when 1 then 1 else 0 end - print rtrim(bitAdd)+-+rtrim(length) end end else if columntype=char select str1=str1+columnname+=+convert(varchar(256),substring(s1,i,length)+,i=
24、i+length else if columntype=nchar select str1=str1+columnname+=+convert(nvarchar(256),substring(s1,i,length)+,i=i+length else if 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_rever
25、seBinary(substring(s1,i+4,4),1900-01-01),120)+,i=i+8 else if columntype=smalldatetime select str1=str1+columnname+=+convert(varchar,dateadd(minute,convert(int,dbo.f_re名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 9 页 - - - - - - - - - verseBinary(substring(s1,
26、i,2) ,dateadd(day,convert(int,dbo.f_reverseBinary(substring(s1,i+2,2),1900-01-01),120)+,i=i+4 else if columntype=int select str1=str1+columnname+=+rtrim(convert(int,dbo.f_reverseBinary(substring(s1,i,4)+,i=i+4 else if columntype=decimal select str1=str1+columnname+=DECIMAL,i=i+length else if columnt
27、ype=bit begin set bitAdd = case when bitCount=0 then i else bitAdd end set bitCount = (bitCount + 1)%8 select str1=str1+columnname+=+rtrim(convert(bit,substring(s1,bitAdd,1)&power(2,case bitCount when 0 then 8 else bitCount end-1)+, ,i=i+case bitCount when 1 then 1 else 0 end - print rtrim(bitAdd)+-
28、+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) fr
29、om #t where p=2 while lib+rtrim(ie) select columnname=name,length=length,columntype=typename,vc=colid-1 from #t where p=2 andpid=lib if dbo.f_reverseBinary(substring(s1,TUVLength,1+(tc-1)/8) & power(2,vc) 0 begin select str1=str1+columnname+=NULL, select ib=ie+1,i=i+2 if countlenVar set ie=convert(i
30、nt,dbo.f_reverseBinary(substring(s1,i,2) end else if columntype=varchar begin select 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 9 页 - - - - - - - - - str1=str1+columnname+=+convert(varchar(256),substring(s1,ib,ie-ib+1)+, select ib=ie+1,i=i+2 set ie=convert(
31、int,dbo.f_reverseBinary(substring(s1,i,2) end else if columntype=nvarchar begin select str1=str1+columnname+=+convert(nvarchar(256),substring(s1,ib,ie-ib+1)+, select ib=ie+1,i=i+2 set ie=convert(int,dbo.f_reverseBinary(substring(s1,i,2) end set count=count+1 set lib=lib+1 end set str1=left(str1,len(
32、str1)-1) END IF operation =LOP_MODIFY_ROW BEGIN print operation+修改前值： +str print operation+修改后值： +str1 END ELSE BEGIN print operation+:+str END set lb=lb+1 END drop table #t,#t1 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 9 页 - - - - - - - - - GO 因为目前解析SQL 2008 会有很多错误，要是认识微软引擎组的人就好了！名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 9 页 - - - - - - - - -