Read SQL Server transaction log

Question

How we can read SQL Server transaction logs, I know using DBCC log (database,4) and it will generate log output now i want to decode Log Record which is is hex format.

0x00003E001C000000A500000001000200BE040000000006021D0000000100000018000000 (only a part of data)

is there any method to read it in text format or convert the hex data to text.i want to make a tool that can read logs.third party tools are available i.e ApexSQL but they are paid tools.

Answer 1

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.