Sunday, February 27, 2011

Read Transaction log using fn_dblog()

Use this if you want to read the transaction log

Its easy and simple

select * from fn_dblog(null,null) gives you the data
query it with partition numbers gives you the complete details.

select partition_id from sys.partitions where object_id=3435334

select * from fn_dblog(null,null) where partition_id= 343457576523
Now you can see the page ids, transaction types, etc.
Page ids you can get from
dbcc ind and dbcc page

eg:

select * from fn_dblog(null,null) where allocunitid in
 (
 select allocation_unit_id from sys.allocation_units where container_id in
 (select partition_id from sys.partitions where object_id=1555184986)
 )

dbcc ind(5,1396968103,9)

dbcc page(5,1,7905811,3)

Finally we got the individual row and key values after the DBCC page,
this wya we can find out what are the rows existing in each page and can now easily find out which pages are modified/updated/deleted in the Tlog.

No comments:

Post a Comment