Read SQL Server transaction log

问题:

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.

回答1:

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.

回答2:

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

回答3:

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

回答4:

Step 1. CREATE TABLE #hex( [hex_Value] varbinary NULL )

Step 2. Insert data into the table, Example insert into #hex values(0x300008000F000000030000020015001B00536976754D79736F7265)

Step 3. SELECT LTRIM(RTRIM(CONVERT(VARCHAR(max),REPLACE(hex_Value, 0x00, 0x20)))) FROM #hex

For more Information go through this link

回答5:

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.

回答6:

I can not understand your needs, but the data from your log can be extracted by tools like Lumigent LogExplorer. I don't know nothing about there another way to do what you want.