A tour of the transaction log: Part 1 INSERT operations

The transaction log is a very important part of SQL Server. Every data modification operation is logged in the transaction log before being ‘hardened’ to the database file.

There are plenty of scenarios where you might take a look at the transaction log like point-in-time transaction log backup restores (to stop at a certain LSN) or finding out who deleted that row or table.

In this series of articles we’ll delve deeper into the transaction log and its use in DML/DDL operations like INSERT, UPDATE, DELETE and DROP. We’ll also restore a transaction log backup at a certain LSN.

This article is the introduction into the commands you can use to explore the transaction log, which are undocumented by Microsoft. We will be tracing an INSERT command through the transaction log right down to the page level!

Setting up the test

The first thing we will be doing is creating a simple test database:

NAME = N'TlogTest', FILENAME = N'E:\Data\TlogTest.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB
NAME = N'TlogTest_log', FILENAME = N'E:\Log\TlogTest_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%

Next step…a table!

USE [TlogTest]


Now let’s insert some data into the table:

GO 1000

Reading the transaction log

Now that we created a test database and table and filled it with some data, let’s see how it looks if we take a peek at the transaction log. For this we will be using the fn_dblog function.

USE [TlogTest]


As you can see from the results we get a whole lot of information back from the transaction log! There are a lot of very interesting columns in the data that is returned, we can see at what time the transaction was processed, the lock information for that transaction, and much more. Keep in mind though that the entire fn_dblog function is undocumented, so looking for Microsoft articles that explain the way it works will get you nowhere.

Following INSERT events

A nice way to see how the transaction log works is following an INSERT action. To make it a bit easier to trace we will be inserting a new value into our test table, it’s a ‘b’ instead of an ‘a’ to make finding it later on easier:

USE [TlogTest]


Now we will use the fn_dblog function again and look for INSERT operations, order by LSN should give us the latest transaction which is our INSERT:

USE [TlogTest]

[Current LSN],
[Transaction Name],
[Transaction SID],
[Page ID],
[Slot ID]
OR [Description] LIKE '%INSERT%'

This query gave me the following result, keep in mind that you might get other ID’s back then I did!


The LOB_BEGIN_XACT operation is the actual INSERT query, LOP_INSERT_ROWS is the operation that places the data into the data pages.

We’ll follow the trail of the data page first! To find out in what data page the data was inserted we can use the Page ID column, only problem is that it’s a hex number. We are only interested in the last 8 numbers, in this case 0000009e. If we convert this value to decimal we get 158, which is the page id we are looking for.

Use DBCC PAGE (and a traceflag) we can take a look inside the data page and see if our data is there:


USE [TlogTest]

DBCC PAGE (TLogTest, 1, 158, 3);

Now the Slot ID comes in handy, this is the slot in the data page where our data is stored, in my results the Slot ID was 351.

This is the result I got from the DBCC PAGE and scrolling down to Slot 351:


As you can see the data from the INSERT is there!

Tracing who did the INSERT

In some cases it might also be useful to find out who performed this INSERT. If we look at our fn_dblog query results again we can focus on the LOB_BEGIN_XACT operation.


We used the LOP_INSERT_ROWS operation for tracing the data pages, we can use the LOB_BEGIN_XACT to find out who performed the transaction by using the Transaction SID.

Simply copy the Transaction SID and perform the following query:


In this example I get the following result:


As you can see the Windows user Administrator performed the INSERT query, which happens to be true 😉

4 comments to “A tour of the transaction log: Part 1 INSERT operations”

You can leave a reply or Trackback this post.
  1. Excellent web site you’ve got here.. It’s difficult to find good quality writing like yours these days.

    I really appreciate individuals like you! Take care!!

  2. What i do not understood is in truth how you are not really a lot
    more neatly-appreciated than you might be now. You are very intelligent.
    You already know therefore significantly when it comes to this matter, produced
    me for my part believe it from a lot of numerous angles.

    Its like women and men are not interested until it is something to accomplish with Woman gaga!

    Your personal stuffs excellent. Always take care of it up!

  3. I havve to express thanks to you just ffor bailing me out of this condition.
    Just after surfing through the world-wide-web and getting proposals
    that were not productive, I thought my life was over. Being alive without the presence
    of solutions to the issues you’ve fixed throuh your main site is
    a crucial case, and thee kind that would have badly affected my entire
    career if I had noot noticed your website. Your own expertise and
    kindness in playing with the whole lot was excellent.
    I don’t know what I would’ve done if I had not discovered uch a subject like this.
    I’m able to at this time look forward to my future.
    Thanmk you very much for thiss specialized and results-oriented help.
    I won’t be reluctant to endorse your blog post to anyone who
    needs to have recommendations on this area.

Leave a Reply to supplement Cancel reply

Your email address will not be published.