Sql-server – Cannot get LOB data from SQL Server transaction logs

database-internalssql serversql-server-2008-r2transaction-log

I am trying to extract all information (Insert, Update and Delete Operations) from the transaction log file of SQL Server manually. For this work, I am using the undocumented functions fn_dblog and fn_dump_dblog for SQL Server.

There are 3 allocation units (sys.allocation_units) in transaction log file Partition (sys.partitions).

  1. IN_ROW_DATA
  2. ROW_OVERFLOW_DATA
  3. LOB_DATA

In all of these allocation units, we found a RowLogContents0 column that contains the data of that row in hexadecimal format. I can get real data from the RowLogContents0 column, when I have an IN_ROW_DATA allocation unit type in my table. But when I have ROW_OVERFLOW_DATA or LOB_DATA allocation unit type in my table then I am unable to get data from RowLogContents0 column.

Does anyone know how I can get data using RowLogContents0 column which is stored in ROW_OVERFLOW_DATA and LOB_DATA allocation unit type?

I am following these links to get inserted and updated data from logs:

Now I am able to get these records and queries. But facing problem when trying to get LOB records as describe above.

Note : I don't want to use any third party tool to do this, because I am trying to make a third party tool for my program. Right now we are only focusing on SQL Server 2008 R2.

Best Answer

If you absolutely want to do this then I would advise you use a 3rd party product that does this for you. These undocumented functions are just that, along with the log structure.

I can get real data from RowLogContents0 column when I have IN_ROW_DATA allocation unit type in my table.

Each type of log record may have its own way of storing the data and could change between operations. There may be additional considerations for each record type as well... for example, in-memory records will not be properly processed using your above methods. There also may be additional considerations between versions of SQL Server.

Note : I don't want to use any third party tool to do this, because I am trying to make a third party tool for my program.

Then I wish you the best in your trial and error to figure out what works and what doesn't work for each specific type of interaction of DML, row store, etc.

The only other recourse you may have is talking with Microsoft if you're a partner and attempting to obtain help with this endeavor.

Disclaimer: I work for Microsoft.

Related Question