使用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))
as
begin
declare @i int,@im int
select @i=1,@im=datalength(@s)
while @i<=@im
begin
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=0x
select @r=@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)
/*
解析日誌
胡冰
時間:2010年12月30日
工作單位:www.5173.com
sample: p_getLog 'tablename';
*/
as
set nocount on
declare @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 int
declare @TUVLength int,@vc int,@tc int,@bitAdd int,@bitCount int,@count int
select 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 sysobjects a inner join syscolumns b on a.id=b.id inner join systypes c on b.xtype=c.xusertype
where a.name=@TableName order 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 AllocUnitName like 'dbo.'+@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<=@le
begin
select @operation=Operation,@s=[RowLog Contents 0],@s1=[RowLog Contents 1] from #t1 where id=@lb AND [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=xprec,@scale=xscale,@vc=colid-1 from #t where p=1 and pid=@lib
-- if @columntype<>'bit'
-- print rtrim(@i)+'->'+rtrim(@length)
if dbo.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 = 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 @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.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_reverseBinary(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=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
-- 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(substring(@s,@i,2)))
set @count=0
select @lib=min(pid),@lie=max(pid) from #t where p=2
while @lib<=@lie
begin
-- print rtrim(@ib)+'->'+rtrim(@ie)
select @columnname=name,@length=length,@columntype=typename,@vc=colid-1 from #t where p=2 and pid=@lib
if dbo.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 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 @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 #t where p=1
while @lib<=@lie
begin
select @columnname=name,@length=length,@columntype=typename,@prec=xprec,@scale=xscale,@vc=colid-1 from #t where p=1 and pid=@lib
-- if @columntype<>'bit'
-- print rtrim(@i)+'->'+rtrim(@length)
if dbo.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 = 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=@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_reverseBinary(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_reverseBinary(substring(@s1,@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 @columntype='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)+'->'+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 #t where p=2
while @lib<=@lie
begin
-- print rtrim(@ib)+'->'+rtrim(@ie)
select @columnname=name,@length=length,@columntype=typename,@vc=colid-1 from #t where p=2 and pid=@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 @count set @ie=convert(int,dbo.f_reverseBinary(substring(@s1,@i,2)))
end
else if @columntype='varchar'
begin
select @str1=@str1+@columnname+'='+convert(varchar(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
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(@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
GO
準備元旦後再改改,
因為目前解析SQL 2008會有很多錯誤,要是認識微軟引擎組的人就好了!
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8183550/viewspace-682907/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 清除SQL Server資料庫日誌SQLServer資料庫
- SQL Server資料庫日誌清除的兩個方法SQLServer資料庫
- SQL Server 檢視資料庫日誌SQLServer資料庫
- SQL server資料庫高可用日誌傳送的方法SQLServer資料庫
- SQL Server 清除資料庫日誌指令碼SQLServer資料庫指令碼
- SQL Server 無日誌檔案附加資料庫SQLServer資料庫
- SQL Server無日誌資料庫恢復模式SQLServer資料庫模式
- SQL Server資料庫事務日誌儲存序列SQLServer資料庫
- SQL SERVER 資料庫日誌收縮整理 三種方法軼事分離資料庫而是清空日誌三是截斷日誌SQLServer資料庫
- Sql Server2014資料庫清理日誌SQLServer資料庫
- SQL Server 2005無日誌檔案附加資料庫SQLServer資料庫
- SQL Server資料庫事務日誌序列號(LSN)介紹SQLServer資料庫
- SQL Server 2005資料庫日誌丟失的恢復SQLServer資料庫
- 查詢SQL Server 2005資料庫重做日誌的資訊SQLServer資料庫
- 清除SQL Server日誌的方法介紹SQLServer
- Blazor使用sql server 資料庫BlazorSQLServer資料庫
- 用sql語句dbcc log 檢視SQL Server 資料庫的事務日誌SQLServer資料庫
- SQL Server中合併使用者日誌表的方法SQLServer
- SQL Server 收縮事務日誌的方法SQLServer
- 刪除SQL Server日誌的具體方法SQLServer
- SQL Server事務日誌的處理方法SQLServer
- SQL SERVER日誌清除的兩種方法(轉)SQLServer
- SQL SERVER收集資料庫使用增量資料SQLServer資料庫
- 瀚高資料庫日誌挖掘方法資料庫
- MS SQL Server 資料庫備份方法SQLServer資料庫
- SQL Server置疑資料庫解決方法SQLServer資料庫
- SQL Server 收縮日誌SQLServer
- SQL Server 錯誤日誌SQLServer
- SQL Server 記憶體資料庫原理解析SQLServer記憶體資料庫
- Sql server日誌損壞後的資料恢復(轉)SQLServer資料恢復
- 清理資料庫監聽日誌最好方法資料庫
- 使用SQL Server過濾資料的方法SQLServer
- SQL Server資料庫內容替換方法SQLServer資料庫
- 附加和分離SQL Server資料庫方法SQLServer資料庫
- 細說SQL Server資料庫備份方法SQLServer資料庫
- 優化SQL Server資料庫查詢方法優化SQLServer資料庫
- SQL Server資料庫建立新使用者及關聯資料庫的方法教程SQLServer資料庫
- 從 Oracle 日誌解析學習資料庫核心原理Oracle資料庫