Read LDF for Particular Transaction in SQL Server
How to proceed for reading the binary data in LDF file to understand the transaction details happen in SQL Server.
Run below query particular database to find the details:SELECT
[SPID],
[Current LSN],
[Operation],
[Context],
[Transaction Name],
[Transaction ID],
[Transaction SID],
[Log Record Length],
[Description],
[Begin Time],
[End Time]
FROM fn_dblog (null, null)
Suppose we have insert data into table called 'Room' in the database.
Now i have excuted the below query to find the what transactions happened on Room
select [transaction name] from fn_dblog(null,null) where [transaction name] is not null
Above screenshot says that 1 insert and 7 update operations happen on room database.
Execute below query and capture the "Transaction ID"
SELECT
[SPID],
[Current LSN],
[Operation],
[Context],
[Transaction Name],
[Transaction ID],
[Transaction SID],
[Log Record Length],
[Description],
[Begin Time],
[End Time]
FROM fn_dblog (null, null)
Now filter the query by giving the input as "Transaction ID"
Now query will look like :
SELECT
[SPID],
[Current LSN],
[Operation],
[Context],
[Transaction Name],
[Transaction ID],
[Transaction SID],
[Log Record Length],
[Description],
[Begin Time],
[End Time]
FROM fn_dblog (null, null) where [transaction id] like '0000:0000022a'
Output is below:
Above output shows the which SPID is responsible for the inserts and complete details of that particular transaction with begin time and end time of transaction
Hope above information helps
Thanks
