Read SQL Server transaction log

Try this.

    Select 
        b.Description,
        d.AllocUnitName,
        b.[Transaction ID],
        d.name,
        d.Operation,
        b.[Transaction Name],
        b.[Begin Time],
        c.[End Time]
    from (
        Select 
            Description,
            [Transaction Name],
            Operation,
            [Transaction ID],
            [Begin Time]
        FROM sys.fn_dblog(NULL,NULL) 
        where Operation like 'LOP_begin_XACT'
    ) as b
    inner join (
        Select 
            Operation,
            [Transaction ID],
            [End Time]
        FROM sys.fn_dblog(NULL,NULL)
        where Operation like 'LOP_commit_XACT'
    ) as c
    on c.[Transaction ID] = b.[Transaction ID]
    inner join (
        select 
            x.AllocUnitName,
            x.Operation,
            x.[Transaction ID],
            z.name
        FROM sys.fn_dblog(NULL,NULL) x
        inner join sys.partitions y
        on x.PartitionId = y.partition_id
        inner join sys.objects z
        on z.object_id = y.object_id
        where z.type != 'S'
    )as d
    on d.[Transaction ID] = b.[Transaction ID]
    order by b.[Begin Time] ASC

That can get the database transaction (like insert, update, delete), transaction time, and the object name.

Hope that can help.

You can use sys.fn_dblog to read the transaction log. Example below.

SELECT [RowLog Contents 0],
       [RowLog Contents 1],
       [Current LSN],
       Operation,
       Context,
       [Transaction ID],
       AllocUnitId,
       AllocUnitName,
       [Page ID],
       [Slot ID]
FROM sys.fn_dblog(NULL,NULL)
WHERE Context IN ('LCX_MARK_AS_GHOST', 'LCX_HEAP', 'LCX_CLUSTERED') 
AND Operation IN ('LOP_DELETE_ROWS', 'LOP_INSERT_ROWS') 

For a delete and insert operation IIRC the [RowLog Contents 0] contains the whole row inserted and deleted. Updates are a bit more complicated in that only a partial row can be logged.

To decode this row format you need to understand how rows are stored internally in SQL Server. The book Microsoft SQL Server 2008 Internals covers this in some detail. You can also download the SQL Server Internals Viewer to help in this regard (And I believe the source code for Mark Rasmussen's Orca MDF is available too which presumably has some code to decode the internal row format).

For an example of doing this in TSQL see this blog post which demonstrates that it is perfectly possible to extract useful information from the log as long as the aim of the project is limited. Writing a full blown log reader that could cope with schema changes in the objects and things like sparse columns (and column store indexes in next version) would likely be a huge amount of work though.

There are several SQL Server functions and commands (e.g. fn_dblog, fn_dump_dblog, and DBCC PAGE) that potentially provide a way to view LDF file content. However, significant knowledge of T-SQL is required to use them, some are undocumented, and the results they provide are difficult to be converted to a human-readable format. Following are the examples of viewing LDF file content using SQL Server functions and commands:

1 - Here is an example using fn_dblog to read an online transaction log, with a result of 129 columns (only 7 shown here)

2 - The fn_dump_dblog function is used to read transaction log native or natively compressed backups. The result is similar:

Unfortunately, no official documentation is available for fn_dblog and fn_dump_dblog functions. To translate the columns, you need to be familiar with the internal structure and data format, flags and their total number in a row data

3 - DBCC PAGE is used to read the content of database online files – MDF and LDF. The result is a hexadecimal output, which unless you have a hex editor, will be difficult to interpret

Select * from sys.fn_dblog(NULL,NULL)
WHERE Context IN ('LCX_MARK_AS_GHOST', 'LCX_HEAP', 'LCX_CLUSTERED') 
AND Operation IN ('LOP_DELETE_ROWS', 'LOP_INSERT_ROWS') 

you get transaction related all the information using above query..where log record column displays your actual record which is in Hexadecimal format..

check this link to get your data into human readable format. check here