データベース・スクリプトを使用してSqlServer 2005データベースのLOGログを分析
81491 ワード
SqlServer 2005データベースのLOGログの分析
主な方法:
2.必要なカスタム関数
3.呼び出し方法
主な方法:
alter
proc
[
dbo
]
.
[
p_getLog
]
(
@TableName
sysname,
@c
int
=
10
)
AS
BEGIN
set
nocount
on
declare
@s
varbinary
(
max
),
@str
varchar
(
max
),
@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
)
declare
@TUVLength
int
,
@vc
int
,
@tc
int
select
b.name,b.length,c.name typename,b.colid,
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
]
,id
=
identity
(
int
,
1
,
1
)
into
#t1
from
::fn_dblog (
null
,
null
)
where
AllocUnitName
like
'
dbo.
'
+
@TableName
+
'
%
'
and
Operation
in
(
'
LOP_DELETE_ROWS
'
)
AND
allocunitname
like
'
%PK_TEMPLOYEESTATION%
'
order
by
[
Current LSN
]
DESC
--
'LOP_INSERT_ROWS',
select
@TUVLength
=
sum
(length)
from
#t
where
p
=
1
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
]
from
#t1
where
id
=
@lb
select
@i
=
5
,
@str
=
''
,
@vc
=
0
select
@lib
=
min
(pid),
@lie
=
max
(pid)
from
#t
where
p
=
1
while
@lib
<=
@lie
begin
select
@columnname
=
name,
@length
=
length,
@columntype
=
typename
from
#t
where
p
=
1
and
pid
=
@lib
--
print rtrim(@i)+'->'+rtrim(@length)
if
dbo.f_reverseBinary(
substring
(
@s
,
4
+
@TUVLength
+
2
+
1
,
1
+
((
@tc
-
1
)
/
8
)))
&
power
(
2
,
@vc
)
<>
0
select
@str
=
@str
+
@columnname
+
'
=NULL,
'
,
@i
=
@i
+
@length
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
'
BEGIN
--
PRINT '@i= ' + CONVERT(NVARCHAR,@i)
--
PRINT 'pct: '+convert(nvarchar,convert(int,dbo.f_reverseBinary(substring(@s,@i+4,4))));
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
;
END
else
if
@columntype
=
'
smalldatetime
'
select
@str
=
@str
+
@columnname
+
'
=
'
+
convert
(
varchar
,
dateadd
(minute,
convert
(
int
,dbo.f_reverseBinary(
substring
(
@s
,
@i
,
2
)))
/
60
,
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='bit'
--
begin
--
select @str=@str+@columnname+'='+rtrim(convert(bit,substring(@s,@i,1)))+',',@i=@i+1
--
end
set
@vc
=
@vc
+
1
;
set
@lib
=
@lib
+
1
;
end
set
@i
=
@i
+
3
+
((
@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
)));
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
from
#t
where
p
=
2
and
pid
=
@lib
;
if
dbo.f_reverseBinary(
substring
(
@s
,
4
+
@TUVLength
+
2
+
1
,
1
+
((
@tc
-
1
)
/
8
)))
&
power
(
2
,
@vc
)
<>
0
begin
select
@str
=
@str
+
@columnname
+
'
=NULL,
'
;
select
@ib
=
@ie
+
1
,
@i
=
@i
+
2
;
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
@vc
=
@vc
+
1
;
set
@lib
=
@lib
+
1
;
end
set
@str
=left
(
@str
,
len
(
@str
)
-
1
);
print
@operation
+
'
:
'
+
@str
;
set
@lb
=
@lb
+
1
;
end
drop
table
#t,#t1
END
GO
2.必要なカスタム関数
--
Function
ALTER
FUNCTION
dbo.f_splitBinary(
@s
varbinary
(
max
))
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
ALTER
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
ALTER
PROCEDURE
[
dbo
]
.
[
p_printSql
]
(
@sql
varchar
(
max
),
@flag
char
(
1
)
=
'
,
'
,
@intal
varchar
(
5
)
=
''
)
as
BEGIN
declare
@l_sql
varchar
(
max
),
@i
int
,
@l
int
;
set
@l
=
len
(
@sql
);
while
@l
>
8000
-
5
begin
select
@i
=
8000
-
5
-
charindex
(
@flag
,
reverse
(
left
(
@sql
,
8000
-
5
))
),
@l_sql
=left
(
@sql
,
@i
),
@sql
=right
(
@sql
,
@l
-
@i
),
@l
=
@l
-
@i
;
print
@intal
+
@l_sql
;
end
print
@intal
+
@sql
END
GO
3.呼び出し方法
exec
dbo.
[
p_getLog
]
'
TEmployeeStation
'
;
GO