sys.fn_dblog返回当前数据库日志的活动部分的内容
use
db1
go
if exists ( select 1 from sys.tables where name = ' t ' )
drop table t
go
create table t(col int )
go
dbcc traceon( 3505 , - 1 )---disable automatic checkpoint behaviorhttp://support.microsoft.com/kb/815436/en-us
go
checkpoint
go
select [ Current LSN ] , [ Previous LSN ] , [ Previous Page LSN ] ,PartitionId, Operation,Context, [ Transaction ID ] , [ Log Record Fixed Length ] , [ Log Record Length ] from sys.fn_dblog( null , null )
go
insert into t values ( 1 ) --
go
select [ Current LSN ] , [ Previous LSN ] , [ Previous Page LSN ] ,PartitionId, Operation,Context, [ Transaction ID ] , [ Log Record Fixed Length ] , [ Log Record Length ] from sys.fn_dblog( null , null )— - sql server need to allocate space for the new table
go
checkpoint
go
insert into t values ( 1 )— -
go
select [ Current LSN ] , [ Previous LSN ] , [ Previous Page LSN ] ,PartitionId, Operation,Context, [ Transaction ID ] , [ Log Record Fixed Length ] , [ Log Record Length ] from sys.fn_dblog( null , null )— - only insert operation
go
if exists ( select 1 from sys.tables where name = ' t ' )
drop table t
go
create table t(col int )
go
dbcc traceon( 3505 , - 1 )---disable automatic checkpoint behaviorhttp://support.microsoft.com/kb/815436/en-us
go
checkpoint
go
select [ Current LSN ] , [ Previous LSN ] , [ Previous Page LSN ] ,PartitionId, Operation,Context, [ Transaction ID ] , [ Log Record Fixed Length ] , [ Log Record Length ] from sys.fn_dblog( null , null )
go
insert into t values ( 1 ) --
go
select [ Current LSN ] , [ Previous LSN ] , [ Previous Page LSN ] ,PartitionId, Operation,Context, [ Transaction ID ] , [ Log Record Fixed Length ] , [ Log Record Length ] from sys.fn_dblog( null , null )— - sql server need to allocate space for the new table
go
checkpoint
go
insert into t values ( 1 )— -
go
select [ Current LSN ] , [ Previous LSN ] , [ Previous Page LSN ] ,PartitionId, Operation,Context, [ Transaction ID ] , [ Log Record Fixed Length ] , [ Log Record Length ] from sys.fn_dblog( null , null )— - only insert operation
http://support.microsoft.com/kb/815436/en-us
2008 的操作日志怎么解析,特别是 LOP_MODIFY_ROW 操作? 与 LOP_INSERT_ROWS LOP_DELETE_ROWS 不同
数据库的insert,udpate,delte操作会记录在日志里,
对于insert操作,会产生after image,也就是变更后的结果,
对于delete/update操作,会产生before image(记录变更前的信息)和after image(记录变更后的信息)
通过before image和 after image就可以找到数据变更前和变更后的状态,从而用于数据库恢复,或者事物的回滚。
你可以进行一些insert,update,deltet操作,观察
[RowLog Contents 0]
,[RowLog Contents 1]