




版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
用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
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 2025年大流量罗茨鼓风机项目合作计划书
- 鄂州消防招聘政府专职消防员笔试真题2024
- 麻醉药品、精神药品全程化管理要点2025
- 2025年己二酸二甲酯项目发展计划
- 广州版综合实践活动四年级下册第一单元第2课《运动与健康同行》教案
- 2025年吉林通化梅河口市“事编助企”引进企业招聘考试笔试试题【答案】
- 2025年南宁市武鸣区特岗教师招聘考试笔试试题【答案】
- 2025年特种设备检验检测项目合作计划书
- 贷款卡业务申报办理流程说明
- 教育政策的多元化应用与实践探索
- 工业废水处理工(中级工)理论试题库汇总-上(单选、多选题)
- 潜水泵操作JSA分析表
- DL∕T 5622-2021 太阳能热发电厂储热系统设计规范
- 物理化学实验:实验12 胶体的制备和电泳
- 高中物理选修 分子动理论
- 领军人才选拔试题答案
- CNC数控车床操作指导书
- 管道施工主要质量保证措施及通病防治措施
- 失火罪消防责任事故罪消防刑事案件移送移交报告
- 斯巴达勇士赛
- 住院医师规范化培训临床小讲课指南(2021年版)
评论
0/150
提交评论