How To Read a Transaction Log in a SQL Server

You can read the transaction log with the help of undocumented functions called fn_dblog и fn_dump_dblog:

I. fn_dblog – an undocumented SQL Server function that allows you to monitor the active part of the transaction log in real time.

Let’s execute the following query:

Select top(100) * FROM sys.fn_dblog(NULL,NULL)

The result is as follows:

Usually, the amount of retrieved data is very large, so it’s better to filter it. For example, let’s select only those transactions that insert rows into a table:

SELECT top(100) [Current LSN],
       [Operation],
       [Context],
       [Transaction ID],
       [Begin time]
       FROM sys.fn_dblog
   (NULL, NULL)
  WHERE operation IN
   ('LOP_INSERT_ROWS');

Here’s a possible result of such a query:

We can retrieve all row deletion transactions in a similar way using this query:

SELECT top(100) [begin time],
       [rowlog contents 1],
       [Transaction Name],
       Operation
  FROM sys.fn_dblog
   (NULL, NULL)
  WHERE operation IN
   ('LOP_DELETE_ROWS');

In the set resulting from this query, rows will look similar to the following:

The information about inserted or deleted rows will be stored in these columns:
• RowLog Contents 0
• RowLog Contents 1
• RowLog Contents 2
• RowLog Contents 3
• RowLog Contents 4
• Description
• Log Record

Different columns are used for each transaction type. To get the required info, you need to know which columns are used for which transactions, but it can be rather difficult because there’s no official documentation describing this.
Inserted and deleted rows are stored as HEX values. To extract data from these values, you need to know the format they’re stored in, understand state bits, know the total number of columns, etc.

Next, we need to convert binary data to a table while considering column data types. The conversion method is different for all data types.

fn_dbLog is a great free tool that allows you to read transaction logs. However, this functionality has some caveats – it’s difficult to navigate through the data as it contains records about system tables, only the active part of the log is displayed, and there’s no information on updates of BLOB values.

With minimal transaction log protocolling, the UPDATE operation does not contain the full values before and after changes, but rather stores only the part that was changed (SQL Server can write that a value was changed from ‘G’ to ‘D’, while in reality, the word ‘GLOAT’ was changed to ‘FLOAT’ ). In this case, you would need to manually restore all intermediary states of the record from the moment it was inserted to the moment you’re interesed in.

When a BLOB object is deleted, it’s not written to the log – only the fact that it was deleted will be recorded. To restore a deleted BLOB object, you need to find a pair for this deletion in the log. It’s a previously performed insertion which is, most probably, not stored in the log’s active part anymore.

II. fn_dump_dblog – as of yet, an undocumented function that allows you to look through the transaction log from it’s backup copy (either a compressed or uncompressed one):

SELECT top(100) [Current LSN],
       [Operation],
       [Context],
       [Transaction ID],
     [transaction name],
       [Description]
FROM fn_dump_dblog
(NULL,NULL,N'DISK',1,N'E:\Backups\JobEmpl.bak',
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT);

The fn_dump_dblog function, just as fn_dblog, returns 129 columns. This is why it’s important to specify the necessary columns in the query.

However, the limitations here are the same as with the fn_dbLog function, including data decryption.

III. DBCC PAGE – an undocumented function that allows you to look through the contents of MDF and LDF files:

DBCC PAGE ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

However, the result of the DBCC PAGE command is not displayed in SQL Server Management Studio by default. To display this result properly, the trace flag 3604 should be turned on:

DBCC TRACEON (3604, -1)

After this, you can execute the desired command.

DBCC PAGE has the same limitations as previously described functions when it comes to data decryption.

However, a more convenient approach is to use off-the-shelf solutions for reading transaction logs and rolling back changes. For instance, the RedGate SQL Log Rescue or dbForge Transaction Log.