T-SQL – How to Read Transaction Log Records

t-sqltransactiontransaction-log

I am reading Remus Rusanu article on tlog.In that article he demoed on how to read log payload by using dbcc page option .My question is ,is there a way to read logrecord output of fn_dblog(null,null) from sql or c#.PFB screen shot for more details.enter image description here

Best Answer

My question is ,is there a way to read logrecord output of fn_dblog(null,null) from sql

Reading transaction log using fn_dblog is not supported by Microsoft. So you should avoid running this command on production server. You can run this on UAT/Test environment.

What do you exactly want to read from output of fn_dblog, can you be precise. There are myraids of information you can get from output of fn_dblog. Its not easy to read the output and one requires some level of expertise to read it.

I will give you a Demo.

BEGIN TRANSACTION TRANSACTION_NAME
SQL QUERY
COMMIT TRANSACTION OR ROLLBACK TRANSACTION

--Then use below query to filter out records related to the transaction

USE DATABASE_NAME
GO
SELECT
OPERATION,
Context,
[Transaction Name],
[AllocUnitId],
[Page ID],
Description,
[Lock Information]
FROM FN_DBLOG(NULL,NULL)
WHERE [Transaction ID] in
(
select [Transaction ID] from
fn_dblog(Null,Null)
where [Transaction Name]='TRANSACTION_NAME'
)

You can run 

SELECT * FROM FN_DBLOG(NULL,NULL)

To see all column names which fn_dblog would produce

Example: I would show what all things are logged when simple update command is fired

Begin tran T1

update [dbo].[IXBUILD] set C2='XXXXXX' where C1=1

Commit

enter image description here

  1. Its has one row for begin tran one for commit and one regarding update operation. You can see LOP_BEGIN_XACT for beginning of transaction and COMMIT for commit of transaction.

  2. LOP_MODIFY_ROW says a row was modified like we did in update statement.

  3. LCK_Clustered index is in picture because table had CI and row must have been exclusively locked for update

  4. Then comes hexadecimal page ID which actually says what page had row which was modified

  5. Then last column Includes the lock Information. The lock which was taken on

    HoBt 72057594041860096:ACQUIRE_LOCK_IX OBJECT: 8:613577224:0 ;ACQUIRE_LOCK_IX PAGE: 8:1:344 ;ACQUIRE_LOCK_X KEY: 8:72057594041860096 (8194443284a0)

    Database ID=8 file number 1 and page 334. You would also see KEY value

  6. There is also a log sequence number for every transaction and column Log Record Length which would tell you LSN and size of log record for particular operation

This is just a bit of information. There is too much to derive from content of transaction log and it would be difficult to write down all here. But I can suggest you various blogs of Paul Randal where you can find him writing about how to take help of the output of fn_dblog to get various information.

Time Pass with Transaction Log

Another dive into Transaction Log

Read various articles by Paul you would learn a lot about contents of transaction log

Tracking page split using Transaction Log

How to tell who changed Log file characteristics

Using fn_dblog,fn_dump_dblog and restoring STOPBEFOREMARK using LSN

Related Question